JOIN expression not supported

[merebag]

I am slowly converting my Access ADP to DAO/Odbc linked Access 2013 to a 2012 SQL server. I have some problems when trying to join tables in a .runSQL access command. It comes back with a "JOIN expression not supported" error.

Here is my code:



With Access.DoCmd
.RunSQL "INSERT INTO TempChildFind(ChildId, AcctId, CurrSite, SecSite, ThirdSite, PrimarySite, AuditId) " _
& "SELECT RefChildID, AccountId, CurrentSite,SecondarySite, TertiarySite, BillingSiteID, " & AuditID & " " _
& "FROM AccountChildReference " _
& "INNER JOIN AccountMaster ON AccountId = acct_ID " _
& "WHERE CurrentSite = " & Me.comboSelectSite & " Or " _
& "SecondarySite = " & Me.comboSelectSite & " OR TertiarySite = " & Me.comboSelectSite & " ORDER BY " _
& "AliasLName asc, AliasFName asc, AccountID asc
End With

This INSERT INTO/SELECT statement works fine when running it in SQL Management Studio, but gets the Join support error when running it in VBA.

Should I be using the .runSQL docmd in this case?

[ranman256]

JOIN is the basis for ALL queries, it should work.

Unless this is a false error and your SQL is incorrect.
Load the sql into a query, it will help you correct it.

[orange]

?? there is no double quote after the last asc ?? may just be a typo in the post

Review the Currentdb.execute your sql here command as a replacement for RunSql

[merebag]

Thanks for the input. However, I did find what the problem was. In order to do the Join that I was doing, I had to fully qualify the names of the fields with the appended table name. So, AccountID became AccountChildReference.AccountId. Once I did that, it worked fine.


Thanks