Solved Mail Merge vba SQLStatement syntax error with join (1 Viewer)

Amith

New member
Local time
Today, 23:20
Joined
Jul 16, 2021
Messages
2
Hi, I'm having issues trying to get the following simple SELECT to run as part of a word mail merge using vba .


Code:
    UserName = (Environ$("Username"))
    sDBPath = "U:\" & UserName & "\db.accdb"

    SQL = "SELECT * FROM [TestName] T INNER JOIN [TestNameData] D ON T.TestNameId = D.TestNameId"

    Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDBPath & ";"

    DefaultName = ActiveDocument.Name
    Fname = ActiveDocument.BuiltInDocumentProperties("Title")
    With ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=sDBPath, _
            LinkToSource:=True, _
            Connection:=Connection, _
            SQLStatement:=SQL
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        .Execute Pause:=False

'[ Rest of code Snipped]

I've tried a few variations on the SQL each with different syntax related errors eg:
"SELECT * FROM [TestName] T INNER JOIN [TestNameData] D ON (T.TestNameId = D.TestNameId)"
"SELECT * FROM ([TestName] T INNER JOIN [TestNameData] D ON (T.TestNameId = D.TestNameId))"

etc including with and without a trailing semicolon.

The error that from the first SQL statement (in the main code block) is:
Error1.png


I'm well versed in T-SQL but not Access's SQL syntax, however all of these seem to work when executed from Access.

I have also tried a few variations on the vba code, such as excluding a Connection argument.

Any help is appreciated. The solution is probably blinding obvious, but I can't see it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:20
Joined
May 7, 2009
Messages
19,169
not sure if this will work:

SQL = "SELECT T.*, D.* FROM [TestName] T INNER JOIN [TestNameData] D ON T.TestNameId = D.TestNameId"

also, just add any desciption to Connection propety, ex:

Connection := "joined table"
 
Last edited:

Amith

New member
Local time
Today, 23:20
Joined
Jul 16, 2021
Messages
2
not sure if this will work:

SQL = "SELECT T.*, D.* FROM [TestName] T INNER JOIN [TestNameData] D ON T.TestNameId = D.TestNameId"

Thanks a lot, it worked! (y)

I could have sworn I had tried this one, I usually default to it; I am in the process of shortening a longer query to fix inside the char limit, so I must have swapped them out for the single SELECT *. Thought I was going to be stuck with the old syntax of joining in the WHERE predicate. :LOL:
 

Users who are viewing this thread

Top Bottom