View Single Post
  #1 (permalink)  
Old July 10th, 2007, 05:02 PM
JezLisle JezLisle is offline
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default error message - Microsoft.Jet cant find table

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?

This is my code below...

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 "Select * From " & 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


Jez


Reply With Quote