Excel VBA linking Excel table to Access database

Posted on 2012-08-16
Medium Priority
Last Modified: 2012-08-17
I am trying to link Excel tables to Access, run append queries to get the data from them and then unlink the tables.  The last two are simple, the linking of the tables though is frustrating.  My code is:
Sub OpenAcc()
    Set oapp = CreateObject("Access.Application")
    oapp.Visible = True
    oapp.OpenCurrentDatabase "O:\RCD\RCD db.accdb"

    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "13 mo Report", "O:\RCD\Report\Billable Expenses.XLS", True, "TestingRange"
End Sub

Open in new window

If I run the code as is, I get an error 424 (Object Required).  If I precede the DoCmd with oapp., I get eror 3170 (Could not find installable ISAM).  What am I doing wrong?
Question by:pmpatane
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38303696
The code is OK. It worked on my machine with Office 2007. I used this from Excel:
Sub OpenAcc()
    Dim oapp As Object
    Set oapp = CreateObject("Access.Application")
    oapp.Visible = True
    oapp.OpenCurrentDatabase "G:\Data\EE\Codes.accdb"
    oapp.DoCmd.TransferSpreadsheet 2, 5, "13 mo Report", "G:\Data\EE\crib-11-test.xls", True, "TestingRange"
End Sub

Open in new window

It's the 3170 error you need to resolve. Start by trying to link the Excel table from MS Access manually. See if that works. I think you need to change acSpreadsheetTypeExcel5 to  acSpreadsheetTypeExcel12 (or higher if you use Office 2010).

Alternatively you might be able to transfer the data using ADO without even opening the access database, but that would require completely different code.
LVL 18

Expert Comment

ID: 38304316
What version of Office? Excel? Access? are you running?
LVL 46

Assisted Solution

aikimark earned 1000 total points
ID: 38304500
This could be caused by your use of the constants acLink, acSpreadsheetTypeExcel5

Try replacing them with the numeric literal equivalent values.
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 38304690
Good one aikimark, points are yours. I assumed there was a reference to the Access library. But if there isn't and the 'Option Explicit' clause is omitted I can reproduce the error. So I'm pretty sure this is the cause.

Author Closing Comment

ID: 38305466
aikimark, I started doing what you had suggested as I was sure that was probably the issue, but had trouble finding the numeric value of aclink, but then nicobo, I read your assumption about the Access library reference and I thought I would check that out, and d'oh, it was not checked.  It worked after that.  One funny thing though, after I run my macro that links, pauses and unlinks, it works the first time, then if I run it again I get run-time error -2147417851 which when I Google I get nothing related to it.  If I then run the macro a third time, all is well again...not a show-stopper issue as everything else is humming, but weird!  Thanks again to both of you!

P.S. p912s, my apologies as you are quite correct I should have included the information you asked for.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 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