November 17, 2005 at 12:52 pm
To whom can help,
We've got a customer with 20 access databases and we are tring to write a program to see what Stored procedures are being used in the database and we are stuck. I've tried opening the MDB in a text stream and search this way but this does not work on bigger MDB's and I've also tried using the Application.CurrentProject.allmodules to search through the modules, but if the module is not opened on the VBA it will not search though it.
Here are my 2 questions, One, is it possible to load the Module it it is unloaded and 2, is there a way to do this for forms.
If you need more info just let me know.
Thanks,
Sam M.
November 18, 2005 at 12:40 am
Try ADO and ADOX:
' Begin ProceduresVB
Sub Main()
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim prc As ADOX.Procedure
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
On Error GoTo ErrorHandler
' Open the Connection
cnn.Open _
"Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='Northwind.mdb';"
' Open the catalog
Set cat.ActiveConnection = cnn
' Refresh the Procedures collection
cat.Procedures.Refresh
For Each prc In cat.Procedures
Debug.Print prc.Name
' Get the command object
Set cmd = prc.Command
Debug.Print cmd.CommandText
' Retrieve Parameter information
cmd.Parameters.Refresh
For Each prm In cmd.Parameters
Debug.Print prm.Name & ":" & prm.Type
Next
Set prm = Nothing
Next
'Clean up
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
'clean up
Set cmd = Nothing
Set prc = Nothing
Set cat = Nothing
Set cnn = Nothing
Exit Sub
ErrorHandler:
Set prm = Nothing
Set cmd = Nothing
Set prc = Nothing
Set cat = Nothing
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
Set cnn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
' End ProceduressVB
You can find more at:
http://msdn.microsoft.com/library/en-us/ado270/htm/admscobjectmodel.asp
You can also look at using Access VBA:
http://msdn.microsoft.com/library/en-us/vbaac11/html/actocOMMap_HV01049589.asp
Andy
November 18, 2005 at 3:51 am
For this purpose, i have made the following steps :
1. open the vba project in Access
2. print it in a pdf document, save it
3.open the pdf doc, copy and paste the text in a text file : it will contain all the vba code
4. put on the sql server database the text file in a stored procedure ( called for exemple SP_comment_vba)
5. search for each real SP's name if it is included in the SP_comment_vba
For example, i have in the SP_comment_vba some lines like those :
If Not bDtsRun("DTSname1") Then
If Not bExecuteSP(dbTemp, "SPname1" & Str$(param1)) Then
pSQL = "SPname2"
And it makes the impact's analysis easyer, from a TSQL view...
November 18, 2005 at 7:57 am
You can put the following code into a new module in the access database to dump out the code for all reports, forms, and modules. It creates a single textt file and is very usefull in diffing the code in access applications. If you want to get fancy you can also dump out the form and report object's properties.
This is vba but I'm sure this can also be tailored to work in a vb app with some modification.
One thing to consider also, though, you may have pass through queries with stored procedure calls. This code doesn't account for that. Hope this helps.
Public Sub Code_Dump()
Dim t As Integer
Dim fs As Object
Dim outfile As Object
Dim strLine As String
Dim strFileName As String
Dim db As Database
On Error Resume Next
Set db = CurrentDb
strFileName = "C:\Temp\VBACode.vba"
'Create outfile
Set fs = CreateObject("Scripting.FileSystemObject")
Set outfile = fs.CreateTextFile(strFileName, True)
' Go through each module
outfile.writeline ""
outfile.writeline ""
For t = 0 To db.Containers("Modules").Documents.Count - 1
outfile.writeline ""
& db.Containers("Modules").Documents.Item(t).Name & " ****"
outfile.writeline ""
outfile.writeline Application.VBE.ActiveVBProject.VBComponents.Item(db.Containers("Modules").Documents.Item(t).Name).CodeModule.Lines(1, Application.VBE.ActiveVBProject.VBComponents.Item(db.Containers("Modules").Documents.Item(t).Name).CodeModule.CountOfLines)
Next t
outfile.writeline ""
outfile.writeline ""
For t = 0 To db.Containers("Forms").Documents.Count - 1
Debug.Print Application.VBE.ActiveVBProject.VBComponents.Item("Form_" & db.Containers("Forms").Documents.Item(t).Name).CodeModule.CountOfLines
If Err.Number = 0 Then
outfile.writeline ""
& db.Containers("Forms").Documents.Item(t).Name & " ****"
outfile.writeline ""
outfile.writeline Application.VBE.ActiveVBProject.VBComponents.Item("Form_" & db.Containers("Forms").Documents.Item(t).Name).CodeModule.Lines(1, Application.VBE.ActiveVBProject.VBComponents.Item("Form_" & db.Containers("Forms").Documents.Item(t).Name).CodeModule.CountOfLines)
End If
Err.Clear
Next t
outfile.writeline ""
outfile.writeline ""
For t = 0 To db.Containers("Reports").Documents.Count - 1
Debug.Print Application.VBE.ActiveVBProject.VBComponents.Item("Report_" & db.Containers("Reports").Documents.Item(t).Name).CodeModule.CountOfLines
If Err.Number = 0 Then
outfile.writeline ""
& db.Containers("Reports").Documents.Item(t).Name & " ****"
outfile.writeline ""
outfile.writeline Application.VBE.ActiveVBProject.VBComponents.Item("Report_" & db.Containers("Reports").Documents.Item(t).Name).CodeModule.Lines(1, Application.VBE.ActiveVBProject.VBComponents.Item("Report_" & db.Containers("Reports").Documents.Item(t).Name).CodeModule.CountOfLines)
End If
Err.Clear
Next t
outfile.Close
End Sub
November 18, 2005 at 3:22 pm
Scott,
The quickest way is to open up each Access Database as a user and click on
Tools-Analyze-Documentor menu.
Select the appropriate options for each database object.
This will present an Access Style report which can be exported to a text file for analysis with whatever tool you choose.
With the correct options chosen for each database object, all Sql, property info for queries, code modules, forms, reports, controls will be ouput.
The only thing you won't catch is any Access tables that may contain the name of the function or stored procedure which can be used in VBA to call the function/stored proc, so some sort of analysis of the tables would be necessary.
Hope this helps.
February 2, 2006 at 6:48 am
Scott,
Thanks a million! - have been trying to do this for a couple of days - no problem with (e.g.) Excel, but doing it in Access defeated me.
I'm planning on altering your code slightly, to output a separate file for each module/form, so that I can more easily pick up differences between 2 MDBs - probably using MS WinDiff (unless you know a better tool? - would need to have command-line capability so I can set up a batch file)
Again, thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply