Database Query Problem [SOLVED]

[hothead]

I'm using the following code:

rs.Open "SELECT * FROM " & lvwGroups.SelectedItem & " ORDER BY Name", dtaVolumes.ConnectionString, adOpenKeyset, adLockReadOnly

The error is as follows:

Syntax error in FROM clause

I looked it up on MSDN, and it says this error is caused by a table having the name of a reserved keyword in SQL. I changed the table name to something like "Test" just to test it, and STILL got the error! Now I know that the word Test is definitely not used in SQL. What's wrong?

[visualAd]

Try this:

rs.Open "SELECT * FROM `" & lvwGroups.SelectedItem & "` ORDER BY `Name`", dtaVolumes.ConnectionString, adOpenKeyset, adLockReadOnly, adCmdText

N.B. This ` is not an apostrophe '.

[vb_dba]

You could also try surrounding the table name with square brackets - []:

rs.Open "SELECT * FROM [b][[/b]" & lvwGroups.SelectedItem & "[b]][/b] ORDER BY [b][[/b]Name[b]][/b]", dtaVolumes.ConnectionString, adOpenKeyset, adLockReadOnly, adCmdText

Also, Name is a reserved word, so I surrounded it with the brackets as well.

If neither visualAd's nor my suggestion works, build a SQL string and output it to verify the sql statement you are trying to run:

Dim sSql As String
 
sSql = "SELECT * FROM [" & lvwGroups.SelectedItem & "] Order By [Name]"
 
Debug.Print sSql
rs.Open sSql, dtaVolumes.ConnectionString, adOpenKeyset, adLockReadOnly, adCmdText

[hothead]

Neither has worked. Still getting error in FROM clause. SQL statement is being passed as expected.

[hothead]

I fixed it.

Strange.... seems the adCmdTable was causing my problem.

[visualAd]

adCmdTable - siginifies the text is the name of a table in the database

adCmdText - should be used if it is an SQL statement.

[hothead]

Yeah, now that you've explained this to me, it makes a lot more sense.