SQL Error, possible newbie error

Question

I'm inserting SQL INSERT INTO statements in a VBA script using ODBC to pull data from a word doc and put it into an access database automatically.

I've verified the data isn't a mismatch and I have compared it to the other INSERT INTO statements which run without issue. These statements are linked to string variables that are then used in the cnn.execute command.

The code to store and execute my variables is as follow:

Sub TransferInstructorInfo()

'Transfer new Instructor Information record to DB

Dim cnn As ADODB.Connection

Dim strConnection As String

Dim InstTable As String

Dim EduTable As String

Dim EmpTable As String

Dim RefTable As String

Dim strPath As String

Dim doc As Word.Document

' Define Doc Variables

 Dim Campus As String
 Dim DOE As String
 Dim Fname As String
 Etc...
'Link defined variables to forms in the word doc

Campus = Chr(39) & doc.FormFields("Campus").Result & Chr(39)
DOE = Chr(39) & doc.FormFields("HireDate").Result & Chr(39)
Fname = Chr(39) & doc.FormFields("FirstName").Result & Chr(39)
Mname = Chr(39) & doc.FormFields("MiddleName").Result & Chr(39)
Nname = Chr(39) & doc.FormFields("NickName").Result & Chr(39)
etc...

InstTable = "INSERT INTO Instructors (InstructorID,[First Name],[Middle Initial],[Last Name],Nickname,Campus,[Job Title],Address,City,State,Zip,Phone,DOB,SSN,DOE)" _
& "SELECT " & InstructorID & "," & Fname & "," & Mname & "," & Lname & "," & Nname & "," & Campus & "," & Job & "," & Address & "," & City & "," & State & "," & Zip & "," & Phone & "," & DOB & "," & SSN & "," & DOE & ";" 


EmpTable = "INSERT INTO [Employment History] ( InstructorID, Company1, CompAdd1, Comp1Supervisor, CompPhone1, Comp1From, Comp1To, Comp1Title, Comp1Description, Company2, CompAdd2, Comp2Supervisor, CompPhone2, Comp2From, Comp2To, Comp2Title, Comp2Description, Company3, CompAdd3, Comp3Supervisor, CompPhone3, Comp3From, Comp3To, Comp3Title, Comp3Description, Company4, CompAdd4, Comp4Supervisor, CompPhone4, Comp4From, Comp4To, Comp4Title, Comp4Description, Company5, CompAdd5, Comp5Supervisor, CompPhone5, Comp5From, Comp5To, Comp5Title, Comp5Description, Company6, CompAdd6, Comp6Supervisor, CompPhone6, Comp6From, Comp6To, Comp6Title, Comp6Description, Company7, CompAdd7, Comp7Supervisor, CompPhone7, Comp7From, Comp7To, Comp7Title, Comp7Description )" _
& "SELECT " & InstructorID & "," & Comp1 & "," & Comp1Add & "," & Comp1sup & "," & Comp1Phone & "," & comp1from & "," & comp1To & "," & Comp1Title & "," & Comp1Desc & "," & Comp2 & "," & Comp2Add & "," & Comp2sup & "," & Comp2Phone & "," & comp2from & "," & comp2To & "," & Comp2Title & "," & Comp2Desc & "," & Comp3 & "," & Comp3Add & "," & Comp3sup & "," & Comp3Phone & "," & comp3from & "," & comp3To & "," & Comp3Title & "," & Comp3Desc & "," & Comp4 & "," & Comp4Add & "," & Comp4sup & "," & Comp4Phone & "," & comp4from & "," & comp4To & "," & Comp4Title & "," & Comp4Desc & "," & Comp5 & "," & Comp5Add & "," & Comp5sup & "," & Comp5Phone & "," & comp5from & "," & comp5To & "," & Comp5Title & "," & Comp5Desc & "," & Comp6 & "," & Comp6Add & "," & Comp6sup & "," & Comp6Phone & "," & comp6from & "," & comp6To & "," & Comp6Title & "," & Comp6Desc & "," & Comp7 & "," & Comp7Add & "," & Comp7sup & "," & Comp7Phone & "," & comp7from & "," & comp7To & "," & Comp7Title & "," & Comp7Desc & ";"


RefTable = "INSERT INTO References ( InstructorID, CharRef1, CRAddress1, CRPhone1, CharRef2, CRAddress2, CRPhone2, CharRef3, CRAddress3, CRPhone3 )" & "SELECT " & InstructorID & "," & charref1 & "," & CRAddress1 & "," & CRPhone1 & "," & charref2 & "," & CRAddress2 & "," & CRPhone2 & "," & charref3 & "," & CRAddress3 & "," & CRPhone3 & ";"

EduTable = "INSERT INTO [Education History] ( InstructorID,Sch1,Sch1Address,Sch1From,Sch1To,Sch1GradDate,Sch1DegreeType,Sch2,Sch2Address,Sch2Program,Sch2From,Sch2To,Sch2GradDate,Sch2DegreeType,TradeSchool,TSAddress,TCourse,TSFrom,TSTo,TSGradDate,TSDegreeType,[Highest Degree Earned],[Highest Awarding Institute],[ASE Certified])" _
& "SELECT " & InstructorID & "," & Sch1 & "," & Sch1Add & "," & Sch1from & "," & Sch1to & "," & Sch1Graddate & "," & Sch1Degree & "," & Sch2 & "," & Sch2Address & "," & sch2program & "," & sch2from & "," & sch2to & "," & Sch2Graddate & "," & sch2degree & "," & TSName & "," & TSAdd & "," & TSprogram & "," & TSfrom & "," & TSto & "," & TSgrad & "," & TSDegree & "," & Highestedu & "," & Highestdegree & "," & ASE & ";"

Debug.Print EduTable
Debug.Print EmpTable

Set cnn = New ADODB.Connection

'DSN connection Set DSN locally if it fails, ODBC panel USER DSN, use access driver and point it to the database

cnn.Open "Instructor"


cnn.Execute InstTable

cnn.Execute RefTable

cnn.Execute EmpTable

cnn.Execute EduTable <----Here is where the debug breaks.

The first three insert statements work The edu table does not.

All three of these work correctly by pulling the data, here is the query that fails and I can't figure out why.

"INSERT INTO [Education History] ( InstructorID,Sch1,Sch1Address,Sch1From,Sch1To,Sch1GradDate,Sch1DegreeType,Sch2,Sch2Address,Sch2Program,Sch2From,Sch2To,Sch2GradDate,Sch2DegreeType,TradeSchool,TSAddress,TCourse,TSFrom,TSTo,TSGradDate,TSDegreeType,[Highest Degree Earned],[Highest Awarding Institute],[ASE Certified])" _
& "SELECT " & InstructorID & "," & Sch1 & "," & Sch1Add & "," & Sch1from & "," & Sch1to & "," & Sch1Graddate & "," & Sch1Degree & "," & Sch2 & "," & Sch2Address & "," & sch2program & "," & sch2from & "," & sch2to & "," & Sch2Graddate & "," & sch2degree & "," & TSName & "," & TSAdd & "," & TSprogram & "," & TSfrom & "," & TSto & "," & TSgrad & "," & TSDegree & "," & Highestedu & "," & Highestdegree & "," & ASE & ";"

The VBA debugger tells me it is an invalid insert statement, and the online checkers tell me it fails around [Education History], what am I'm missing here?

I do realize I can use transactions and accomplish the same thing but I believe I'd still run into an error with my INSERT INTO statements.

Any help is greatly appreciated, been stuck on this one for awhile.

Answer 1

Also, since your are a self-declared newbie, we would be remiss not to recommend for you or future readers to parameterize your SQL queries (an industry best practice) which avoids any quote enclosure, runs a bit more efficiently, and forces you as developer to explicitly define datatypes of each value (which helps avoid missing a few).

Below are both ADO and DAO methods with example for one query. While it may seem intimidating and too much new lines of code, still consider it as it safely bind values from your user form from Word document to keep database secure from sql injection. Some clever user of yours may try sabotaging your work! Notice id, date and string types being defined.

ADO Method (which you currently use)

Dim cmd As ADODB.Command
...
Campus = doc.FormFields("Campus").Result
DOE = doc.FormFields("HireDate").Result
Fname = doc.FormFields("FirstName").Result
Mname = doc.FormFields("MiddleName").Result
Nname = doc.FormFields("NickName").Result
...

InstTable = "INSERT INTO Instructors (InstructorID, [First Name], [Middle Initial]," _
             & " [Last Name], Nickname, Campus, [Job Title]," _
             & " Address, City, State, Zip, Phone, DOB, SSN, DOE) " _
             & " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" 

Set cmd = New ADODB.Command          ' RESET CMD WITH EACH SQL QUERY 
With cmd
    .ActiveConnection = conn
    .CommandText = InstTable
    .CommandType = adCmdText
    .CommandTimeout = 45
End With

cmd.Parameters.Append cmd.CreateParameter("InstructorIDParam", adInteger, adParamInput, InstructorID)
cmd.Parameters.Append cmd.CreateParameter("FNameParam", adVarChar, adParamInput, FName)
cmd.Parameters.Append cmd.CreateParameter("MNameParam", adVarChar, adParamInput, MName)
cmd.Parameters.Append cmd.CreateParameter("LNameParam", adVarChar, adParamInput, LName)
cmd.Parameters.Append cmd.CreateParameter("NnameParam", adVarChar, adParamInput, Nname)
cmd.Parameters.Append cmd.CreateParameter("CampusParam", adVarChar, adParamInput, Campus)
cmd.Parameters.Append cmd.CreateParameter("JobParam", adVarChar, adParamInput, Job)
cmd.Parameters.Append cmd.CreateParameter("AddressParam", adVarChar, adParamInput, Address)
cmd.Parameters.Append cmd.CreateParameter("CityParam", adVarChar, adParamInput, City)
cmd.Parameters.Append cmd.CreateParameter("StateParam", adVarChar, adParamInput, State)
cmd.Parameters.Append cmd.CreateParameter("ZipParam", adVarChar, adParamInput, Zip)
cmd.Parameters.Append cmd.CreateParameter("PhoneParam", adVarChar, adParamInput, Phone)
cmd.Parameters.Append cmd.CreateParameter("DOEParam", adDate, adParamInput, DOE)
cmd.Parameters.Append cmd.CreateParameter("SSNParam", adVarChar, adParamInput, SSN)

cmd.Execute
Set cmd = Nothing

DAO Method

' SET VBA reference: Microsoft Office ##.0 Access database engine object library 

Dim db As DAO.Database, qd As DAO.QueryDef

Set db = OpenDatabase("C:\Path\To\Database.accdb", True)

InstTable = "PARAMETERS InstructorIDParam Long, FnameParam Text(255), MnameParam Text(255), LnameParam Text(255),"_ 
             & "         NnameParam Text(255), CampusParam Text(255), JobParam Text(255), AddressParam Text(255)," _ 
             & "         CityParam Text(255), StateParam Text(255), ZipParam Text(255), PhoneParam Text(255)," _
             & "         DOBParam Text(255), SSNParam Text(255), DOEParam DateTime;" _
             & " INSERT INTO Instructors (InstructorID, [First Name], [Middle Initial], [Last Name], Nickname," _
             & "             Campus, [Job Title], Address, City, State, Zip, Phone, DOB, SSN, DOE) " _
             & " VALUES (InstructorIDParam, FnameParam, MnameParam, LnameParam, NnameParam, CampusParam, JobParam," _ 
             & "         AddressParam, CityParam, StateParam, ZipParam, PhoneParam, DOBParam, SSNParam, DOEParam);" _

Set qd = db.CreateQueryDef("", InstTable)

qd!InstructorIDParam = InstructorID
qd!FNameParam = FName
qd!MNameParam = MName
qd!LNameParam = LName
qd!NnameParam = Nname
qd!CampusParam = Campus
qd!JobParam = Job
qd!AddressParam = Address
qd!CityParam = City
qd!StateParam = State
qd!ZipParam = Zip
qd!PhoneParam = Phone
qd!DOEParam = DOE
qd!SSNParam = SSN

qd.Execute

Answer 2

Figured it out, I wasn't looking hard enough

I mistyped two variables in the Edu query.

I'd like to thank everyone for their help, specifically @Tim Williams for debug print. I noticed a few variables outputted as ,, instead of their actual entry when I followed his advice and put the output in a query.