sql command to compact access db is ?

[tmb]

I have an access database on a remote web server and a program that will let
me issue raw sql commands to it...

Is there an sql command to 'compact' the data base?

thanks for any help.

 

[Douglas J. Steele]

Define what you mean by "sql command". Compact isn't part of standard SQL.

 

[david epsom dot com dot au]

Not unless you turn off all security and let the
user run file commands through sql. If you let the
user have enough permission to run a script that
does a compact, you are also letting them do things
like delete files. 'Shell' is not an 'SQL' command,
but it is a command that can be run from SQL.

(david)

 

[tmb]

I'm using a 3rd party program to issue standard sql commands to the access
db. The db is on a web server at an isp's facility. I am issuing these
commands through an administration program over the web. Commands like...


SELECT description, weight FROM products
UPDATE products SET price = 5.95 WHERE idProduct = 1

etc.

I could ftp the db down and use Access to do the compacting... but I don't
have the latest Access version that it requires.

Maybe it is time to buy some new software.

thanks

 

[Douglas J. Steele]

If that's the limit of the interaction you can have, I think you're out of
luck.

In theory, you should be able to compact the database through script,
though, without even requiring Access. Something along the lines of the
following (modified from what's shown in
http://support.microsoft.com/?id=230501) may work (assuming MDAC's been
installed on the server):

Dim jro
Set jro =CreateObject("jro.JetEngine")
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\nwind2.mdb;" & _
"Jet OLEDBatabase Password=test", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\abbc2.mdb;" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDBatabase Password=test"