Solved: SELECT Query using Dynamic Table name

[asingh]

Hi,

How do I run a SELECT query in Access-VBA where the table name is supplied dynamically.

I have tried the following line...but it is not working..



DIM rst as ADODB.recordset

Set rst = New ADODB.Recordset

rst.Open "SELECT tbl_name FROM & ' " & tbl_nm & " ' ", CurrentProject.Connection, adOpenStatic

rst.Close
Set rst = Nothing


I get the error "syntax error in from clause"

The column name tbl_name is existent in the table I am trying to query...!

thanks a lot for the help...

regards,

asingh

[geekgirlau]

I think the spaces will cause a problem - try this (assuming that there is a value in "tbl_nm"):

DIM rst as ADODB.recordset

Set rst = New ADODB.Recordset

rst.Open "SELECT tbl_name FROM & '" & tbl_nm & "'", CurrentProject.Connection, adOpenStatic

rst.Close
Set rst = Nothing

[asingh]

Hi,
I figured it out..this does the job:

rst.Open "SELECT tbl_name FROM " & tbl_nm & " ", CurrentProject.Connection, adOpenStatic

geekgirlau..will test what you suggested..and close the thread after that...

thanks a lot...

regards,
asingh

[asingh]

Hi,

geekgirlau.tried what you had suggested..getting a "incomplete query" error for some reason. Thanks anyways..closing this thread.

regards,

asingh