Solved

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

Posted on 2011-03-23
11
444 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
Comment Utility
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
Comment Utility
The line that is giving me the error is

db.TableDefs.Append XLTable.

Any ideas?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 

Author Comment

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

Author Comment

by:JCutcliffe
Comment Utility
I mean. I am not sure why as it used to work
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
I am using Excel 2007. Heres the Excel file
IEB-ORACLE-Mar-11-Dailies-Templa.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
>>>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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok now tell me which line was giving you the error ;)

Sid
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now