Opening Access without declaring a Global Object?

J

Joel.R.Hill

So I've encountered an error that many others have come across, but
there still doesn't seem to be a clear solution. I am trying to open
MS Access via an Excel macro and import a spreadsheet. This creates an
underlying EXCEL.EXE due to a global object being stored in the memory,
but I'm unsure how to alleviate this error. Any help/ideas would be
much appreciated. The below is the Excel macro that is opening MS
Access:

Sub OpenAccess()
Dim oApp As Object
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase "C:\Test.mdb"
Set oApp = Nothing
End Sub
 
U

Unowho13

Hi Nick,

This issue occurs if you create a mdb that has an import macro; once
the Access import macro runs after the Excel macro, it creates another
EXCEL.EXE instance. Use the following steps to replicate the error...
Assume you have the following Excel spreadsheet + the code listed
below:

ColA ColB
CarType Make
Ford Mustang
Chevy Impala

1) Create an Access database with a table + an import macro pointing
at the spreadsheet described above
2) Save + Close the mdb
3) Use the Excel code below to open the mdb
4) Run the Access import macro
5) Close the mdb
6) Close the Excel App
7) Go to the task manager; you will notice the EXCEL.EXE is still
present even though the physical Excel App has been closed.

hope this clarifies...
 
N

NickHK

Running you code below results in Access starting and the mdb opening, then
all closing/quitting.
OK, there's no import routine running, but as such that is Access code
problem, not Excel.

I assume there is some kind of auto run macro in the .mdb to do the import.
But as you creating an instance of Access, why not control the import from
the Excel macro. Then .Quit Access when finished.

NickHK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top