Solved

Excel Macro that Exports & Appends data to an Access Table -ERROR MESSAGE

Posted on 2011-03-23
11
454 Views
Last Modified: 2012-05-11
Hi Guys, I am getting an Error Message on an Excel Macro that is saying "RUN Time Error 3011
The Microsoft Jet Database Engine could not find the object "Excel Data2". Make sure the Object exists and that you spell its name and path correctly". This is an Excel Macro that exports data from Excel & appends it in an Access table. I am not sure why but it used to work, but now it doesn't. See code down below. Anyone have any ideas?
Sub APPEND_DATA_TO_ACCESS()
    Dim ExcelBook2 As String
    Dim db As Database
    Dim AccessMDB As String
    Dim XLTable As TableDef
    Dim SQLstring As String
    '---------------------------------------------------------------------------------
    '- 1. SET THE NEW EXCEL NAMED RANGE ("ExcelData")
    rg = ActiveSheet.Range("A1").CurrentRegion.Address
    ActiveWorkbook.Names.Add Name:="ExcelData2", _
                         RefersTo:="=" & ActiveSheet.Name & "!" & rg
    '---------------------------------------------------------------------------------
    '- LINK TO THE ACCESS DATABASE
    AccessMDB = "g:\Restricted\UKMSI-FA-FS-EUC01 Man Securities Global EUCs\Equities 2011\Dailies Databases\FEB 2011\Feb ManSecs Dailies- Oracle.mdb"
    Set db = DBEngine(0).OpenDatabase(AccessMDB)
    '---------------------------------------------------------------------------------
    '- 2. ACCESS : MAKE THE TEMPORARY TABLE ("Temp"). LINK IT TO THE EXCEL DATA
    ExcelBook2 = ThisWorkbook.FullName
    Set XLTable = db.CreateTableDef("Temp55")
    '*********************************************************************************
    XLTable.Connect = "Excel 8.0;DATABASE=" & ExcelBook2      ' CHECK VERSION        **
    '*********************************************************************************
    XLTable.SourceTableName = "ExcelData2"
    db.TableDefs.Append XLTable
    '---------------------------------------------------------------------------------
    '- 3. ACCESS : APPEND DATA FROM Temp TABLE TO ExcelData TABLE. DELETE Temp TABLE
    strsql = "INSERT INTO [GFM Daily PandL] SELECT * FROM Temp55"
    db.Execute strsql
    db.TableDefs.Delete "Temp55"
    db.Close
    Set XLTable = Nothing
    Set db = Nothing
    '---------------------------------------------------------------------------------
    MsgBox ("Done")
End Sub
0
Comment
Question by:JCutcliffe
  • 6
  • 5
11 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35201618
Which line is giving you the error?

I managed to test the above till

db.TableDefs.Append XLTable

without any errors.

Sid
0
 

Author Comment

by:JCutcliffe
ID: 35205382
The line that is giving me the error is

db.TableDefs.Append XLTable.

Any ideas?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205402
That is surprising become that line didn't error out when I tested it on my machine.

Was it able to create the table "Temp55" when you ran the code?

Sid
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:JCutcliffe
ID: 35206007
No, I can't see the table being produced. Not why as it used to.
0
 

Author Comment

by:JCutcliffe
ID: 35206015
I mean. I am not sure why as it used to work
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206096
Can you just keep one record in the table and delete the rest and upload the table here? Let me test it for you real time. Also which Excel version are you using?

Sid
0
 

Author Comment

by:JCutcliffe
ID: 35206193
I am using Excel 2007. Heres the Excel file
IEB-ORACLE-Mar-11-Dailies-Templa.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206224
>>>I am using Excel 200

Then try this

XLTable.Connect = "Excel 12.0;DATABASE=" & ExcelBook2

instead of

XLTable.Connect = "Excel 8.0;DATABASE=" & ExcelBook2

Sid
0
 

Author Comment

by:JCutcliffe
ID: 35206304
just did it. i get the error message "Run Time error 3170 - could not find installable ISAM". what does that mean?
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35206618
Ok try this :)

I think this should work.

Sub APPEND_COMMS_DATA_TO_ACCESS3()
    Dim ExcelBook As String
    Dim db As Database, fld1 As Field
    Dim AccessMDB2 As String
    Dim XLTable2 As TableDef
    Dim SQLstring As String
    
    rg = ActiveSheet.Range("A1").CurrentRegion.Address
    
    ActiveWorkbook.Names.Add Name:="ExcelData", _
    RefersTo:="='" & ActiveSheet.Name & "'!" & rg
    
    AccessMDB2 = "g:\Restricted\UKMSI-FA-FS-EUC01 Man Securities Global EUCs\Equities 2011\Dailies Databases\FEB 2011\Feb ManSecs Dailies- Oracle.mdb"
    
    Set db = DBEngine.Workspaces(0).OpenDatabase(AccessMDB2)
    ExcelBook = ThisWorkbook.FullName
    
    Set XLTable2 = db.CreateTableDef("Temp58")
    
    XLTable2.Connect = "Excel 8.0;DATABASE=" & ExcelBook
    XLTable2.SourceTableName = "ExcelData"
    db.TableDefs.Append XLTable2
    db.TableDefs.Refresh
    strsql = "INSERT INTO [comm] SELECT * FROM Temp58"
    db.Execute strsql
    db.TableDefs.Delete "Temp58"
    db.Close
    Set XLTable2 = Nothing
    Set db = Nothing
    MsgBox ("Done")
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35206731
Ok now tell me which line was giving you the error ;)

Sid
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
Ransomware continues to grow in reach and sophistication, putting data everywhere at risk. Learn how to avoid being caught in its sinister clutches with these 11 key tips.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question