Understanding Query in SQL Errors Out in VBA

Introduction

When working with data from a database using Visual Basic for Applications (VBA), errors can occur due to various reasons, including syntax mistakes or incorrect usage of certain features. In this article, we’ll delve into the world of SQL and explore why the provided query is causing an error in VBA.

Understanding SQL Syntax

SQL stands for Structured Query Language, a standard language used to interact with relational databases. The query provided works correctly on SQL Server, but it throws a runtime error when executed in Excel’s VBA editor.

The first step to resolving this issue is understanding the basics of SQL syntax. In this case, we’re dealing with an UPDATE statement that updates the Match_Date field in the Tech_Share_Attendance table by setting its value to the corresponding date from the Tech_Share table.

UPDATE Tech_Share_Attendance
SET (Match_Date) = (Tech_Share.Date)
FROM Tech_Share
WHERE Tech_Share.TechShare_ID = Tech_Share_Attendance.Tech_Share_ID;

VBA and SQL Integration

To integrate SQL with VBA, we use the ADO (ActiveX Data Objects) library, which provides a set of components for accessing and managing data in various databases.

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

The Error: Syntax Error

The error message (80004005) "near FROM: syntax error (1)." indicates a syntax error in the query. The FROM keyword is used to specify the table from which to retrieve data, but in this case, it’s missing.

strsql = "UPDATE Tech_Share_Attendance SET Match_Date = Tech_Share.Date FROM Tech_Share WHERE Tech_Share.TechShare_ID = Tech_Share_Attendance.Tech_Share_ID"

Quotation Marks and Apostrophes

The original query uses apostrophes (') to enclose the table names, whereas in VBA, we typically use square brackets ([]) or double quotes ("") instead.

strsql = "UPDATE Tech_Share_Attendance SET Match_Date = [Tech_Share.Date] FROM Tech_Share WHERE Tech_Share.TechShare_ID = Tech_Share_Attendance.Tech_Share_ID"

Resolving the Error

To resolve this error, we need to modify the query in VBA to match the syntax used on SQL Server. We’ll also use square brackets ([]) instead of double quotes (") to enclose table names.

strsql = "UPDATE Tech_Share_Attendance SET Match_Date = [Tech_Share.Date] FROM Tech_Share WHERE Tech_Share.TechShare_ID = Tech_Share_Attendance.Tech_Share_ID"

Full Code Example

To demonstrate the corrected query, let’s take a look at the full code example:

Private Sub btnEdit_Click()
Unload frmTechShare_Attend
frmTechShare_Lookup.Show
End Sub

Private Sub lblName_Click()
End Sub

Private Sub listRotations_Click()
End Sub

Private Sub UserForm_Initialize()
Dim Name As String
Name = getAssociateName()
lblName.Caption = Name

AssociateID = getAssociateID()
ContactKey = getContactKey()

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=\\xxx\xxx\xxx\xxx\xxx\xxx;"
strsql = "UPDATE Tech_Share_Attendance SET Match_Date = [Tech_Share.Date] FROM Tech_Share WHERE Tech_Share.TechShare_ID = Tech_Share_Attendance.Tech_Share_ID"

' OPEN RECORDSET[
Debug.Print (strsql)
rst.Open strsql, conn

If rst.EOF = False Then
    With Me.listAttendance
        .Clear
        Do
            Tech_Share_ID = rst![Tech_Share_ID]
            Match_Date = rst![Match_Date]
            .AddItem (Tech_Share_ID + " | " + Match_Date)
            rst.MoveNext
        Loop Until rst.EOF
    End With
End If
End Sub

Private Sub listAttendance_Click()
End Sub

Conclusion

In this article, we explored the issue of a query in SQL causing an error when executed in VBA. We discussed the importance of understanding SQL syntax and how to correctly integrate it with VBA using ADO.

By modifying the query to match the syntax used on SQL Server and using square brackets ([]) instead of double quotes (") for table names, we were able to resolve the error and successfully execute the query in VBA.

Last modified on 2024-01-09