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

openrecordset with variable table names

P: 80
Trying to write a select statement as a parameter of openrecordset where the name of the table will be variable.

Set rs = dbs.OpenRecordset("Select Count(*) as Cnt from " & strMyVariable & "_final where State = 'MO'")

This errors out, "cannot find table or query "_final". How do I get it to substitute in my variable?
Oct 16 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,315
It should work fine assuming that strMyVariable isn't null and is a control with a value or a variable that you dimmed and then set.
Oct 16 '07 #2

ADezii
Expert 5K+
P: 8,597
Trying to write a select statement as a parameter of openrecordset where the name of the table will be variable.

Set rs = dbs.OpenRecordset("Select Count(*) as Cnt from " & strMyVariable & "_final where State = 'MO'")

This errors out, "cannot find table or query "_final". How do I get it to substitute in my variable?
The correct syntax would be something similar to:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rst1 As DAO.Recordset, strTableName As String
  2.  
  3. strTableName = "Employees"
  4.  
  5. Set MyDB = CurrentDb()
  6. Set rst1 = MyDB.OpenRecordset("Select * From " & strTableName & _
  7.                 " Where [LastName] = 'King'", dbOpenDynaset)
  8.  
  9. rst1.MoveLast
  10. Debug.Print "Number of Employee Records (Last Name = King) is: " & rst1.RecordCount
  11.  
  12. rst1.Close
  13. Set rst1 = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Number of Employee Records (Last Name = King) is: 2
Oct 17 '07 #3

P: 80
Duh! I was hard coding my variable AFTER the Set statement, so it was NULL! Thanks, Rabbit.
Oct 17 '07 #4

Rabbit
Expert Mod 10K+
P: 12,315
Duh! I was hard coding my variable AFTER the Set statement, so it was NULL! Thanks, Rabbit.
Not a problem, good luck.
Oct 17 '07 #5

Post your reply

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