Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Accessing Access via Excel

Posted on 2002-06-28
14
Medium Priority
?
265 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
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.

 
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 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

581 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