Invalid SQL Statement error

[Jez]

I have this code below, that I am wanting to append to a table from a command
button. I keep getting an error message saying Invalid SQL Statement when I
run the code.
It happens on line rs.Open strTempTable, cnn
Where am I going wrong?

Private Sub cmdImport_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
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
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
"Data Source=" & cTables
'set rs to temporary table
rs.CursorLocation = adUseClient
rs.Open strTempTable, cnn
'insert new addresses into tblAddress
sQRY = _
"INSERT INTO tblCSATAddressA ( 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, '" & Me.cboBusinessType & "' AS BusinessType " & _
"FROM tblCSATAddressTEMP LEFT JOIN tblFamilyTree ON
tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
cnn.Execute sQRY
End If
DoCmd.DeleteObject acTable, strTempTable
MsgBox Me.cboBusinessType & " Data has been imported",
vbExclamation, cApplicationName
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub

 

[Alex Dybenko]

Hi,
try to use:

rs.Open "Select * From " & strTempTable, cnn

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

 

[Jez]

Hi, Thanks for that. No that error seems to have gone away, I now have
another

Because when I click on the button to load in the data, it imports the TEMP
table into my front end database, but the other tables are on another drive.
It gives me a error message, "Microsoft.Jet cant find tblCSATAddressTEMP.

Is this because on the line I had the error before it says ,cnn which is my
connection to the back end database.

In this case how can I get the TEMP table to import onto the back end
database or how can I get it to see the TEMP in my front end database?

Jez

Hi,
try to use:

rs.Open "Select * From " & strTempTable, cnn

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

I have this code below, that I am wanting to append to a table from a
command
button. I keep getting an error message saying Invalid SQL Statement when
I
run the code.
It happens on line rs.Open strTempTable, cnn
Where am I going wrong?

Private Sub cmdImport_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
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
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; " & _
"Data Source=" & cTables
'set rs to temporary table
rs.CursorLocation = adUseClient
rs.Open strTempTable, cnn
'insert new addresses into tblAddress
sQRY = _
"INSERT INTO tblCSATAddressA ( 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, '" & Me.cboBusinessType & "' AS BusinessType " & _
"FROM tblCSATAddressTEMP LEFT JOIN tblFamilyTree ON
tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
cnn.Execute sQRY
End If
DoCmd.DeleteObject acTable, strTempTable
MsgBox Me.cboBusinessType & " Data has been imported",
vbExclamation, cApplicationName
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub