SQL Incompatibility between DAO and ADO

[bobsmythe]

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

[parksie]

Try these:

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

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

[Clunietp]

Your syntax is fine, except a minor difference between ADO and DAO are the wildcard characters.

DAO uses the asterisk ( * )

while

ADO uses the percent ( % )

as their respective wildcards. Replace your asterisk with a percent and you should be fine

[parksie]

*oops* sorry...I did know that...but I forgot. Thanks for pointing it out Clunietp...I'll know next time I need to use ADO (I don't use it much, but didn't have too many problems).