Create Access DB in VBA from Visio (or Excel) in 2000 format but compatible with windows 10

[DJHarris71]

Hello,
I have many years of experience coding in VBA in Excel and Visio. However, recently I have been asked to create an Access database file from within Visio (or just assume it is Excel - the basis is very similar). Even better it needs to be in Access 2000 format.

I was surprised it was not as straight forward as creating an Excel file with vba - although maybe I am doing something wrong. After doing many web searches - the way I am making my database file is with these commands:

Set cat = CreateObject("ADOX.Catalog")
cat.Create "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

I did some research and found if I add the 'Jet OLEDB:Engine Type' to 5 I can get this in 2002/2003 format

cat.Create "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Engine Type=5;Data Source=" & dbPath

However i can not get this in 2000 format. It seems version 'Engine Type=4' fails (which is probably because these drivers are not loaded in 2010 PC's?)

First: Don't ask why it has to be 2000 format. It just does.
Second: Is there a better way to create an Access database from Visio/Excel VBA (why ADOX and not an Access.Database object or is that what ADOX means)?
Third: Is there a way to save it in 2000 format in a Win 2010 machine?
Lastly: Is there a way to hook into Access 'Save As' feature that does allow you to save a model in 2000 .mdb format?

Thanks
Doug

 

[clmoi90]

Hello DJHarris71 (or others),

I also need to create an Access database in Access 2000 format (using excel VBA).
Did you find the solution (and do you still remember it) after more than 2.5 years?

Thank you in advance!

Best regards,
Ludo

 

[Trevor G]

With Access in the Save As you can still safe in 2000 Format. I would ask do you have data already and if yes which application is it in (such as Excel).