CODEWITHSUNDEEP
sql-serverms-accessvba
TomBridges
TomBridges

Reputation: 53

Access VBA ODBC connection failed

I have an Access Frontend with a SQL Server backend.

On one of the forms, there is a bit of VBA to keep an "Audit Log" of the changes.

In one procedure there are these 2 bits of code, the first works, but the second gives an error

Working:

sSQL = "DELETE FROM [dbo_EventReport_Audit_Temp_Before];"
CurrentProject.Connection.Execute sSQL

Not working a few lines down:

sSQL = "INSERT INTO [dbo_EventReport_Audit_Temp_Before] (<TABLE COLUMNS>) SELECT <TABLE COLUMNS> FROM [dbo_EventReport] WHERE ((Event_ID)= <EVENT_ID>");"
CurrentProject.Connection.Execute sSQL

So the first statement deletes any records in the table. This works fine, as I've inserted dummy data and stepped through the code and seen it be deleted.

But the second statement causes an error:

Error -2147467259: ODBC--connection to 'EventsDB' failed.

Anyone any idea why the first statement works ok, but the second fails?

Extracting the value of sSQL for the second statement and manually running it through an SQL Query in Access inserts the data into the table.

EDIT: I didn't want to post the full statement as it's a bit of a monster. But here is is: 

sSQL = "INSERT INTO " & sAudTmpTable & " ( [audType], [audDate], [audUser], [Event_Number], [Event_ID], " & _
            "[Received_Date], [Response_Date], [Site], [Server], [Workstation], [Software_Version], [Data_Version], " & _
            "[Description], [Test_Description], [Company], [Observed_By], [Observed_Date], [Tested_By], [AssignedTo], " & _
            "[Tested_Date], [Test_Result], [Ind_Tested_By], [Ind_Tested_On], [Ind_Test_Result], [Reviewed_By], " & _
            "[Actioned_Date], [Review_Date], [Review_Result], [Completed_By], [Completed_Date], [Closed_By], " & _
            "[Closed_Date], [Exclude], [Category], [State], [Event_Responsibility], [Probability], [WIP_Number], " & _
            "[OriginalWIP], [Severity], [Blocked], [Block_Description], [Tags], [Work], [TaskID], [EventType], " & _
            "[DefectType], [known_issue_impact], [known_issue_description], [Operator_Notes], [BugWIP], " & _
            "[SupplierName], [SupplierCompany], [Simulator], [ATSTest], [FixPriority] ) " & _
            "SELECT '" & EditOrInsert & "' AS Expr1, '" & DateTime & "', '" & User & "', [Event_Number], [Event_ID], " & _
            "[Received_Date], [Response_Date], [Site], [Server], [Workstation], [Software_Version], [Data_Version], " & _
            "[Description], [Test_Description], [Company], [Observed_By], [Observed_Date], [Tested_By], [AssignedTo], " & _
            "[Tested_Date], [Test_Result], [Ind_Tested_By], [Ind_Tested_On], [Ind_Test_Result], [Reviewed_By], " & _
            "[Actioned_Date], [Review_Date], [Review_Result], [Completed_By], [Completed_Date], [Closed_By], " & _
            "[Closed_Date], [Exclude], [Category], [State], [Event_Responsibility], [Probability], [WIP_Number], " & _
            "[OriginalWIP], [Severity], [Blocked], [Block_Description], [Tags], [Work], [TaskID], [EventType], " & _
            "[DefectType], [known_issue_impact], [known_issue_description], [Operator_Notes], [BugWIP], " & _
            "[SupplierName], [SupplierCompany], [Simulator], [ATSTest], [FixPriority] " & _
            "FROM [" & sTable & "] WHERE ((" & sKeyField & ")=" & lngKeyValue & ");"

Upvotes: 1

Views: 993

Answers (1)

HansUp
HansUp

Reputation: 97101

You reported this attempt fails ...

CurrentProject.Connection.Execute sSQL

... but this works using the same sSQL statement ...

CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges

CurrentProject.Connection.Execute is an ADO method. CurrentDb.Execute is a DAO method. The two methods are similar, but not the same.

One important difference is the ADO version is more likely to fail when the SQL statement includes reserved words as object (table, field, etc.) names; DAO is more forgiving about problem names.

But there are other differences, and it is not possible to determine which of them was the key factor for an INSERT statement we haven't seen. ;-)

Upvotes: 1

Related Questions