CreateDatabase / Access 2007

[Douglas J. Steele]

Set dbsHomedata = wrkDefault.CreateDatabase("C:\Transfer\Homedata.mdb", _
dbLangGeneral, dbVersion40)

(yeah, it's a little confusing that you need to use dbVersion40 to be
compatible with DAO 3.6!)

 

[FPS, Romney]

Hi all,

Question: Can Access 2007 be forced to create an Access 2000 database when
using CreateDatabase?

I have a "utility" database that I distribute in version 2000 to users which
is used to extract data from their main database (also version 2000) and
which then creates and dumps this data into a new database ("Homedata.mdb").
This new database can then be zipped up, emailed, and uploaded into our
central database.

The basic procedure is:
Data is extracted from the user's main database; placed in tables within the
"utility" database; "Homedata.mdb" is created; the tables from the "utility"
database are copied into "Homedata.mdb" using DoCmd.CopyObject; the user
zips-up and emails "Homedata.mdb".

The problem is that with users running Access 2007, even though their
"utility" database is version 2000 and version 2000 is set as the default
database format under Access Options, the new database that is created is in
version 2007. This means that I have to wait for someone with 2007 on their
laptop to process this "Homedata.mdb" that the user emailed.

The "utility" database uses the following code to create the new database:

Dim wrkDefault As Workspace
Dim dbsHomedata As Database
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
' Make sure there isn't already a file with the name of
' the new database.
If Dir("c:\Transfer\Homedata.mdb") <> "" Then Kill
:\Transfer\Homedata.mdb"
' Create a new database
Set dbsHomedata = wrkDefault.CreateDatabase("C:\Transfer\Homedata.mdb",
dbLangGeneral)

Suggestions?
Thanks,
Mark

 

[Douglas J. Steele]

Yes, although the documentation says dbVersion30 uses database engine
version 3.0 file format (which was Access 95).

http://msdn.microsoft.com/en-us/library/bb243161.aspx

 

[FPS, Romney]

by specifying dbVersion30 in the options part of CreateDatabase?

 

[FPS, Romney]

Thank you, Douglas
Since I do design work using Access '97, would dbVersion30 work just as
well?
Mark

 

[DinosRose]

This is exactly the topic I was looking for. The Access Help in 2007 goes
from dbVersion40 to dbVersion120. Based on this thread, it sounds like
dbVersion40 is Access 2000. If I want to force CreateDatabase to make Access
2002-2003, what dbVersion do I need to use?

 

[David W. Fenton]

Set dbsHomedata =
wrkDefault.CreateDatabase("C:\Transfer\Homedata.mdb", _
dbLangGeneral, dbVersion40)

(yeah, it's a little confusing that you need to use dbVersion40 to
be compatible with DAO 3.6!)

Why would that be confusing? DAO 3.6 is the version of DAO that goes
with Jet 4.0. DAO != Jet -- they have independent version numbers.

 

[David W. Fenton]

The Access Help in 2007 goes
from dbVersion40 to dbVersion120. Based on this thread, it sounds
like dbVersion40 is Access 2000. If I want to force
CreateDatabase to make Access 2002-2003, what dbVersion do I need
to use?

The dbVersionXX constants are not *Access* versions, but *Jet*
versions. dbVersion40 is Jet 4.0. dbVersion30 is Jet 3.0. I'd expect
there to be a dbVersion35 for Jet 3.5 (the version that shipped with
Access 97).

The Jet version for Access 2000, 2002 and 2003 remained the same,
i.e,. Jet 4.0. Now, there are version-specific formats for each of
those versions of Access, but the differences between A2000, A2002
and A2003 databases are *not* in Jet, but in the Access project
(i.e., VBA). So, for CreateDatabase, you would use dbVersion40 for
all three.

 

[FPS, Romney]

Doug,

I opened the 97 version of the Utility database and added "vbVersion30" as
an option for CreateDatabase.
'-----------------
Set dbsHomedata = wrkDefault.CreateDatabase("C:\Transfer\Homedata.mdb",
dbLangGeneral, dbVersion30)
'-----------------
I converted the Utility database to Access version 2000 and successfully
used it under Access 2007 to create "Homedata.mdb", which could then be
opened under either Access 97 or Access 2002/2003. -- great!

So far, so good.

However, the 97 version of the Utility database no longer works for creating
this "Homedata.mdb" file, producing the dreaded: "This program has performed
an illegal operation and will shut down ...". Removing that option results
in the 97 version working fine just as it always has.

"Compile and Save All Modules" never grays out with this option present.
This is Access 97, SR2, Jet35sp3
References:
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.5 Object Library

Any ideas?

Thanks,
Mark

 

[Douglas J. Steele]

I'm not sure I follow what you're doing, but I don't see why you'd bother
specifying dbVersion30 in the Access 97 version.

 

[Mark]

Doug,
You're correct, the option is not necessary in the 97 version. However, I
develop in Access 97 and then convert this to 2000, which is then
distributed to 2002/2003 and 2007 users. It's just more convenient if the
code is compatable across both 97 and 2000. That way, I can add it in at the
time of development (Access97), convert it to 2000 and distribute it --
without then also having to add additional code to prevent 2007 users from
creating a Homedata.mdb file which can't be processed by any of our office
computers.

2002/2003 users also created a Homedata.mdb which couldn't be opened under
97, but that wasn't a problem -- we've got plenty of computers in the office
running Access 2002/2003 which can be used to process this Homedata.mdb file
emailed in by users -- just no Access 2007 computers.

Adding the option "vbVersion30" to CreateDatabase does work to prevent 2007
from creating a 2007 version of Homedata.mdb. However, adding this option in
the 97 development database crashes the system -- even though "vbVersion30"
is listed in the 97 Help files as a viable option under CreateDatabase.

Mark

 

[FPS, Romney]

I've got it. Thanks, Doug
.... adding the "dbVersion30" option does work in both the 97 version as well
as the (converted) 2000 version -- which is where it's needed later for 2007
users.

Initially, adding the dbVersion30 option and then clicking "Compile and Save
All Modules" was triggering "This program has performed an illegal operation
and will shut down ...". The problem was actually associated with another
module, but didn't generate the "illegal operation/shut down" problem until
after I added the dbVersion30 option in this current module.

Thank you for your help.
Mark