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

Hi Jez,

If you need to access tables within the Front End application, then simply use the CurrentProject.Connection for your connection, to access tables in the backend, keep doing what your doing.

If you need to access both tables within the same query, I believe you will need to copy the required data to one of the projects/files (I am assuming the backend).

If any of this is unclear, then please ask.

Regards,
Rob

Rob, Thanks for the reply.

I am getting a little lost, basically I am trying to get the import TEMP table to append to the live db table in the back end DB (this is held on another drive), I changed the cnn in the error line to CurrentConnection, but this again says it cant find the table I want to append to. Is this because when I import the TEMP table its on my front end DB, how can I get it to import to the back end DB? then would I need to use cnn or currentconnection as both tables would be in the same DB but the code where the command button is in another DB?

Jez

Jez,

No problem. When you Use DoCmd, in most cases it will always work with the Current database, this, in your case is the Front End. So yes, when you import the spreadsheet, it will be placed into the Front End.

In order to Append the records in the Front End, you will need to link to that in some way.
If the Address/Location of the Backend Database is static then you could use:
INSERT INTO [TableName] IN 'C:\SomeDir\AccessDB.mdb'
SELECT [Field 1], [Field2]

And this should work.

So something like: Be sure to update the address of the DB highlighted in bold!!

Hope this helps/points you in the right direction.

Regards,
Rob

Rob, superb!! Thats has help so much.

on the code you wrote, dim sql as String. I used sQRY as string, is this any different, as I continued using the sQRY and I have an error saying "Syntax error in INSERT INTO statement", I also have used the constant name I have got for where the database is held.
This is the path of my DB (BackEnd) Z:\CSITables.mdb, but I have used a public constant and done this
Public Const cTables = _
    "Z:\CSITables.mdb"
Therfore it starts like this
 sQRY = _
            "INSERT INTO tblCSATAddressA IN 'cTables' " & vbCrLf & _

Have I gone wrong somewhere?

Jez

I forgot to ask,

What does this mean " & vbCrLf & _, whats the difference between
 " & _?

Jez

Jez,

Glad your happy!

First off, if you wish to use your constant for the database file try:

And you second question about vbCrlf, all that does is insert a Line Break to break up the SQL
this has no effect on what the SQL produces, but when debugging it makes it easier on the eyes.
You dont have to use it, its just a habit of mine =)

Hope this helps,
Rob

Excellent.

Thanks Rob. But still I think am missing something as it keeps telling me there is a syntax error on this code.

It highlights the line CurrentProject.Connection.Execute sQRY

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 " & vbCrlLf & _
            "LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
            CurrentProject.Connection.Execute sQRY

Have I gone wrong somewhere?

Jez

Jez,

There is an error on the line: It should read: Hopefully that should do it!

Regards,
Rob

Rob, Still I get syntax error on the same line.

I have re-written it out 4 times now, cant see where the problem lies. Am pulling my hair out with this.

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

Jez