Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 10th, 2007, 05:02 PM
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


  #2 (permalink)  
Old July 11th, 2007, 02:32 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

  #3 (permalink)  
Old July 11th, 2007, 04:17 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #4 (permalink)  
Old July 11th, 2007, 04:35 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

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:
Code:
Dim sql As String
sql = _
"INSERT INTO tblCSATAddressA IN 'C:\DB.mdb'" & 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
Be sure to update the address of the DB highlighted in bold!!

Hope this helps/points you in the right direction.

Regards,
Rob

  #5 (permalink)  
Old July 11th, 2007, 05:22 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old July 11th, 2007, 05:30 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I forgot to ask,

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

Jez

  #7 (permalink)  
Old July 11th, 2007, 05:43 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

Glad your happy!

First off, if you wish to use your constant for the database file try:
Code:
sQRY = _
            "INSERT INTO tblCSATAddressA IN '" & cTables & "' " & vbCrLf & _


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



  #8 (permalink)  
Old July 11th, 2007, 06:12 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #9 (permalink)  
Old July 11th, 2007, 06:31 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

There is an error on the line:
Code:
 "FROM tblCSATAddressTEMP " & vbCrlLf & _
It should read:
Code:
 "FROM tblCSATAddressTEMP " & vbCrLf & _
Hopefully that should do it!

Regards,
Rob

  #10 (permalink)  
Old July 11th, 2007, 06:42 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Error: Microsoft Jet Engine asters ASP.NET 1.0 and 1.1 Professional 2 April 3rd, 2007 09:33 AM
Microsoft JET Database Engine error '80040e14' aprnip Classic ASP Databases 2 November 15th, 2004 02:06 AM
Microsoft JET Database Engine error '80040e07' aprnip Classic ASP Databases 1 October 22nd, 2004 07:19 AM
Trying to find the Microsoft JET OLEDB download marthaj Access ASP 3 September 23rd, 2003 11:53 PM
Microsoft JET Database Engine error '80040e10' nvillare Classic ASP Databases 1 August 20th, 2003 12:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.