Switch Databases

[FoFa]

Searched but did not find. Came close when Pile-big-as-mile had some code to open a second DB, but does not work for me. Here is what I need, hopefully someone can clue me in. Within the same instance of MSaccess I need to open a second DB and close the first (or make it unavailable while the second instance is running), run the startup (or send it a startup form) from a button in the first DB, then when done, close that second DB and reopen the first one. But I can't open a second instance of MSaccess to do this (security won't let me).
Any idea on how to do this?
Thanks

 

[spacepro]

Fofa,

I'll have a go at this one.

Don't know if it will work, but if you use the commondialog control and set the path to the db using something like:

Me.CommonDialogName.InitDir = "C:\DB path"
Me.CommonDialog3.ShowOpen

Trigger this from a cmd button and do the same in the second db.

I think this will open the second db in the same access window, but it closes the first one down.

Do a search for common dialog to aid in the code above.

Hopefully this might help.

Regards

Andy

 

[spacepro]

Fofa,

It doesn;t work. I just tried it. It opens another window of access.

The 2nd db opens in the same window of access if you select from the menu

File > Open

then select the db.

Don't know how you work round this.

Andy

 

[Mile-O]

What's the overall purpose of doing this?

 

[FoFa]

Allowing users to acces the tables directly in one DB, but only access the data via the application in the other (main). The Main DB has security that only it can run in this instance on this server, so if the access instance closes, so does the session. But it does appear to allow me to open a second access instance as long as the original is still running. So basically the first app opens the second DB, runs what ever macro (depending on the button pushed in the main app) and dumps them out to a form. They are allowed to edit the data in the tables directly. Once done they have to close that instance of access to return to the main instance. It seems to work pretty good actually, and even thou I think it is a bad approach, it mets the expectations of the users and management who requested it work this way. What they make you do when it is due yesterday.

 

[spacepro]

Have you tried linking the tables in the main db, so that the tables are viewed/edited in the one db.

Andy

 

[Mile-O]

Have you tried linking the tables in the main db, so that the tables are viewed/edited in the one db.

Exactly what I was thinking.

 

[billyr]

I don't know if this will give you an idea or not. I like to use this function to work with split applications:
Function locatetables() As String
locatetables = DLookup("[tablepath]", "tbltablelocs", "[recno]=1")
End Function
(This function will "find" the linked _be)
In this case, tbltablelocs is a 1 record table kept in the .MDE (not linked). (It could easily be a 2 record table). I use the function like this:
Dim dbs as database
Dim rs as recordset
Set dbs = OpenDatabase(locatetables())
....
I did an application where 1 mde needed to be able to access, 1 at a time, multiple table sets (different plants data).
To control which _be is linked, I copied and tuned the relink code in the Northwind sample to clear current links and link a different _be. There is a button on my main switchboard to allow my user to select a different set of tables (and of course, link the selected _be). This code stores the now "current" table location in the table, tbltablelocs. It sounds like you know where everything is and the sequence they should be available to the user? I had to open the file search dialog; but, you should not have to go to all that trouble? If any of this is of interest, I can find the link module I used.

 

[ChrisO]

G’day all.

I don’t know if this attachment is the sort of thing that is required or not.

It began life with 6 customers and is currently running > 200 customers each in its own BE.

As with everything there is both good and bad and it depends on the situation. The main restriction is that all the BE’s need to have the same structure. The other drawback is when working across all BE’s the code is slightly more complex. There’s an automatic switching procedure included.

Even if it doesn’t fill the direct need it may give some ideas.

Regards,
Chris.

 

[FoFa]

It was more than just access to the tables, I over simplified it. It was NOT giving them access to the tables in the Main aplication (the database window if you will). I have been through it all on this one. Biggest issue was a time crunch. What they really needed was to implement security. We don't need no stink'n security, and in the next breath, that isn't secure enough. Lets move it to Sqlserver, no, don't want to do that, yet. They need to access the tables directly to edit the data. Oh, they have access to the tables, don't want that, they might edit the data directly. We have gone around and around before I started this thread, believe me.
Thanks