variable table names in a select statement

[neeeel]

Is it possible to have variable names in a select statement
for example


public sub test( name as string)
index = 0
Set dbs = OpenDatabase("c:\Users\neil\Desktop\test.accdb")
Set rst = dbs.OpenRecordset("SELECT FTR from '" & name & "' where R = " & index)

I am getting a syntax error, incomplete query clause when I run the code

[Imdabaum]

Yes it is possible. Did you try to remove the single quote in your SELECT statement? I know VBA likes to confuse us by saying they're required in a DLookup, then they let them slide on other instances. This might be one of them.

IF you want a quicker way to get the database try Currentdb()
Do you have those variables dimmed?

Dim dbs as Database
Dim rst as DAO.Recordset
Dim index as Integer
index = 0
Set dbs = Currentdb()
Set rst = dbs.OpenRecordset("SELECT ftr FROM " & name & " WHERE r = " & index, dbOpenDynaset)

[neeeel]

ye, that worked, Im sure I tried that before I posted, but obviously not

thanks for your help

[Imdabaum]

You don't have to tell me about it... I've been there. It's a conspiracy that the computers have been working on for years. Almost like a slave rebelion... they get tired of following orders, and so they just take short breaks. Fortunately logic is a hard concept to argue with and they all go back to work in the end... or they give up ghost. ;) Glad I could help.