Re: Syntax Error - INSERT INTO Statement

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <Jez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C1774541-A85B-40B5-8AB8-B0A266D695A0@xxxxxxxxxxxxxxxx
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" wrote:

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" <Jez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F16FFCAF-CDEA-4420-BC4F-8480B88868F9@xxxxxxxxxxxxxxxx
> Hi, I am pulling my hair out with this Syntax issue, I am trying to > load
> in a
> linked Spread*** 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 spread***
> DoCmd.TransferSpread*** 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



.

Quantcast