By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

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

P: 2
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??
Feb 12 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,703
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim strSQL As String, strTableName As String
  3.  
  4.  
  5. strTableName = "Employees"
  6. strSQL = "Select * From " & strTableName
  7.  
  8. Set MyDB = CurrentDb()
  9. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  10.  
  11. MyRS.MoveLast: MyRS.MoveFirst
  12.  
  13. Debug.Print "The number of Records in " & strTableName & " is: " & MyRS.RecordCount
  14.  
  15. MyRS.Close
  16. Set MyRS = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. The number of Records in Employees is: 11
Feb 13 '08 #2

P: 2
adezii

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

Many thanks
Feb 13 '08 #3

ADezii
Expert 5K+
P: 8,703
adezii

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

Many thanks
You are quite welcome.
Feb 13 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.