Help: Access97 SQL Fails with ADO 2.5

[pcawdron]

I have an Insert query that runs beautifully in Access 97 but fails to produce any results when run from VB6 using ADO 2.5 Command.

I've figured out what the problem is, but I haven't figured out the solution. The query contains the clause...

WHERE DEALER = "This Dealer" AND INVOICE_TYPE LIKE "Tax*"

If I remove this from the query it run's, but it's pulling too many records.

I've tried the Providers MSDASQL.1, Jet OLEDB 3.51 and 4.0.

I've tried the following strings

WHERE DEALER = 'This Dealer' AND INVOICE_TYPE LIKE 'Tax*'
WHERE DEALER = """This Dealer""" AND INVOICE_TYPE LIKE """Tax*"""

The query executes without error, takes a few seconds, but the database is not updated. Anyone else come across this?

PS. This is also listed under the VB Forum & Access SQL Forum

 

[DougP]

Is the data in SQL Server 7 ???
Try adding Parenthesis
WHERE ([DEALER] = 'This Dealer') AND ([INVOICE_TYPE] LIKE 'Tax%')


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[pcawdron]

No, it's not in SQL Server - it's Access 97. I tried your suggestion, but, alas, with no success.

Since then I have been able to get a simplified INSERT query to work with quotes, but I still can't get the query I need working. What's baffling me is that it works fine in Access97, it just doesn't do anything when run from VB6 thru ADO

In VB6 I'm using a string to distinguish b/w dealers, it looks something like this

xSQL = "INSERT INTO Comments ( Comment )
SELECT NewComments.NewComment
FROM NewComments
WHERE (((NewComments.NewComment)=" & chr(34) & VarNewComment & chr(34) & &quot);"

But for some reason ADO doesn't like the SQL. I've tried...

xSQL = "INSERT INTO Comments ( Comment )
SELECT NewComments.NewComment
FROM NewComments
WHERE (((NewComments.NewComment)='" & VarNewComment & "'));"

But that doesn't work either...

Any ideas??? Any comments about ADO???