Function Get Table List : MS Access




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

combo box source

SQL Statement:

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

Function Code:

get table function

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.

function get table list

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
Function Code:

function check if field exists



Related posts