How to schedule an automatic reverse-engineer from a database into a data model?

by Oct 25, 2022

When you use ER/Studio Data Architect, you can easily create a new data model based on an existing database by using the Reverse Engineer Wizard from the User Interface.

In the last step of the wizard, you can save the settings to a file for later reuse.

If you have previously saved such a file, you can load it with the Wizard Quick Launch to get back all your previous settings.

So, it's very easy to configure the reverse engineer once, and to manually replay it when we need it.

Would it be possible to automatize this reverse engineer? Not directly, but we can run an ER/Studio Data Architect macro from command line, and this macro could reverse engineer our database. So here is the macro I coded to achieve it:

'TITLE:  wReverseEng.BAS
'DESCRIPTION:  This macro reverse engineers a DB into a timestamped data model file

Const PROJECT_FILENAME = "<YOUR_PATH><FILENAME>"
Const QUICK_LAUNCH_PATH = "%appdata%IderaERStudioXML<QUICK_LAUNCH_FILENAME>.rvo"

'ER/Studio Variables
Dim rvo As ReverseEngineer
Dim diag As Diagram

Dim sProjectPath$

Option Explicit

Sub Main
	Debug.Clear

	' Timestamp & extension added
	sProjectPath = PROJECT_FILENAME & Format(Now, "_yyyy-mm-dd_HH-mm-ss") & ".dm1"

	' Create the Reverse Engineer Object
	Set rvo = DiagramManager.ReverseEngineerObject

	' Load the settings from the Quick Launch save
	rvo.QuickLaunchFile = QUICK_LAUNCH_PATH

	' Reverse engineer the database 
	Set diag = rvo.DoReverseEngineer

	' Save the project
	diag.SaveFile(sProjectPath)

	' Close the newly created project
	DiagramManager.CloseDiagram(diag.FileName)
End Sub

Then to automatize it, we can schedule a call to this macro using the Windows Task Scheduler.

Launch the Task Scheduler and Create a Basic Task…

Define when you want the task to start:

Choose the Action, Start a program:

Click the Browse… button and select the ER/Studio Data Architect application. By default: "C:Program FilesIDERAERStudio Data Architect <VERSION>ERSTUDIO.exe"

Add arguments: -m "<PATH TO THE ABOVE EDITED MACRO FILE SAVED IN YOUR ENVIRONMENT>"

Review the summary and click the Finish button to create your scheduled task.

Finally, the task will run when it's scheduled and your project files will be generated.

Let me know in the comments if you have any questions!