CODEWITHSUNDEEP
sqlvbaexcelt-sql
RyanWilliamWest
RyanWilliamWest

Reputation: 126

Incorrect Syntax Error "JOIN" in VBA

I'm trying to learn VBA Script specifically connecting to SQL and pulling a few account details. However, I'm getting a syntax error "Incorrect Syntax near the keyword 'Join'. Could you help me find the syntax error. 

        strQuery = "" _
                        & "DECLARE @Perns TABLE(Id INT)" _
                        & " INSERT INTO @Perns (Id)" _
                        & " SELECT 4098 " _
                        & " SELECT ddm.FirstName, ddm.LastName, am.alias AS Alias, ddm.PersonnelNbr AS N'Pern#', nam.parent_OU_DN, gc.PersonGroupDesc AS N'AccManNG Status' " _
                        & " FROM @Perns AS p" _
                        & "LEFT OUTER JOIN [Accmanng].[dbo].[directory_data_master] ddm ON p.Id=ddm.PersonnelNbr" _
                        & "LEFT OUTER JOIN [Accmanng].[dbo].[alias_master] am ON am.alias_id = ddm.alias_id" _
                        & "LEFT OUTER JOIN [Accmanng].[dbo].[network_account_master] nam ON AM.alias_id = nam.alias_id" _
                        & "LEFT OUTER JOIN [Accmanng].[dbo].[dim_groupcode] gc ON ddm.PersonGroupCode=gc.PersonGroupCode " _
                        & "WHERE ddm.PersonnelNbr in ( SELECT Id FROM @perns)"


    rst.Open strQuery, ConnectString


    Sheets(1).Range("A2").CopyFromRecordset rst

Upvotes: 0

Views: 311

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Print out your code. About 19 times out of 20 that makes the error obvious.

The first few lines of your code are:

DECLARE @Perns TABLE(Id INT)
INSERT INTO @Perns (Id)
 SELECT 4098 
 SELECT ddm.FirstName, ddm.LastName, am.alias AS Alias, ddm.PersonnelNbr AS N'Pern#', nam.parent_OU_DN, gc.PersonGroupDesc AS N'AccManNG Status' 

. . .

This is not valid SQL:

SELECT 4098 SELECT ddm.FirstName

Upvotes: 1

Polynomial Proton
Polynomial Proton

Reputation: 5135

You are missing the space before the JOIN clause

      strQuery = "" _
                    & "DECLARE @Perns TABLE(Id INT)" _
                    & " INSERT INTO @Perns (Id)" _
                    & " SELECT 4098 " _
                    & " SELECT ddm.FirstName, ddm.LastName, am.alias AS Alias, ddm.PersonnelNbr AS N'Pern#', nam.parent_OU_DN, gc.PersonGroupDesc AS N'AccManNG Status' " _
                    & " FROM @Perns AS p" _
                    & " LEFT OUTER JOIN [Accmanng].[dbo].[directory_data_master] ddm ON p.Id=ddm.PersonnelNbr" _
                    & " LEFT OUTER JOIN [Accmanng].[dbo].[alias_master] am ON am.alias_id = ddm.alias_id" _
                    & " LEFT OUTER JOIN [Accmanng].[dbo].[network_account_master] nam ON AM.alias_id = nam.alias_id" _
                    & " LEFT OUTER JOIN [Accmanng].[dbo].[dim_groupcode] gc ON ddm.PersonGroupCode=gc.PersonGroupCode " _
                    & " WHERE ddm.PersonnelNbr in ( SELECT Id FROM @perns)"

EDIT: Also Gordon caught some more issues. I'm pretty sure the error you get is because of the space near join.

Upvotes: 3

Related Questions