use variable instead of table name

[AbdullahGH]

Hi,,

In Access VBA, I have many tables and instead to write same code for each table, I want to use Variable name for those Tables and useing loop.
Below statements not accepted and give error "Object required", The original tables object defined and accepted.

Set Table_Temp = db.OpenRecordset("First_Table_Name")
or
Set Table_Temp = db.TableDef("First_Table_Name")

In Excel VBA, it is working fine like below:



Set Table_Temp = Workbooks("First_Table_Name.xlsm"

Set Sheet_Temp = Temp.Sheets("First_Sheet_Name")

[davegri]

See if this will help. tblStreets is an existing table.

Public Sub test()
    Dim db As DAO.Database
    Dim table_temp As DAO.TableDef
    Set db = CurrentDb
    Set table_temp = db.TableDefs("tblStreets")
    Debug.Print table_temp.RecordCount       'verify that it works
End Sub

Or if you want the table name passed as a parameter:

Public Sub subTest(tblArg As String)
    Dim db As DAO.Database
    Dim table_temp As DAO.TableDef
    Set db = CurrentDb
    Set table_temp = db.TableDefs(tblArg)
    Debug.Print table_temp.RecordCount
End Sub

[ranman256]

Also...

Public Sub subTest(tblArg As String)
    Msgbox  CurrentDb.TableDefs(tblArg).RecordCount
End Sub

[John_G]

Below statements not accepted and give error "Object required",
Set Table_Temp = db.OpenRecordset("First_Table_Name")

You are probably getting that error message because Access doesn't know what the variable db is, or what Table_Temp is.

You can fix this in two ways. The first way is to add these lines to your code:

Dim db as database
Dim Table_temp as Recordset
set db = currentdb


The second way is to use

Dim Table_temp as Recordset

as before, then change
Set Table_Temp = db.OpenRecordset("First_Table_Name")
to
Set Table_Temp = Currentdb.OpenRecordset("First_Table_Name")

Also, if First_Table_Name is a string variable containing a table name, use it without the quotation marks:

Set Table_Temp = Currentdb.OpenRecordset(First_Table_Name)

because it is already a string.