Repost of Problem with Accessing JOINed Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have JOINed two tables in SQL using variables for the table names. (The SQL works fine since I can access the data via DEBUG).

BUT what is the right VB syntax to access the data in code.

If the table names were explicit I can use the syntax 'MySet![ABC.field1].value' where ABC is one of the table names. BUT what is the syntax for a variable table name in this same context?

thanks
Laurie Eaton

That is:

If I join two tables with explicit names I can access the data in the recordset using
'MySet![ABC.field1].value' where 'ABC' is one of the explicit names. This works fine.
But if the explicit table name is replaced by a variable in the SQL statement I can't find the correct syntax in VB to access the data. The SQL part with variable names (in the form '" & XYZ & ") is working ok since I have checked it in Debug. The data is in the recordset but I can't get to it using variables.

So in summary my problem is the VB syntax to access the recordset when the table names used to form the recordset via a SQL JOIN are variables.
 
If the table names were explicit I can use the syntax 'MySet![ABC.field1].value' where ABC is one of the table names. BUT what is the syntax for a variable table name in this same context?
Click to expand...

SQL and VBA are two different domains. SQL queries know nothing about
VBA variables; and to VBA, a SQL query is a single undivided string
value. You'll need to probably use the values - string constants -
which actually exist in the query SQL.
 
John,

thankyou for your response. I appreciate it.

The SQL was executed via a 'Set MySet = mydb.OpenRecordset(SqlTxt)' type statement in VB. (sorry I didn't make that clear). The table variable in SQL is made a part of the SQL string, but a different syntax is required when it comes to interrogating the recordset data using the same variable. Thats the bit I can't figure out.

regards

Laurie Eaton

John Vinson said:
If the table names were explicit I can use the syntax 'MySet![ABC.field1].value' where ABC is one of the table names. BUT what is the syntax for a variable table name in this same context?
Click to expand...

SQL and VBA are two different domains. SQL queries know nothing about
VBA variables; and to VBA, a SQL query is a single undivided string
value. You'll need to probably use the values - string constants -
which actually exist in the query SQL.
Click to expand...
 
Back
Top