473,324 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

openrecordset with variable table names

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
4 6800
Rabbit
12,516 Expert Mod 8TB
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
8,834 Expert 8TB
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
Duh! I was hard coding my variable AFTER the Set statement, so it was NULL! Thanks, Rabbit.
Oct 17 '07 #4
Rabbit
12,516 Expert Mod 8TB
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

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

Similar topics

5
by: Philippa | last post by:
I'm trying to access data in vba using the openrecordset command. The data in on a SQL Server 2000 database, and I have linked tables to that data. the Table I'm trying to access is one of these...
8
by: Russell Potter | last post by:
I'm trying to create a recordset using "currentDB.OpenRecordSet", using a query as the "source" string (the only parameter: so all the others are set to their defaults which, I believe, means...
5
by: Sunnyrain | last post by:
I am developing a program in Access 2000. I couldn't make OpenRecordset method work right. It's working when I opened a simple SQL query below in OpenRecordset. ..... Dim dbs As Database, rst...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
16
by: John | last post by:
Does the length of my C variable names have any affect, performance-wise, on my final executable program? I mean, once compiled, etc., is there any difference between these two: number = 3; n =...
10
by: MLH | last post by:
Gentlemen: I am having one heck of a time taking a DAO walk through the records in an SQL dynaset. I'm trying to walk a set of records returned by a UNION query. I'm attempting to filter the...
4
by: Victor Lagerkvist | last post by:
Hello, I have the need to parse variable names from a string and save them somewhere safe for future usage. Here's my first attempt (I don't have any rules for valid names yet) - but I have a...
22
by: MLH | last post by:
100 Dim db As Database, rst As Recordset 120 Set db = CurrentDb 140 PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID FROM qryBatchList WHERE...
7
by: mirandacascade | last post by:
The questions are toward the bottom of this post. Situation is this: 1) Access 97 2) Multi-user appplication 3) SQL Server 2000 4) Sporadically (i.e. less than 1% of the time) encounter the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.