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

by Oct 25, 2022

This blog post describes how you can run a macro to reverse engineer a database and call it from the Windows Task Scheduler.

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.

Reverse Engineer Wizard

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

Reverse Engineer Wizard

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

 Wizard Quick Launch

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:

'#Language "WWB-COM"
''MACRO TITLE: wReverseEng
' MACRO VERSION: 1.0
'
'This macro reverse engineers a DB into a timestamped data model file
'
' Requirements:
'	You must update the constants below to use the correct files
'	A quick launch file previously saved from the Reverse Engineer Wizard
'
'------------------------------------------------------------------------------

Const PROJECT_FILENAME = "<YOUR_PATH><FILENAME>"
Const QUICK_LAUNCH_PATH = "%appdata%\Idera\ERStudio\XML\<QUICK_LAUNCH_FILENAME>.rvo"

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

Dim sProjectPath$

Option Explicit

Sub Main
	Debug.Clear

	Dim path$()
	Dim folder$
	Dim att%

	On Error GoTo eQLP
	GetAttr(QUICK_LAUNCH_PATH)

	On Error GoTo eFolder

	' Extract folder
	path = Split(PROJECT_FILENAME, "\")
	ReDim Preserve path(UBound(path) - 1)

	' Check if folder is available
	folder = Join(path, "\")
	att = GetAttr(folder)
	' folder NA
	If ((att <> vbDirectory) And (att <> vbDirectory + vbHidden)) Then
		Debug.Print "Folder '" & folder & "' doesn't exist." & vbCrLf & "Try to create it..."
		' Create the folder
		MkDir folder
	End If

	' folder available
	att = -1
	att = GetAttr(folder)
	If ((att = vbDirectory) Or (att = vbDirectory + vbHidden)) Then
		' 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 & ERDA
		DiagramManager.CloseDiagram(diag.FileName)
	End If

	Exit Sub

	eQLP:
		If Err.Number = 10051 Then
			Debug.Print "'" & QUICK_LAUNCH_PATH & "' doesn't exist."
		End If
		Exit Sub
	eFolder:
		If Err.Number = 10051 Then
			Debug.Print "'" & folder & "' doesn't exist."
			Resume Next
		ElseIf Err.Number = 10102 Then
			Debug.Print Err.Description & vbCrLf & folder
		End If
End Sub

To use the above macro, you need to update the constants to match the path to your DM1 file and to your Quick Launch settings.

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…

Create Basic Task

Create a basic task

Define when you want the task to start:

Trigger

Choose the Action, Start a program:

Start a program

Click the Browse… button and select the ER/Studio Data Architect application. By default: “C:\Program Files\IDERA\ERStudio Data Architect <VERSION>\ ERSTUDIO.exe

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

Add arguments

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

Review task

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

Task is running

File created

Summary

In this blog post, we used:

If you want to reverse engineer a list of databases, I’d suggest to check this other post.

Bonus

A short video which shows how to create a macro from a script in ER/Studio Data Architect: