Function/SQL Get Table List from MS Access
There are two ways to get a table list and put them into the combo box.
Method 1: Use a System Object as a Row Source of Combo box
1. Create a combo box
2. Set the Row Source Type to “Table/Query”
3. Under the Row Source of the combo box Data Property Sheet, add the SQL Statement to select only the table name
SELECT MSysObjects.name AS TableName FROM MSysObjects WHERE (((MSysObjects.type)=1) AND ((MSysObjects.Flags)=0)) ORDER BY MSysObjects.name;
MSysObjects.Type = 1 for table
More info about the MSysObjects.Type at stackoverflow.com here:
MSysObjects.Flags=0 where 0 means Select Query
Method 2: Use a Function
Function “GetTableList()” is shown in the picture below. This function will collect the tables that do not start with “MSys” name which is a system table. If you want to list all tables that starting with tbl_ then you can also use this code:
If tdf.Name like “tbl_*” Then to replace If Left(tdf.Name, 4) <> “MSys” Then
This function also uses a function “CheckFieldExists()” to list only the tables that have a specific field name. In the function below, the field name “Email” is specified to list only the tables that have Email field name.
A function CheckFieldExists() returns True if the table has a field name the specified in the parameter. We will use this function with the GetTableList() function to get only the tables that have field name “Email” by using If Statement as shown below:
If CheckFieldExists(tdf.Name, "Email") Then strList = strList & tdf.Name & ";" End If