WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WIL Extenders
ODBC
plus
Sample Code

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Compact an access database 

Keywords: 	 COMPACT_DB Compact Access database

Question:

I found somewhere a snippet in delphi + odbc connection to pack an Access database. How do I do it with winbatch?

I could not find 'COMPACT_DB' documented anywhere.

Here is the Delphi the code 

with OEAdministrator1 do
begin
DataSourceType:= dsUser;
Driver:= 'Microsoft Access Driver (*.mdb)';
Attributes.Clear;
Attributes.Add('COMPACT_DB=' + filename+ ' ' + filename + ' General');
if not Modify then
ShowMessage('Not modified');
end;

NOTE: The database may not be compacted, for example, if it is in use by someone else.

Answer:

c_msgtitle = "ODBC Utility"
c_datasource = "sample"

;LINK ODBC FUNCTIONS
AddExtender("wwodb34I.dll")
databasename =`C:\OLDC\123.MDB`
;ALLOCATE SQL ENVIRONMENT HANDLE (MAX 1)
henv = qAllocEnv()
If henv == @qError
Message(c_msgtitle,"Could not allocate SQL environment handle.")
Exit
EndIf

;ALLOCATE SQL CONNECTION HANDLE (MAX 10)
hdbc = qAllocConnect(henv)
If hdbc == @qError
Message(c_msgtitle,"Could not allocate SQL connection handle.")
Exit
EndIf


request=2;ODBC_CONFIG_DSN
driver="Microsoft Access Driver (*.mdb)"; 
attrs="COMPACT_DB=%databasename% %databasename% General"; 
displayflag=@false
ret=qConfigData(request, driver, attrs, displayflag)
message("qConfigData - Created DSN",ret)
qFreeConnect(hdbc)
qFreeEnv(henv)

An alternative is to use ADO or OLE DB. The COMPACT_DB syntax of the SQLConfigDataSource() API attempts to compact the data source on itself, and is addtionally complicated by the 'type' (OFFICE 2000, 97, or 2.0), You can see this if you manually set up an ACCESS DSN, and click the 'Create' Button - in the next dialog you will see an array of radio buttons with Type 4 selected ( Office 2000 - assuming you have the latest drivers installed).

Anyway, the ADO syntax compacts into a separate file. 

src = "C:\TEMP\ORIGINAL.MDB"
tget = "C:\TEMP\TEMP.MDB"

If FileExist(tget) == 1
FileDelete(tget)
Endif

JE = ObjectOpen("JRO.JetEngine")
JE.CompactDatabase("Data Source=%src%","Data Source=%tget%")

;FileMove(tget,src,@FALSE) <- optional
ObjectClose(JE)


Article ID:   W14537

Filename:   Compact an Access Database.txt
File Created: 2001:03:01:15:41:34
Last Updated: 2001:03:01:15:41:34