Accessing Access via Excel

i have an access app that opens a spreadsheet in excel. excel then runs the following code.

i have the following code in Excel:

Dim db As Database
Dim rst As Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("treportheaderinfo", dbOpenSnapshot)

the problem occurs on the set db = currentdb. another instance of access opens up and is blank, meaning that it just opens up access with no database in it.

this problem started to occur when i went from excel 97 to excel 2K. the code worked fine in excel 97. im running access 97. any ideas on how to get it to accept the instance of access that is already open rather than opening another instance?
LVL 2
BangerterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ildcConnect With a Mentor Commented:
Hi Bangerter,

Maybe You could use the following, since you say that the db is surely open:

Dim objAccApp As Access.Application

Dim dbThisDb As DAO.database

Set objAccApp = GetObject(, "Access.Application")
Set dbThisDb = objAccApp.DBEngine.Workspaces(0).Databases(0)


Be sure you set the right references in your excel project. At least the DAO 3.6 reference and the Access 9.0 Object library.  It is also best thet you use whereever you can the DAO prefix if you use DAO objects.

Regards
0
 
Richie_SimonettiIT OperationsCommented:
I think is a problem with data type: Access in Office97 use DAO and Access in Office2000 uses ADO as default so CurrentDB are pointing to different type of objects.
0
 
Richie_SimonettiIT OperationsCommented:
Besides, why don't you run your code only in Access managing Excel with Automation?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
ildcCommented:
I agree with Richie, but it should be better in your case that you reference the correct database instead of using the currentdb().

Set db = DBEngine.OpenDatabase("DATABASE_PATH_AND_NAME")

don't forget to do a db.close or a set db = nothing afterwards.
regards
0
 
Arthur_WoodCommented:
you cannot simply Open the "currentDatabae" as you have never indicated WHICH database you wnat to open.

What you need to do is OpenDatabase (<path to the mdb file>)

ILDC showed you how.  so, for instance in the MDB (and this should be an Access 97MDB, unless you are using the NEWEST version of DAO)wre located in C:\Projects\NewProject\MyDatabase.mdb  then the line would look like this:

Set db = DBEngine.OpenDatabase("C:\Projects\NewProject\MyDatabase.mdb")


and the code you rihinally posted should be changed to this:

Dim db As Database
Dim rst As Recordset
Dim i As Integer
Set db = DBEngine.OpenDatabase("C:\Projects\NewProject\MyDatabase.mdb")
Set rst = db.OpenRecordset("treportheaderinfo", dbOpenSnapshot)

this code WILL ALWAYS open a NEW instance of Access, rhather than being able to "attach" to the currently running instance.  It is MUCHG safer to do that, as you are then ABSOLUTELY positive that Excel is opening the CORRECT MDB file, rather that relying on the user to a) remember to oepnm Access before running the Excel Spreadsheet, and b) even if they rememebr to open Access first, to also remember WHICH MDB they are supposed to open.  By having everything encapsulated in the Excel macro, there is NO possibility of error, Excel will ALWAYS open the corrent MDB.  I have several Applications that function in EXACTLY this manner, and the user is totally un-aware of the specifics to the underlying database, as well they should be.

Arthur Wood
0
 
Richie_SimonettiIT OperationsCommented:
..."this code WILL ALWAYS open a NEW instance of Access..."
I don't understand. You don't open an Access App with that code, only a db that is in Access format.
0
 
Arthur_WoodCommented:
actually, Richie, yes, a New instance of the Access engine is in fact opened when you open a DAO "connection", just look at what is running as a Application (Ctrl-Alt-Del) and you will find that msaccess.exe is in fact executing.

And you cannot "connect" to an already running instance of the engine.
0
 
BangerterAuthor Commented:
the user starts in access and will not open the excel file unless it is through access. i need the program to open a certain excel spreadsheet; and update the query behind the spreadsheet (this all works fine). i then have excel refer back to access for some page header and footer information. up until the company upgraded to office 2K this all worked fine.

i dont want it to open up a new instance of access and i know that you can connect to a currently open db as i have had this working at one time.

i tried the code posted above and i still have the same problem of another access window opening
0
 
BangerterAuthor Commented:
the user starts in access and will not open the excel file unless it is through access. i need the program to open a certain excel spreadsheet; and update the query behind the spreadsheet (this all works fine). i then have excel refer back to access for some page header and footer information. up until the company upgraded to office 2K this all worked fine.

i dont want it to open up a new instance of access and i know that you can connect to a currently open db as i have had this working at one time.

i tried the code posted above and i still have the same problem of another access window opening
0
 
davlun20080Commented:
I think it will be a problem with the db engine, as stated earlier in the thread
0
 
Richie_SimonettiIT OperationsCommented:
Sorry, Arthur, i never see Access running in background just for open an Access mdb file. What about if Access isn't installed? what opens that code in such case?
0
 
BangerterAuthor Commented:
this still isnt working. what is the code to use ado? if office 2K is all ADO then i shouldnt have a problem using it.
0
 
BangerterAuthor Commented:
i finally got it working. this is the code i got it working with:

Dim db As Database
Dim rst As Recordset
Dim i As Integer

i = 1
Set db = DBEngine.OpenDatabase("c:\ClinopRpt\UserRpt_Dev.MDB")
Set rst = db.OpenRecordset("treportheaderinfo", dbOpenSnapshot)

i will give the points to ildc. his comments lead me to the right solution. although if anyone else wants some points for their help, let me know and i will give you some too.

thanks
brent
0
 
Richie_SimonettiIT OperationsCommented:
hummm... No, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.