Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Accessing Access via Excel

Posted on 2002-06-28
14
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use 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.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

609 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