corhaveman
asked on
compact and repair
hi Experts,
Here's my problem: I have an ms-access frontend db linked to a backend of about 700 mB. Only opening the backend and closing (compact on close = true) works fine. If I close the frontend (and so the backend), the backend is not compacted.
I tried to compact the backend db in vba code in the frontend (ensuring there are no links anymore to the backend; db.laccdb disappears) but this code almost always gets stuck. The only way to end the frontend is in the taskmanager.
The frontend schedules a proces. After this daily proces has run I want the backend to be compacted. This proces makes the backend grow about 65 mB. After compacting about 3mB.
I also tried to find some code to copy the tabels of the backend to a new backend db (so to compact the backend) but i didn't find it. Obviously it's only possible to im-/export to/from the frontend.
Any suggestions??
Here's my problem: I have an ms-access frontend db linked to a backend of about 700 mB. Only opening the backend and closing (compact on close = true) works fine. If I close the frontend (and so the backend), the backend is not compacted.
I tried to compact the backend db in vba code in the frontend (ensuring there are no links anymore to the backend; db.laccdb disappears) but this code almost always gets stuck. The only way to end the frontend is in the taskmanager.
The frontend schedules a proces. After this daily proces has run I want the backend to be compacted. This proces makes the backend grow about 65 mB. After compacting about 3mB.
I also tried to find some code to copy the tabels of the backend to a new backend db (so to compact the backend) but i didn't find it. Obviously it's only possible to im-/export to/from the frontend.
Any suggestions??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
LSM consulting: I'm wondering if set dbs=opendatabase(<BackendD B>) works becasuse export is only possible from the current db in which case the links are exported ;-(
I'll give it a try.
I'll give it a try.
Set dbs=CurrentDB: I suppose this should be set dbs=opendatabase(<BackendDYou can use any path for the destination database. If your goal is to actually move tables from the Backend to a new database, then yes, you should use OpenDatabase to open the backend, and set the destination to a new database somewhere. Note you can use CreateDatabase to build a new database to hold those exported tables:B>) otherwise the links are exported.
http://msdn.microsoft.com/en-us/library/office/ff835033.aspx
So you would do something like this:
Dim acc As Access.Application
Set acc = New Access.Application
acc.Visible = False
acc.OpenCurrentDatabase Me.txDBPath
In this case, Me.txDBPath would point to your Backend. You'd then use the acc variable when calling your TransferDatabase:
acc.DoCmd.TransferDatabase
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for corhaveman's comment #a38813267
for the following reason:
SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact works fine. Ik had to make a few adjustments to make it; the compact proces takes takes sometime so I had to pause the main proces until the compacting proces had finished.
thanks a lot
Accepted answer: 0 points for corhaveman's comment #a38813267
for the following reason:
SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact works fine. Ik had to make a few adjustments to make it; the compact proces takes takes sometime so I had to pause the main proces until the compacting proces had finished.
thanks a lot
It would seem my suggestion to use " SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact " provided you with assistance, so I'm curious as to why you did not accept my comment (http:#a38809644) as an Assisted solution.
I've asked the Mods to review this.
I've asked the Mods to review this.
ASKER
LSM: I'm sorry: pressed the button (accepted solution) on the wrong comment (my own)!
ASKER
Set dbs=CurrentDB: I suppose this should be set dbs=opendatabase(<BackendD
I 'll try SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact after 'de-linking' the backend