Solved

Accessing Access via Excel

Posted on 2002-06-28
14
251 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

16 Experts available now in Live!

Get 1:1 Help Now