opening an access database through VBA in excel

[hysteria]

Hey..
I was wondering if there is a way to open a dialogue box from excel and the user should be able to choose which database to open from that dialogue box.
I do not want to hard code the path into the code because different databases might be opened.
How do i do that using VBA?
Thanx a lot in advance for any help

 

[scking]

Well, it can be done but will take some thorough planning and a fair amount of coding and patience. There are quite of few ways of implementing this.

1) You could have a table containing the location of all tables you might want to open.
2) You could set a directory as the starting point then search for all *.mdb files in each subdirectory.
3) You could set a number of directories to use as the starting point for a search of subdirectories.

Now do pseudo code to help design your app:
Read each directory for the table
For each directory
Search the directory and all subdirectories
..building a string that delimites the values with ;
..and set the source for the control.

When the user selects a database
Read each item in the Listbox
If an item is selected
.. feed the DSN to a function that opens the database

There are lots of assumptions here but you would need to be aware of all of them.




----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[hysteria]

Thanx a lot.
It does sound a lil more complicated than i thot itd be but yes those are good ideas.
I'll see where i end up

thanx

 

[danvlas]

Go to:

http://www.mvps.org/access/api/api0001.htm

Copy allllll the code there
Paste it into a module
Save the module.

In your code:

Dim PathAndFile As String
PathAndFile = GetOpenFile()
If PathAndFile = "" Then Exit Sub
Shell "msaccess.exe " & """" & PathAndFile & """"
'or
'FollowHyperlink PathAndFile

should open the database you select. It works in Access, but it's API, so should work in excel too.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com