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:
[CODE=vb]
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.OpenRecord set(strSQL, dbOpenDynaset)

MyRS.MoveLast: MyRS.MoveFirst

Debug.Print "The number of Records in " & strTableName & " is: " & MyRS.RecordCoun t

MyRS.Close
Set MyRS = Nothing[/CODE]
OUTPUT:
[CODE=text]The number of Records in Employees is: 11[/CODE]

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