Compact Access Database

[bri189a]

I'm working on a program that uses an Access db on the back end... I need to have database compacting available. I'm using the COM object JRO (Jet Replication Objects...replacement to the old DAO). This object has the CompactDatabase method, slightly differant from the old DAO method, but same thing... anyhow this is what I got:

 

string SRC_DB = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Database Password=" + @Password + ";Data Source=" + @sourcePath + ";";
		string DST_DB ="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Database Password=" + @Password + ";Data Source=" + @destPath + ";";
		try
		{
			JRO_JEC.CompactDatabase(SRC_DB, DST_DB + ".mdb");
		}
		catch (System.Exception ex)
		{
			Console.WriteLine("Failed! Error:{0}", ex.Message);
		}

 

Now if I take the password off the database and drop the UserID and Database Password portion it works, but the db needs a password, so thus the extra parameters. These parameters work perfectly when connecting to the db using ADO.NET, but with this method (OR THE OLD DAO CompactDatabase Method) I get "Could not find installable ISAM".... any ideas? Or do you know a better way to compact? FYI.... using the full connection string (all the parameters) results in the 'Incorrect Parameter Format' error... and again, the same thing (copy and pasted) in ADO.NET works fine)... also using the old fashioned Password=password results in the 'Workgroup file missing or locked exclusively by another user'... this is a simple fix, I just can't seem to figure out what the problem is... what works in an ADO.NET connection string should work here too!

Edited January 12, 2004 by bri189a

[samsmithnz]

IS there a reason why you can't use ADO.NET then? If it works for you, why not continue to use it...

[bri189a]

Found the problem... like I said... something stupid (happens when you only have 3 hours of sleep)... the code works fine... I just can't append ".mdb" at the end of the connection string! Duh! It's suppose to be at the end of the file name.... what an idiot I am! :(

 

Sam - actually I haven't seen an ADO.NET method for compacting... if there is one let me know!~ Thanks!

[Robby]

There isn't in ado.net

[samsmithnz]

Can't you create a reference using the access object, open the right database and then call a compact function? I think that's how I did it on my old project, I can't look though as it was another job and I don't have the source anymore...

[Robby]

Sam, that method would require the end-user to install Access on their PC

[samsmithnz]

Not if a dll called it from the back end.

[Robby]

Are you sure, my understanding is that any time you reference an Office object you need the app itself. (or it may depend on the type of call you make to the object)

[samsmithnz]

I'm pretty sure. I know for an previous app we had we just distributed this Excel object that gave us access to all of excels methods so that we could export the data into a spreadsheet. The client machine still counted as having Office of course for licensing purposes.

 

But if the backend has an access database, then I assume it also has access, and therefore this shouldn't even be a problem.

[bri189a]

Sam, so you know, the Access object uses the DAO object to compact database in Office 97 and prior:

 

DAO.DatabaseEngine.CompactDatabase(sting, string, object, long (flag));

 

Office 2000 will still use the DAO method but Microsoft recommends uses JRO (Jet Remotable? Object), and this is the method for Office 2002, and 2003 as I understand... don't have those versions yet:

 

JRO.JetEngine.CompactDatabase(string, string);

 

So in reality, using Access.Application() I would still have to go back and use either DAO or JRO to do the compacting... because that's how Access does it internally.