Algorithm to Open or Activate Access and return an object handle

[Dr Rubick]

I'm trying to write a procedure in Excel to open Microsoft Access (if it is
not already open), then open a particular database (if it is not already
open, or open a new instance of access if the existing one has a different
file), and return an object handle to that database (leaving it open). I
already have a string variable with the full filename and path of the desired
database. It sounds simple enough, but it is proving very difficult to do.

I've tried using "GetObject", with error-trapping to use "CreateObject" if
"GetObject" fails. The problems are that if I use "CreateObject", then (1)
sometimes the "accObj.Visible = True" line crashes, and (2) Access will often
close when my macro ends, and I can't find a way to make it stay open.

I could probably keep trying different things until something works, but I
feel like I'm re-inventing the wheel here. Does anyone have sample code that
will do this?

 

[Dave]

just what are you trying to do once you open the database?

it might stay open if you define the variables to hold the object instances
as global outside of your macro. otherwise it will likely close when the
variable goes out of scope.

 

[Dr Rubick]

I want to go to a record that corresponds to what the user selected in the
calling subroutine. Then I want to leave Access open, so that the user can
see the data in that record. That's why I need my macro to be robust to
cases when the database is already open, in case the user then goes back to
Excel to look up another record without closing access first.

I can handle opening the right form and then navigating to the desired
record, once I get an object handle to the database.

 

[Dave]

kind of round about going through excel to search a database, there are nice
ways to do that directly in access.

but anyway, try putting the code doing the opening and object creation in a
separate module and make the variables that hold the object public. it may
also work in the 'thisworkbook' excel module but i would think a separate
module may be better. i am thinking that your objects are going out of
scope when the macro exits or when the excel worksheet loses focus.