Compact Access database using ADO

[Ellis Dee]

I'm a DAO guy, but I'm trying to switch over to ADO. One of the stumbling blocks is that I need to be able to:

1) Compact a database; no database password, no encryption
2) Compact a password protected database
3) Encrypt a password protected database

In DAO, steps 2 & 3 are wrapped up into a single call.

Also, for the bonus round, in ADO how do you:

A) Set a database password
B) Change the database password
C) Unset the database password

Thanks much.

[si_the_geek]

The simple (and annoying) answer is that ADO doesn't actually provide the features to do that... the more complex (and helpful) answer is that there is a separate item in the ADO "group" of DLL's known as JRO, an explanation & example of using it for C&R can be seen here.

I'm not sure about encryption or passwords (depending on your usage, the above may be ok for passwords), but these are likely to be covered by JRO, or perhaps by ADOX ("Microsoft ADO Ext. ...").

[Ellis Dee]

Ugh. So I have to ask, should I even bother switching from DAO to ADO?

DAO code is much simpler and (apparently) more feature-rich. I find that I often compact & repair, use table-type Seeks, and other such things that appear to be much more bothersome in ADO.

Another bonus of DAO is that it's a single DLL, as opposed to the whole MDAC annoyance.

I get that ADO is the way of the the now and future, but then again I'm using VB6. If I were using .Net, then obviously ADO all the way.

But is it worth the hassle to use ADO instead of DAO when programming in VB6 with an Access back end?

[wolf99]

just my 2 cents;
I too am only learning ADO but I find it works better with more recent versions of access which is handy when people other then me will be working on or using a database.

As i understand it (as in not too much really ), ADO and recent access versions were made to suit each other better than DAO

[si_the_geek]

I can understand your reservations.. one big issue with DAO is that it is obsolete (aka "very very unsupported"), and will not work on 64bit versions of Windows.

wolf99 makes a good point too.. DAO was originally designed purely for Access, so works well with the early versions. As time goes on, MS have moved away and made it work much better with ADO - as well as adding JRO (features for Access etc) to ADO.


In terms of MDAC, Windows XP includes MDAC 2.8 by default, so you often don't need to bother installing it.. it is only one file that needs to be installed anyway (tho that is an installation program in itself). There are apparently simple ways of integrating this into your own setup (for DB apps I've only had local users, so have done it manually).


ADO has many new features, and I have yet to find something that DAO has that ADO hasn't (it's just a matter of where to look - usually ADOX or JRO).

It is debatable if converting an existing program is worthwhile, but I would definitely do all new development in ADO.

[Ellis Dee]

Thank for the feedback, wolf. I consider that a compelling reason to change, which is good because I really need to regardless of my whiny reasons for not wanting to.

I'm using this page as a basic reference point in switching over my more advanced DAO functionality to ADO. It's a great resource. But then I saw that it was using JRO instead of ADO to compact, so I created this thread.

Looks like Access 97 and earlier you have to use DAO. Access 2003 and beyond, you should use ADO. Versions in between (like I have)? Programmer's choice.

[Ellis Dee]

Sorry, wasn't dismissing your response geek; it wasn't there when I responded to wolf.

Great points, and thanks for the thoughtful reply. I think I'm best off using ADO going forward, and only redesigning existing apps as necessary.

One exception, though, is a personal project that I've been writing for years. I use it as a test case for learning various techniques, (like using the treeview control, for example,) and I'll probably retrofit it for ADO/JRO.

Luckily I keep all database access confined to single module, and that module does nothing but data access, so it won't be overly difficult to convert any of my existing apps from DAO to ADO.

[SparrowHawk7]

I'm going to bump this back to the top as I am trying to do the same thing and there's no need to create a new duplicate thread. I could not find the MSJet reference in my list of references (Microsoft Jet and Replication Objects X.X library), but copied the code over anyway. I'm using VB6 with svcpk 6.

Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MYMDBPATH & ";Jet OLEDB:Database Password=test", _ 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MYMDBPATH & ";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test"

I changed the paths to insert my own to the database I wish to compact. I also used Access 2003 to convert my dB to it's own version (2003 - it was 2000). Whenever I run this procedure I get the error:

"Cannot perform this operation; features in this version are not compatible in databases with older formats".

And then it very kindly deletes my database - probably because I'm overwriting the existing one ... that's a minor thing. (I always keep backups of my up-to-date databases ...)

Is this trying to tell me I can only use this routine with databases created with Access 2007? Could it be that I am not inserting the reference (though I would suspect I'd get a different error in that case).

Any ideas? Thanks,
Ken

[si_the_geek]

I'm going to bump this back to the top as I am trying to do the same thing and there's no need to create a new duplicate thread.

It is generally best to create a new thread, and just link to an existing one if apt.

When you reply to a thread, anyone who has subscribed to it (not just the people who have posted) will get notified of your response - and they may not be interested any more (especially after 2 years!), so you are basically spamming them.

People who are available to help you will see a new thread, and are more likely to reply (as there is less reading to do before they reach your question).

I could not find the MSJet reference in my list of references (Microsoft Jet and Replication Objects X.X library), but copied the code over anyway.

If you haven't got the reference set, it shouldn't work at all - you should get an error about the type not being defined.

You can add the reference by browsing - the file name is MSJRO.DLL, and should be in the System32 folder (perhaps in an ADO sub folder).

If it isn't on your computer (or you just want to be sure you are up to date), you can download the latest version of Jet from the link in my signature.

And then it very kindly deletes my database - probably because I'm overwriting the existing one ...

Yes.

Do it to a separate file, and then overwrite the original if it was successful.

I also used Access 2003 to convert my dB to it's own version (2003 - it was 2000). Whenever I run this procedure I get the error:

"Cannot perform this operation; features in this version are not compatible in databases with older formats".

Is this trying to tell me I can only use this routine with databases created with Access 2007?

No, it is telling you that you are trying to save to an older version (Jet 3), which does not support the features of the source file (which I presume is Jet 4). The link I provided back in post #2 provides these details:

NOTE: The Jet OLEDB:Engine Type=4 is only for Jet 3.x format MDB files. If this value is left out, the database is automatically upgraded to the 4.0 version (Jet OLEDB:Engine Type=5). See the following table for appropriate values for Jet OLEDB:Engine Type:
Jet OLEDB:Engine Type Jet x.x Format MDB Files
1 JET10
2 JET11
3 JET2X
4 JET3X
5 JET4X

Note that Access 2007 is a whole new beast, and you will probably need to use AccDB rather than Jet (and I've got no idea how you'd do that).

[SparrowHawk7]

It is generally best to create a new thread, and just link to an existing one if apt.

Will do ...

You can add the reference by browsing - the file name is MSJRO.DLL, and should be in the System32 folder (perhaps in an ADO sub folder).

Found it in the common files directory ... still not available in the VB references however.

It was this that solved the problem ...

NOTE: The Jet OLEDB:Engine Type=4 is only for Jet 3.x format MDB files. If this value is left out, the database is automatically upgraded to the 4.0 version (Jet OLEDB:Engine Type=5). See the following table for appropriate values for Jet OLEDB:Engine Type:
Jet OLEDB:Engine Type Jet x.x Format MDB Files

When I removed those references everything worked just fine.

Thanks. Sorry guys for posting this here ...
Ken