Access Update Query from VB6 1

[ncscott]

I have a form written in VB6 that, among other things, accepts a value for the variable "varRackLocation" and one for "varSerialNumber" and then is supposed to perform the following database query:

UPDATE [tablename] SET [RackLocation] = varRackLocation where [SerialNumber] = varSerialNumber

The Query is run by an ADODB Command which uses an ADODB connection which is properly created. There are several other queries that use this same connection, but this is the only Update query, and it's the only one that isn't working. The query string is built like this:

strSQLUpdateAssets = "UPDATE [Assets] SET [RackLocation] = '" & tmpLocation & "' where [SerialNumber] LIKE '*" & tmpSerialNumber & "'"
CmdInventorySave.ActiveConnection = cnn01 '(defined earlier)
CmdInventorySave.CommandText = strSQLUpdateAssets

CmdInventorySave.Execute

When the query string is printed to the debug window and then pasted into the Access Query tool, the update works perfectly. But running it from the form has no effect. No errors are thrown, but the table data remains unchanged. Again, the same ADODB connection is being used to execute several SELECT and INSERT queries on the same DB. It is just the UPDATE query that is giving me trouble.

 

[Golom]

Little ADO-specific thing.

When you run from the query window you are in fact using DAO syntax which interprets "*" as the "zero or more characters" mask for LIKE.

However, ADO doesn't use "*". Instead it uses "%" for that purpose. Replace "LIKE '*" with "LIKE '%" and you should be OK.

 

[bjd4jc]

It is because of your wildcard character. You need to use a % instead of * when using ADO with Access.

 

[bjd4jc]

Sorry Golom, came in right behind you