Re-indexing/compacting an Access database from within a VB Program

[33216CLC]

Hello,

I would like to Re-index the database used along with my progam by clicking on a menu option within my program. How can I go about this?

Thanks,

Cooleen

 

[culshaja]

If I remeber rightly ADOX and companion to ADO provides this functionality.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[culshaja]

If you are using Access then to Compact the database you would use Jet and replication objects (JRO) which is part of MDAC.
You would use the JetEngine object and issue the following command:

objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbpath", "Provider=Microsoft.Jet.OLEDB.4.0; Dat Source=compacteddbpath"

The first arguement is the source db and the second arguement is the location that the compacted db should go to.

ADOX gives you administrative tasks. Sorry about the confusion.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[33216CLC]

Hi James,

Thanks for the tip. Tell me something; What if I wanted the compacted database in the same location as the source database? Would this create a problem or should this be handled in a cut and paste manner? I need the database in the same location because my program is instructed to look in one lacation for the database.

NB. I need this feature because the database grows every week and as a result my program slows down when using certain functions.

Cooleen

 

[culshaja]

My best guess is that like access you would have a problem. You could write a bit of code or a batch file that is executed that renames the old DB and then copies/renames the newly compacted DB.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[swilliams]

Are you using ADO to access the database in the first place??
If you are using the Jet engine, you can do

DBEngine.CompactDatabase

Simon

 

[33216CLC]

I tried the code below and got invalid password as error.

DBEngine.CompactDatabase sOldName, sNewName, , "; pwd=" & g_sPassword

The password represented by the constant, "g_sPassword", is the correct one for the database. I also followed the help in Vb, but I still get the error. Again, I tried compacting the same access database with the Visdata program and got the error again. Any ideas?