Create MDE file from 2000 format MDB using VBA

[AlanJ10]

Ok, I've tried to summarise my dilemma in the title, but I've thought of a few ways I might achieve it, I'm just not sure how to go about any of them, or which might be the correct one.

I'm running Access 2003, which is set to create 2000 format databases by default. What I want to do, is create a new mdb file, copy some tables, etc, and convert the new mdb into an mde file.

I've managed everything, but the final conversion, and when I try to manually convert the file, I'm told it needs to be in Access 2002/03 format before I can create an mde.

So, here are the possible solutions I've come up with:

1. Before creating the new mdb, change the default file format to Access 2003. This sounds like the worst solution, because I can't say what versions it will be running on in the future, and I presume if I change the setting, I'd be changing the setting for that copy of Access?

2. Create the new mdb in the correct format.
I'm using this -
Set db = ws.CreateDatabase(strFilename, dbLangGeneral)
to create the new mdb. I see I can add dbVersionXX on the end of that, but I can't find the correct value of XX to save it as a 2003 file.

3. Save it as the default fileformat, then convert to 2003 if necessary, but I don't know how to do this in vba.

2 seems to be the best bet, if there's a way to do it like that. Otherwise, I'd welcome any other suggestions.

 

[SOS]

Just so you are aware -

In order to create an MDE (or ACCDE) file you have to use the version that you want the MDE/ACCDE to be in. So, if you want a 2000 MDE you will need to create the MDE from a 2000 installation. No getting around that.

 

[AlanJ10]

That's something I'd better check actually, but just to be clear, I can only create a 2003 MDE from my installation of Office 2003?
What software will someone need to run this 2003 MDE?

Sorry, I've never actually done anything with MDE files before, so trying to create one like this is sort of jumping in at the deep end.

 

[SOS]

Yep, you can only create a 2003 MDE from 2003. You can use the runtime to run it and the 2007 Runtime should be able to run it for those who don't have Access installed. You can find the 2007 Runtime here.

 

[AlanJ10]

Ok, that's fair enough. So, how can I create a 2003 MDE given my situation above.
Easy answer is presumably to set my default file format to 2003, but I can't guarantee that the people running this will have the same setting (in fact, I can't guarantee what version of access they'll be running at all, but I'll worry about that later).

 

[SOS]

You can convert your 2000 database to 2003 format by opening the database up, going to TOOLS > DATABASE UTILITIES > CONVERT DATABASE and selecting the 2002-2003 format. You will have to save it to a different name (the old format one won't be changed) and you will then have the new version which you can then make into an MDE.

As for others using it, it won't matter which version they use IF they use a version 2003 or higher OR if they have a previous version they can use the free Access Runtime as I have noted - they would need to install that runtime in order to use your database then if they didn't have 2003 or higher.

 

[AlanJ10]

You can convert your 2000 database to 2003 format by opening the database up, going to TOOLS > DATABASE UTILITIES > CONVERT DATABASE and selecting the 2002-2003 format. You will have to save it to a different name (the old format one won't be changed) and you will then have the new version which you can then make into an MDE.

As for others using it, it won't matter which version they use IF they use a version 2003 or higher OR if they have a previous version they can use the free Access Runtime as I have noted - they would need to install that runtime in order to use your database then if they didn't have 2003 or higher.

I know this, I want to do the whole thing as a process in vba:
1. Create new mdb
2. If necessary, convert to 2003-03 format
3. Convert to mde

I can do 1 and 3 ok, but I don't know how to do 2.

 

[AlanJ10]

Right, I seem to have resolved my above problem using convertAccessProject, but I'm still getting "Run-time error '7952' You made an illegal function call" when I try to create my MDE file (using appAccess.SysCmd 603, strFilename, strFilenameMDE).

Any ideas?

Edit:

Not entirely sure what I've changed to be honest, but it now runs without error, but doesn't create a file.
If I put the actual filepaths in, then it runs and creates the mde, but as soon as I try to replace either path with a variable containing exactly the same path, it fails?

 

[SOS]

Not entirely sure what I've changed to be honest, but it now runs without error, but doesn't create a file.
If I put the actual filepaths in, then it runs and creates the mde, but as soon as I try to replace either path with a variable containing exactly the same path, it fails?

So what does the code look like that assigns the values to the variables?

 

[AlanJ10]

Just a straightforward strFilename = "Path to database here", so it made no sense at all.

I seem to have got it all working now by putting the makeMDE code into a separate function and calling it as part of my code. Thanks for your help.

 

[AlanJ10]

Right, I'm back.
I'm now trying this in 2007, but have discovered that MDEs seem to have disappeared to be replaced with ACCDE files.
I've tried my code in 2007, but it fails silently when creating the MDE/ACCDE file. Is there a different method to creating an ACCDE in vba in 2007?

Edit - Forget it, my code wasn't compiling under 2007 and so the ACCDE wasn't being created (although I could create it manually?), so, problem solved.