CODEWITHSUNDEEP
sqlms-access
punkdis
punkdis

Reputation: 43

Microsoft Access does not like a table field name in a SQL Query

I have the following SQL query that I am trying to run inside Microsoft Access 2010 and I get an error( Syntax error in INSERT INTO statement) then it highlights the field "Level" if I rename the field to "Level1" it works. The problem is that I can't rename the field because I have several SQL queries in PHP that I rely on the field name "Level" Any ideas of why it would throw an error.

The reason I am running the query is to copy data from an external ODBC sqlite database using linked tables. I do have several other INSERT INTO queries working this way. If there is another way please let me know. I have the query in some VB code that I use DoCmd.RunSQL

INSERT INTO MinorStats1 ( PlayerID,YearNumber,TeamID,Level,B_PA,B_AB,B_Hits,B_Doubles,B_Triples,B_HR,B_RBI,B_BB,B_K,B_TB,B_AVG,B_SLG,B_OPS,B_OBP,B_FPCT,B_SB,B_CS,P_Wins,P_Losses,P_Saves,P_GS,P_OutsPitched,P_BF,P_Hits,P_HR,P_BB,P_K,P_ER,P_ERA)SELECT PlayerID,YearNumber,TeamID,Level,B_PA,B_AB,B_Hits,B_Doubles,B_Triples,B_HR,B_RBI,B_BB,B_K,B_TB,B_AVG,B_SLG,B_OPS,B_OBP,B_FPCT,B_SB,B_CS,P_Wins,P_Losses,P_Saves,P_GS,P_OutsPitched,P_BF,P_Hits,P_HR,P_BB,P_K,P_ER,P_ERA FROM MinorStats

Upvotes: 1

Views: 613

Answers (2)

Greg Sansom
Greg Sansom

Reputation: 20840

Enclose level in square brackets as follows:

INSERT INTO MinorStats1 ( PlayerID,YearNumber,TeamID,[Level],B_PA,B_AB,B_Hits,B_Doubles,B_Triples,B_HR,B_RBI,B_BB,B_K,B_TB,B_AVG,B_SLG,B_OPS,B_OBP,B_FPCT,B_SB,B_CS,P_Wins,P_Losses,P_Saves,P_GS,P_OutsPitched,P_BF,P_Hits,P_HR,P_BB,P_K,P_ER,P_ERA)SELECT PlayerID,YearNumber,TeamID,[Level],B_PA,B_AB,B_Hits,B_Doubles,B_Triples,B_HR,B_RBI,B_BB,B_K,B_TB,B_AVG,B_SLG,B_OPS,B_OBP,B_FPCT,B_SB,B_CS,P_Wins,P_Losses,P_Saves,P_GS,P_OutsPitched,P_BF,P_Hits,P_HR,P_BB,P_K,P_ER,P_ERA FROM MinorStats

You need to do this because level is a reserved word in Access. For a complete list of reserved words, see http://support.microsoft.com/kb/286335.

Upvotes: 3

ron tornambe
ron tornambe

Reputation: 10780

You just need to surround [Level] (which is a reserved word evidently) with brackets.

Upvotes: 2

Related Questions