Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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


Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
  #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



Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
  #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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:01 PM.


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