simsjr said:
Hi,
I need a way to create and dump a list of all query names in an Access
database into a variable for the purpose of displaying the list to a user in
a MsgBox or something.
Should be easy, but I'm not sure how to go about it.
Hi,
I have created a general purpose code which adds all objects in a table from
current project. It adds all tables, queries, forms, reports, etc.
To make it work, follow the steps as given below.
Step 1:
Create a table with three fields as under.
Name: tblObjects
Field Name data type
objNo Number
objType Text
objName Text
Step 2:
Copy and paste this code in any standard module.
'=================Start Code ==============
Public Sub EnterObjectNames()
Dim objCollection As Object
Dim aob As AccessObject
Dim strSQL As String
strSQL = "DELETE tblObjects.* FROM tblObjects;"
'Delete old data if any in tblObjects
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "tblObjects", cnn, adOpenKeyset, adLockOptimistic, adCmdTable
Dim intNum As Integer
intNum = 0
With CurrentData
For Each aob In .AllTables
'Do not add tables used by Access internally which starts with letter
'M'
If Mid(aob.Name, 1, 1) <> "M" Then
rst.AddNew
rst!objName = aob.Name
rst!objType = "Table"
intNum = intNum + 1
rst!objNo = intNum
rst.Update
End If
Next aob
intNum = 0
For Each aob In .AllQueries
rst.AddNew
rst!objName = aob.Name
rst!objType = "Query"
intNum = intNum + 1
rst!objNo = intNum
rst.Update
Next aob
End With
With CurrentProject
intNum = 0
For Each aob In .AllForms
rst.AddNew
rst!objName = aob.Name
rst!objType = "Form"
intNum = intNum + 1
rst!objNo = intNum
rst.Update
Next aob
intNum = 0
For Each aob In .AllReports
rst.AddNew
rst!objName = aob.Name
rst!objType = "Report"
intNum = intNum + 1
rst!objNo = intNum
rst.Update
Next aob
intNum = 0
For Each aob In .AllModules
rst.AddNew
rst!objName = aob.Name
rst!objType = "Module"
intNum = intNum + 1
rst!objNo = intNum
rst.Update
Next aob
End With
rst.Close
Set rst = Nothing
End Sub
'=============End Code ================
With Current Project you can also add macros and Data access pages. If
required add code as written for other objects.
Step 3 :
In immediate window execute the procedure. Just type the procedure name
"EnterObjectNames" and press enter.
Step 4:
Now go to database window. Open tblObjects. You will see all objects in
current database.
Good Luck,
Surendran
