table's name in a variable - in sql stmt

[jincep]

hi
can any one help me how to construct an sql select statement in which the table's name , i'm givin it in a variable

Tbl="eng_class6" or sometimes Tbl="maths_class6" , so I want data from diiferent tables.

Cmd.CommandText = "select * from " & Tbl & " where questno =" & arrRndNos(i) & ""
Cmd.CommandType = adCmdTable
Set RS = Cmd.Execute

Is it possible?
this gives me the error in FROM clause.
thanks

[Killer42]

can any one help me how to construct an sql select statement in which the table's name , i'm givin it in a variable
Tbl="eng_class6" or sometimes Tbl="maths_class6" , so I want data from diiferent tables.
Cmd.CommandText = "select * from " & Tbl & " where questno =" & arrRndNos(i) & ""
Cmd.CommandType = adCmdTable
Set RS = Cmd.Execute
Is it possible?
this gives me the error in FROM clause.
thanks

The general idea seem alright, so the problem is likely to be in the specific details of this case. Can you tell us the exact error message? And can you set a break point or something, and display the value of Tbl to check for certain what's in it? (You could also use Debug.Print, or whatever).

It might help (or might not) help if you put square brackets around the table name. For example, "[maths_class6]" instead of "maths_class6".

It might help to know what environment you're working in, too. For example, VB6, VB.Net, VBA in MS Access, etc.

Um... You might also try adding a semicolon (;) on the end of the string - don't know whether it will help or not. Is that what was supposed to be in that empty string you're appending?

[jincep]

hi, it works with this.

Cmd.CommandText = "select * from " & Tbl & " where questno =" & arrRndNos(1) & ";"
Cmd.CommandType = adCmdText
Set RS = Cmd.Execute

thank you so much

[Killer42]

hi, it works with this.
Cmd.CommandText = "select * from " & Tbl & " where questno =" & arrRndNos(1) & ";"
Cmd.CommandType = adCmdText
Set RS = Cmd.Execute
thank you so much

So the problem must have been either the semicolon, or the value of i.