Hi - I'm trying to create a dropdown box in Excel based on table names in Access db.
Below is the code I'm using to get the data I foresee maybe 15 tables. But am unsure how to store the data as a variable in VBA.
Code:
Sub Example1()'an Access object
Dim objAccess As Object
'connection string to access database
Dim strConnection As String
'catalog object
Dim objCatalog As Object
'connection object
Dim cnn As Object
Dim i As Integer
Dim intRow As Integer
Set objAccess = CreateObject("Access.Application")
'open access database
Call objAccess.OpenCurrentDatabase( _
"D:StuffBusinessTempNewDB.accdb")
'get the connection string
strConnection = objAccess.CurrentProject.Connection.ConnectionString
'close the access project
objAccess.Quit
'create a connection object
Set cnn = CreateObject("ADODB.Connection")
'assign the connnection string to the connection object
cnn.ConnectionString = strConnection
'open the adodb connection object
cnn.Open
'create a catalog object
Set objCatalog = CreateObject("ADOX.catalog")
'connect catalog object to database
objCatalog.ActiveConnection = cnn
'loop through the tables in the catalog object
intRow = 1
For i = 0 To objCatalog.Tables.Count - 1
'check if the table is a user defined table
If objCatalog.Tables.Item(i).Type = "TABLE" Then
'get the tables name
Cells(intRow, 1) = objCatalog.Tables.Item(i).Name
intRow = intRow + 1
End If
Next i
End Sub