CODEWITHSUNDEEP
sqlvbaexcel
Jason Samuels
Jason Samuels

Reputation: 971

Updating records with ADODB Recordset

I need to update a recordset in Excel VBA. I extract the data from a CSV and use the Text driver to connect to the file. Here is my connection to the file, recordset and update code.

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
con.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Users\jasons\Documents\sl;Extensions=csv;"

rs.Open "SELECT *, 0 As item FROM Pct.csv where [Sku No] in ('123455','123456')", con, adOpenDynamic, adLockOptimistic


rs.MoveFirst
Do Until rs.EOF
rs.Fields(0) = 2
rs.MoveNext
Loop

But I keep getting the following error message at the rs.MoveNext line.

-2147467259-[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression '(item=Pa_RaM001 AND Style Code=Pa_RaM002 AND Sku No=Pa_RaM003'.

When i remove the loop and update the first record as

rs.MoveFirst
rs.Fields(0) = 2

then it works. I looked on the internet for examples and but i can not see what i am doing wrong. I would appreciate any assistance.

Upvotes: 3

Views: 3612

Answers (1)

Axel Richter
Axel Richter

Reputation: 61862

You could use the following approach to generate CSV files with ADODB. UPDATE CSV files is definitely a very bad idea. ALTER TABLE is also impossible in my opinion. So the approach will CREATE a new temporary table with the new field. Then copying the data from the old table and setting the data of the new field. And at last swapping the temporary table into the CSV file.

Sub ADODB_CSV()

 Dim con As New ADODB.Connection

 con.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Users\jasons\Documents\sl;Extensions=csv;"

 On Error Resume Next
 con.Execute "DROP TABLE temp.csv"
 On Error GoTo 0

 con.Execute "CREATE TABLE temp.csv (item integer, [Style Code] text, [Sku No] text)"

 con.Execute "INSERT INTO temp.csv (item, [Style Code], [Sku No]) SELECT 0, [Style Code], [Sku No] FROM Pct.csv WHERE [Sku No] NOT IN ('123455','123456')"
 con.Execute "INSERT INTO temp.csv (item, [Style Code], [Sku No]) SELECT 2, [Style Code], [Sku No] FROM Pct.csv WHERE [Sku No] IN ('123455','123456')"

 con.Execute "DROP TABLE Pct.csv"
 con.Execute "CREATE TABLE Pct.csv (item integer, [Style Code] text, [Sku No] text)"

 con.Execute "INSERT INTO Pct.csv SELECT * FROM temp.csv"

 con.Execute "DROP TABLE temp.csv"

 con.Close

End Sub

Upvotes: 3

Related Questions