combo box.

[k209310]

is it possible to populate a combobox in a user form with a list of table taken from an access database?

 

[DJN]

No unless you have the table names in a table? You could use a list box instead and use
SELECT [Name] FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];
as the RowSource. This will list all tables in your DB.


David

 

[k209310]

thanks for that ill give it a try.

 

[k209310]

Just out of interest i thought people might like to Know that this code populates ca combox with a list of table from a database using VBA.

'Populates the combo box with the tables from a database
'declaration of variable
Dim strDatabasePath As String
Dim oConn As Object
Dim oTable As Object
Dim oCatalog As Object

'Displays a status bar to visually show users of task taking place (Lets the user of slow mahines know that the program has not hung)
Application.DisplayStatusBar = True
Application.StatusBar = "Searching for Required Tables"

'declares the path of the database.
strDatabasePath = "Db path"

'This code opens a connection to the database using OLEDB connection
Set oConn = CreateObject("ADODB.Connection")
oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
oConn.ConnectionString = strDatabasePath
oConn.Open

'Now open a catalog - used to determine the information about the database
Set oCatalog = CreateObject("ADOX.Catalog")
Set oCatalog.ActiveConnection = oConn

'Now look through each table in the database and add it to the combobox
For Each oTable In oCatalog.Tables
If oTable.Type = "TABLE" Then Me.ComboBox1.AddItem oTable.Name
Next oTable

'Close the connection
oConn.Close
'resets the staus bar
Application.StatusBar = "Ready"