Solved

Accessing Access via Excel

Posted on 2002-06-28
14
253 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:Bangerter
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7117908
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7117910
Besides, why don't you run your code only in Access managing Excel with Automation?
0
 
LVL 7

Expert Comment

by:ildc
ID: 7117971
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7118791
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7118903
..."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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7119133
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
 
LVL 2

Author Comment

by:Bangerter
ID: 7119354
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
 
LVL 2

Author Comment

by:Bangerter
ID: 7119359
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
 
LVL 3

Expert Comment

by:davlun20080
ID: 7119644
I think it will be a problem with the db engine, as stated earlier in the thread
0
 
LVL 7

Accepted Solution

by:
ildc earned 100 total points
ID: 7119826
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7120802
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
 
LVL 2

Author Comment

by:Bangerter
ID: 7122556
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
 
LVL 2

Author Comment

by:Bangerter
ID: 7122673
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7122741
hummm... No, thanks.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

813 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

17 Experts available now in Live!

Get 1:1 Help Now