Passing Access SQL query through Excel VBA Code

[brian_msbc]

This has been working for me and all the sudden it does not... Here is my query:

Insert INTO tblPartnerCreditRating (PartnerID,QuestionID,YearValue,Fitch,Moodys,SP,Local) VALUES ('Partner name', 'G3.1', '2014', 'NA', 'Aa3', 'AA-', 'NA')

When I run this through Access is works perfectly, however when I run this through Excel VBA I get this error:

Run-time error '-2147217900 (80040e14)

Insert INTO Syntax error

Any clue?

 

[Ranman256]

access is very forgiving inside its walls.
Excel may want more standard sql and ADO connections.

how ARE you connecting to the backend?

 

[brian_msbc]

This is how I'm connecting:

 Dim conn As New Connection
Dim rs As New Recordset
    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\localprofilepath.accdb;"
conn.Open (strcon)

then

                            qry = "Insert INTO tblPartnerCreditRating (PartnerID,QuestionID,YearValue,Fitch,Moodys,SP,Local) VALUES ('" & wb.name & "', '" & RemoveQuoteMark(R.Value) & "', '" & RemoveQuoteMark(WS1.Cells(p, 2).Value) & "', '" & RemoveQuoteMark(WS1.Cells(p, 4).Value) & "', '" & RemoveQuoteMark(WS1.Cells(p, 5).Value) & "', '" & RemoveQuoteMark(WS1.Cells(p, 6).Value) & "', '" & RemoveQuoteMark(WS1.Cells(p, 7).Value) & "');"
                            rs.Open qry, conn, adOpenKeyset

 

[brian_msbc]

again, what kills me is that I am doing the exact same scenario elsewhere in my code successfully, which leads me to believe its a table data mismatch, but when I use that same insert query in Access, it enters the data correctly with no error.

 

[Galaxiom]

Debug.Print the query command text after it has been assembled. It usually reveals the problem.

 

[brian_msbc]

FYI, I finally solved this... a week later.... by putting brackets around the field names:

Insert INTO tablename ([Field1],[Field2],[Field3]) VALUES ('Value1', 'Value2', 'Value3')

Now, I've been successful without brackets in the past, my guess is that certain spaces or characters require brackets, and was throwing off my code. In any case, there doesn't appear to be any negative to using brackets so ill probably just use brackets going forward.