Access2003 Difference between "OpenAccessProject" and "OpenCurrentDatabase"

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 11473
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Access2003 Difference between "OpenAccessProject" and "OpenCurrentDatabase"

Post by ChrisGreaves »

In this thread I began exploring Word2003/VBA manipulation of the VBProject in Access2003; I did not delve deeply.
In this thread I tried to resolve a problem surrounding my Word2003/VBA code to save an opened Access2003 database with a new name; I had opened the database (file), modified the VBA code in the Access database modules, and wanted to save the now-modified database with a new name.

An interim solution to saveAs is to start by FileCopying the user's database to a uniquely-named MDB file and work on this cloned MDB file, leaving it to the user to rename/move the updated file.
However the same application is able to saveAs Word, Excel, and PowerPoint files, so I would like to maintain this interface, and in the long term make the application issue a saveAs for the Access MDB files so that from the user's point of view, the handling of a file, regardless of its type (DOC/XLS/PPT/MDB) works in a consistent manner.

This may be impossible.

But now I am wondering whether I should be using OpenAccessProject rather than OpenCurrentDatabase.

I am correct in my understanding:
(1) I can have only one database (MDP) open at a time from within (say) Word2003/VBA
(2) A single database (MDB) can have none, one or more AccessProjects (ADP) associated with it (for example an "updateMe" project, a "verifyMe", project, a "reportMe" project ...)

I suspect that i am getting way out of my depth here; the user of the application will probably be content at being able to "clean up" VBA code in a simple database file (MDB) that contains some Access2003/VBA procedures - for example "LIST a summary of all procedures in my MDB database file".

Thanks
Chris
Last edited by ChrisGreaves on 16 Oct 2020, 10:20, edited 1 time in total.
You can walk out of your house, but you always return Home

User avatar
HansV
Administrator
Posts: 69376
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access2003 Difference between "OpenAccessProject" and "OpenCurrentDatabase"

Post by HansV »

The .adp file format has been deprecated; it was a failed attempt by Microsoft to improve connectivity between SQL Server and Access. See The Microsoft Access ADP Story. So I wouldn't recommend using .adp.

Microsoft Access is different from Word and Excel because a database has a very different internal structure than a Word document and Excel workbook.

For example, Access has a Compact and Repair Database command. From the user's viewpoint, it looks like the current database is compacted. But behind the scenes, the following happens:

1) Access creates a compacted copy of the current database, with a generic name such as Database1.mdb.
2) Access closes the current database.
3) Access deletes the database that it just closed.
4) Access renames the new database to the name of the old one.
5) Access opens the new database.

If you want to mimic this in VBA, you run into a problem: the moment you close the current database, the code in it stops running. So you're stuck.
RunCommand acCmdCompactDatabase won't work on the current database. (There is a workaround for this, but that is not relevant to this discussion).

Now let's look at the Save As command for the database. Behind the scenes, this does the following:

1) Access closes the current database.
2) Access creates a copy of the database file in the specified location.
3) Access Opens the newly created database.

Access VBA does NOT have an equivalent instruction. FileCopy is the way to copy a database.
Regards,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 11473
Joined: 24 Jan 2010, 23:23
Location: paused.undefined.exposed

Re: Access2003 Difference between "OpenAccessProject" and "OpenCurrentDatabase"

Post by ChrisGreaves »

HansV wrote:
03 Oct 2020, 10:13
Access VBA does NOT have an equivalent instruction. FileCopy is the way to copy a database.
(Thanks Hans; in replying to this i have a sense of deja vu)

I understand this now. Unlike word processors and workbook editors, a database package works directly on data on a disk drive; it does not "load a copy into RAM memory" and then require the user to "save the copy to disk".

Cheers
Chris
You can walk out of your house, but you always return Home