How can i close the current database and open another one in the same window? i have tried using opencurrentdatabase and closecurrentdatabase but i just can't seem to get it. Any help would be great.
Who is Participating?
jtbConnect With a Mentor Commented:
Have you tried this?

Dim acc As Object
Set acc = CreateObject("Access.Application.7")
acc.OpenCurrentDatabase "C:\MYDB.MDB"

Now your database is open, have a look.  If you want it to stay open, you should open a form or something.

acc.Docmd.openform "Formname"

Hope this helps,
ramromconsultant Commented:
Help says:
"You can use the OpenCurrentDatabase method to open an existing database as the current database. You can use this method to open a database from another application that is controlling Microsoft Access through Automation, formerly called OLE Automation. For example, you can use the OpenCurrentDatabase method from Microsoft Excel to open the Northwind sample database in the Microsoft Access window."
The key thing here that this method works when you are controlling Access from another application. It sounds like you want the access session itself to close one database and open another.

One way to do what you want is:

Sub Switch_To(new_mdb)
  SendKeys "%{f}c%{f}o" & new_mdb & ".mdb{enter}"
End Sub

Call this sub with the name of the .meb file you want to open, e.g. Switch_To "sales"

JambyteAuthor Commented:
I would rather not use sendkeys, its kind of slopy, do you know of another way?
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ramromconsultant Commented:
Sloppy? what does that mean? IF it works, why not use it?

I know of no other way within access. Consider that access is only functional when an mdb file is open in it. There is very little that can be done with no mdb open. The only way I know is to use sendkeys as illustrated or some other 3rd party macro player such as tempo.
JambyteAuthor Commented:
is there a way to open a new database in a new window and then close the current database and window? programicaly? (w/o sendkeys)
ramromconsultant Commented:
Sub OpenDB(path) ' your pathname may vary
  Shell pathname:="c:\office97\office\msaccess.exe" & " " & _
  Chr(34) & path & Chr(34), windowstyle:=1
End Sub

Sub test()
  OpenDB "c:\foo\bar.mdb"
End Sub

ramrom: I don't think Jambyte ment to offend you in any way. Sendkeys are sloppy programming because it makes your code depend on the user not touching the computer till the keys has done their job. My experience is that a lot of users get impatient and start cliking and typing "to see what happened" if the program does not appear to do anything. This could make the keys getting sent to the wrong destination (speaking from experience).

There is almost always a way around using the sendkeys approach; docmd domenuitem ... and in Access'97 docmd.runcommand ... But sometimes there is no way around and then we have to cross our fingers and hope that the user does not manage to interfer with the process.

Have a nice day !
JambyteAuthor Commented:
any other way?
Somtimes ago i had the same problem. I worked a lot on it.And the only way I found was to use sendkeys.
Fixed the -1 problem on this question.
Escrow points corrected.

Community Support Moderator
Experts Exchange
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.