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.

 

[Pat Hartman]

The query as given will only show Access tables contained within the database. If you have any linked Access tables, you'll need to include TYPE = 6 and if you have any linked ODBC tables, you'll need to include TYPE = 4.

For those of you who are interested, the list as I have figured out so far is:

Type	TypeDesc
-32768	Form
-32766	Macro
-32764	Reports
-32761	Module
-32758	Access Object  - ?
-32757	Access Object  - ?
1	Table - Local Access Tables
2	Access Object - Database
3	Access Object - Containers
4	Table - Linked ODBC Tables
5	Queries
6	Table - Linked Access Tables
8	SubDataSheets

I would be interested in hearing about more if anyone knows of any.

 

[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"