Insert Query - VBA

[Trevor G]

I am trying to get around creating an Append Query by using the Insert Statement in a VBA module.

This is the Query SQL that I have when I create the query in design view.

INSERT INTO tblAbsenceRegister ( EmployeeID, AbsenceType, StartDate, EndDate, HalfDay, StartTime, FinishTime )
SELECT tblEmployeeNames.ID, tblTempImport.AbsenceType, tblTempImport.StartDate, tblTempImport.EndDate, tblTempImport.HalfDay, tblTempImport.StartTime, tblTempImport.EndTime
FROM tblTempImport LEFT JOIN tblEmployeeNames ON tblTempImport.From=tblEmployeeNames.EmployeeNames;

And this is what I am trying to convert it to using VBA.

Sub updateAbsenceType()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "INSERT INTO tblAbsenceRegister (EmployeeID,AbsenceType,StartDate,EndDate,HalfDay,StartTime,FinishTime)"
SQL = SQL & "SELECT tblEmployeeNames.ID,tblTempImport.AbsenceType,tblTempImport.StartDate,tblTempImport.EndDate,tblTempImport.HalfDay, tblTempImport.StartTime,tblTempImport.EndTime"
SQL = SQL & "FROM tblTempImport LEFT JOIN tblEmployeeNames ON tblTempImport.From = tblEmployeeNames.EmployeeNames;"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End Sub

This is all part of multiple applications being used, so excel, outlook and access. I have all the other things working at this time but not this code.

I get a run time error 3075 Missing Operator.

 

[spikepl]

so where is your

debug.print SQL?

and what is its output?

 

[Trevor G]

I have added the debug.print statement to run before the DoCmd statement and it shows me this

INSERT INTO tblAbsenceRegister (EmployeeID,AbsenceType,StartDate,EndDate,HalfDay,StartTime,FinishTime)SELECT tblEmployeeNames.ID,tblTempImport.AbsenceType,tblTempImport.StartDate,tblTempImport.EndDate,tblTempImport.HalfDay, tblTempImport.StartTime,tblTempImport.EndTimeFROM tblTempImport LEFT JOIN tblEmployeeNames ON tblTempImport.From = tblEmployeeNames.EmployeeNames;

 

[plog]

I think you need to add spaces to the end, or beginning of each line that contains SQL. You've essentially got this:

SQL ="Some text here"
SQL= SQL & "some more text here"

When you add that together you get:

"Some text heresome more text here"

You need to add spaces otherwise when you concatenate data to SQL its going to go right onto the end of the last line in there.

 

[Trevor G]

Excellent that did the trick. Just added a space after the quote marks worked. Thank you.