SQL Incompatibility between DAO and ADO

[Linguist]

I recently wrote a catalog application using DAO technology. I discovered that since the computer I was installing it on did not have an MS Access driver, the program would not function as it did on my home computer which has Access. A friend suggested that I use ADO. After laboriously changing the code in that direction, lo and behold, the SQL syntax was not acceptable to the compiler. Here are a couple of the SQL statements are there are in DAO format:

Data1.RecordSource = "SELECT * FROM" & " " & cboTables.Text & " " & "WHERE" & " " & MyQuery

Data1.RecordSource = "SELECT * FROM [" & cboTables.Text & "]" & " WHERE [" & cboFields.Text & "] LIKE '*" & txtSearch.Text & "'"

Any suggestions on how I should convert them into proper ADO syntax? And where can I go to learn more about ADO SQL?

Thanks,
-Bob Smythe

 

[mohmehran]

As you know, SQL is a standard and there is no difference between the SQL syntax used in DAO and ADO. They are just different in object model. Are you using the ADO object model right? Tell exactly what piece of code are you using to implement this?

 

[davidrobin]

For a start you query would look more readable if changed from this

Data1.RecordSource = "SELECT * FROM" & " " & cboTables.Text & " " & "WHERE" & " " & MyQuery

to this

Data1.RecordSource = "SELECT * FROM " & cboTables.Text & " WHERE " & MyQuery

Secondly there is a difference in DAO wildcards and ADO wildcards. The wild card in the second query should be a % sign and not a * sign.

"SELECT * FROM [" & cboTables.Text & "]" & " WHERE [" & cboFields.Text & "] LIKE '%" & txtSearch.Text & "'"

Also make sure you do not have any ' characters in your text string or that will affect you query also.

Hope this helps.