Use a variable in place of a table Name in Select statement

[BarryS]

My Access 2003 application imports periodical Excel spreadsheets, which are in the same format, into sequential Tables.
I wish to to use the same query each period to process the latest Table using;

Dim strTablename as String
strTablename = [latest Table name]

Select * from strTablename

The Query produced returns strTablename as the name of the 'linked' Table

What am I doing wrong??

[ADezii]

My Access 2003 application imports periodical Excel spreadsheets, which are in the same format, into sequential Tables.
I wish to to use the same query each period to process the latest Table using;

Dim strTablename as String
strTablename = [latest Table name]

Select * from strTablename

The Query produced returns strTablename as the name of the 'linked' Table

What am I doing wrong??

The correct Method for using a Variable Name in place of a Table Name within a SELECT Statement is:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim strSQL As String, strTableName As String
 
 
strTableName = "Employees"
strSQL = "Select * From " & strTableName
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
MyRS.MoveLast: MyRS.MoveFirst
 
Debug.Print "The number of Records in " & strTableName & " is: " & MyRS.RecordCount
 
MyRS.Close
Set MyRS = Nothing

OUTPUT:

The number of Records in Employees is: 11

[BarryS]

adezii

The addition of the Variable after the '&' fixed the problem

Many thanks

[ADezii]

adezii

The addition of the Variable after the '&' fixed the problem

Many thanks

You are quite welcome.