Syntax Error - INSERT INTO Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am pulling my hair out with this Syntax issue, I am trying to load in a
linked Spreadsheet and then append some fields from this into a table that is
sitting on another drive but I am connecting to it via ADO. For some reason
when executing the code it doesnt seem to like my SQL statement and I cant
find out where, or why

Can anyone point me in the right direction?

Private Sub cmdImport_Click()
'cTables is named as a Public Constant (z:\CSITables.mdb)
Dim cnn As ADODB.Connection
Dim sQRY As String
DoCmd.SetWarnings False
strTempTable = "tblCSATAddressTEMP"
strFilePath = Me.txtFilePath
'Check that BusinessType has been selected- quit if not
If IsNull(Me.cboBusinessType) Or Me.cboBusinessType = "" Then
MsgBox "You must select a Business Type from the dropdown list",
vbExclamation, cApplicationName
Exit Sub
End If
If VBA.Len(strFilePath) <> 0 Then
'Import the spreadsheet
DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
"Data Source=" & cTables
'insert new addresses into tblAddress
sQRY = _
"INSERT INTO tblCSATAddressA IN '" & cTables & "' " & vbCrLf & _
"( JobNumber, Address, ProjectID, Project, JobDate, TeamCode,
Engineer, Contract, BusinessType )" & vbCrLf & _
"SELECT" & vbCrLf & _
"tblCSATAddressTEMP.[No]," & vbCrLf & _
"tblCSATAddressTEMP.Description," & vbCrLf & _
"tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
"tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
"tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
"tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
"tblFamilyTree.Engineer," & vbCrLf & _
"tblFamilyTree.Contract," & vbCrLf & _
"'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf & _
"FROM tblCSATAddressTEMP " & vbCrLf & _
"LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] =
tblFamilyTree.TeamCode"
CurrentProject.Connection.Execute sQRY
End If
'DoCmd.DeleteObject acTable, strTempTable
MsgBox Me.cboBusinessType & " Data has been imported",
vbExclamation, cApplicationName
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub
 
This is from the debug.print

debug.Print sQRY
INSERT INTO tblCSATAddressA IN 'Z:\CSITables.mdb'
( JobNumber, Address, ProjectID, Project, JobDate, TeamCode, Engineer,
Contract, BusinessType )
SELECT
tblCSATAddressTEMP.[No],
tblCSATAddressTEMP.Description,
tblCSATAddressTEMP.[Bill-to Customer No],
tblCSATAddressTEMP.[Scheme Code],
tblCSATAddressTEMP.[Planned Start Date],
tblCSATAddressTEMP.[Team Code],
tblFamilyTree.Engineer,
tblFamilyTree.Contract,
'CHI' AS BusinessType
FROM tblCSATAddressTEMP
LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] =
tblFamilyTree.TeamCode

Alex Dybenko said:
Hi,
get result of your query statement into debug window:
?sQRY

and then paste in into new query sql view - access will tell you what is
wrong there


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Jez said:
Hi, I am pulling my hair out with this Syntax issue, I am trying to load
in a
linked Spreadsheet and then append some fields from this into a table that
is
sitting on another drive but I am connecting to it via ADO. For some
reason
when executing the code it doesnt seem to like my SQL statement and I cant
find out where, or why

Can anyone point me in the right direction?

Private Sub cmdImport_Click()
'cTables is named as a Public Constant (z:\CSITables.mdb)
Dim cnn As ADODB.Connection
Dim sQRY As String
DoCmd.SetWarnings False
strTempTable = "tblCSATAddressTEMP"
strFilePath = Me.txtFilePath
'Check that BusinessType has been selected- quit if not
If IsNull(Me.cboBusinessType) Or Me.cboBusinessType = "" Then
MsgBox "You must select a Business Type from the dropdown list",
vbExclamation, cApplicationName
Exit Sub
End If
If VBA.Len(strFilePath) <> 0 Then
'Import the spreadsheet
DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath, True
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
"Data Source=" & cTables
'insert new addresses into tblAddress
sQRY = _
"INSERT INTO tblCSATAddressA IN '" & cTables & "' " & vbCrLf &
_
"( JobNumber, Address, ProjectID, Project, JobDate, TeamCode,
Engineer, Contract, BusinessType )" & vbCrLf & _
"SELECT" & vbCrLf & _
"tblCSATAddressTEMP.[No]," & vbCrLf & _
"tblCSATAddressTEMP.Description," & vbCrLf & _
"tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
"tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
"tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
"tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
"tblFamilyTree.Engineer," & vbCrLf & _
"tblFamilyTree.Contract," & vbCrLf & _
"'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf & _
"FROM tblCSATAddressTEMP " & vbCrLf & _
"LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] =
tblFamilyTree.TeamCode"
CurrentProject.Connection.Execute sQRY
End If
'DoCmd.DeleteObject acTable, strTempTable
MsgBox Me.cboBusinessType & " Data has been imported",
vbExclamation, cApplicationName
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
 
ok, now create a new query, switch to SQL view and paste this SQL there,
access will tell you what is going wrong

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Jez said:
This is from the debug.print

debug.Print sQRY
INSERT INTO tblCSATAddressA IN 'Z:\CSITables.mdb'
( JobNumber, Address, ProjectID, Project, JobDate, TeamCode, Engineer,
Contract, BusinessType )
SELECT
tblCSATAddressTEMP.[No],
tblCSATAddressTEMP.Description,
tblCSATAddressTEMP.[Bill-to Customer No],
tblCSATAddressTEMP.[Scheme Code],
tblCSATAddressTEMP.[Planned Start Date],
tblCSATAddressTEMP.[Team Code],
tblFamilyTree.Engineer,
tblFamilyTree.Contract,
'CHI' AS BusinessType
FROM tblCSATAddressTEMP
LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] =
tblFamilyTree.TeamCode

Alex Dybenko said:
Hi,
get result of your query statement into debug window:
?sQRY

and then paste in into new query sql view - access will tell you what is
wrong there


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Jez said:
Hi, I am pulling my hair out with this Syntax issue, I am trying to
load
in a
linked Spreadsheet and then append some fields from this into a table
that
is
sitting on another drive but I am connecting to it via ADO. For some
reason
when executing the code it doesnt seem to like my SQL statement and I
cant
find out where, or why

Can anyone point me in the right direction?

Private Sub cmdImport_Click()
'cTables is named as a Public Constant (z:\CSITables.mdb)
Dim cnn As ADODB.Connection
Dim sQRY As String
DoCmd.SetWarnings False
strTempTable = "tblCSATAddressTEMP"
strFilePath = Me.txtFilePath
'Check that BusinessType has been selected- quit if not
If IsNull(Me.cboBusinessType) Or Me.cboBusinessType = "" Then
MsgBox "You must select a Business Type from the dropdown list",
vbExclamation, cApplicationName
Exit Sub
End If
If VBA.Len(strFilePath) <> 0 Then
'Import the spreadsheet
DoCmd.TransferSpreadsheet acLink, , strTempTable, strFilePath,
True
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
"Data Source=" & cTables
'insert new addresses into tblAddress
sQRY = _
"INSERT INTO tblCSATAddressA IN '" & cTables & "' " & vbCrLf
&
_
"( JobNumber, Address, ProjectID, Project, JobDate,
TeamCode,
Engineer, Contract, BusinessType )" & vbCrLf & _
"SELECT" & vbCrLf & _
"tblCSATAddressTEMP.[No]," & vbCrLf & _
"tblCSATAddressTEMP.Description," & vbCrLf & _
"tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
"tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
"tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
"tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
"tblFamilyTree.Engineer," & vbCrLf & _
"tblFamilyTree.Contract," & vbCrLf & _
"'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf
& _
"FROM tblCSATAddressTEMP " & vbCrLf & _
"LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] =
tblFamilyTree.TeamCode"
CurrentProject.Connection.Execute sQRY
End If
'DoCmd.DeleteObject acTable, strTempTable
MsgBox Me.cboBusinessType & " Data has been imported",
vbExclamation, cApplicationName
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top