CODEWITHSUNDEEP
vba
Zaid Shaikh
Zaid Shaikh

Reputation: 49

ADODB VBA SQL Query for update

I have a macro which update the MS Access file and then move the data from one table to another table. With the below coding, I am able to move the data from one table to another table but data is not getting updated.

enter code hereDim pdate As Date With Acon .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=" + myFileNameDir .Properties("Jet OLEDB:Database Password") = "synpass" .Open End With

Issue is in this code

Set CMD = CreateObject("ADODB.Command")
CMD.ActiveConnection = Acon
strSQL = "Update Detail set Trackerpulldate = pdate where [Overall status]= 'Completed'"
CMD.CommandText = strSQL
CMD.Execute
CMD.Save

Below code is running fine with proper output

On Error Resume Next
Set CMD2 = CreateObject("ADODB.Command")
CMD2.ActiveConnection = Acon
strSQL2 = "INSERT INTO Updated_Tracker SELECT * From Detail where [Overall status]= 'Completed'"
CMD2.CommandText = strSQL2
CMD2.Execute

Acon.Close
Set Acon = Nothing
On Error GoTo 0

Upvotes: 0

Views: 53

Answers (2)

ValNik
ValNik

Reputation: 5643

Try this

Set CMD = CreateObject("ADODB.Command")
CMD.ActiveConnection = Acon
strSQL = "Update Detail set Trackerpulldate = @pdate where [Overall status]= 'Completed'"
CMD.CommandText = strSQL
CMD.Parameters.Append CMD.CreateParameter("@pdate",adDate, adParamInput, , pdate)
CMD.Execute
CMD.Save

why not just a simple string concatenation?

Formating date value for string concatenation in query text is provider or server specific, often causes errors on server side or ADODB provider.

Upvotes: 0

Lord-JulianXLII
Lord-JulianXLII

Reputation: 1249

I'm assuming pdate is supposed to be a variable (containing a date)?!

"Update Detail set Trackerpulldate = pdate where [Overall status]= 'Completed'"

As it stands right now you are never actually referring to the variable pdate - I's just part of a string. You are basically trying to update Trackerpulldate to the value pdate not the value the variable pdate holds.

See the code below that removes all unnecessary code and shows the problem.

Dim pdate As Long
pdate = 10
Debug.Print "pdate"

Upvotes: 0

Related Questions