Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

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??
0
corhaveman
Asked:
corhaveman
  • 4
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
With linked tables you don't actually "open" the backend, so setting compact_on_close=true won't have any effect on the backend. You'll have to do that manually.

You can launch a process to compact the backend, but as you've seen this only works if there are no other users in the database. I cases like yours, where you must run a process and then compact, the best way to manage that is to open the backenend exclusively, run the process, and then compact. This is best done LOCALLY (i.e. not over the network).

You can copy objects from one database to another from a third database - I have a routine that does something very similar using TransferDatabase. It's quite simple:

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs=CurrentDB
For Each tdf in dbs.Tabledefs
  If Left(tdf.Name, 4) <> "MSYS" then
    DoCmd.TransferDatabase acExport, "Microsoft Access", Me.txDBPath, acTable, tdf.Name, tdf.Name

Note that "txDBPath" is a textbox that contains the full path to the new database file. Once you've completed the transfer, you can then Compact like this:

Dim sComp As String
        sComp = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact " & Me.txProjPath & "\" & Me.txProjName & ".accdb"
        Shell sComp

Note that txProjPath is the name of the NEW database I created ... that's a textbox on my form which would contain a value like "C:\SoemFolder\SubFolder", and "txProjname" is a texbox containing the actual name of my db, like "Project1".
  End If
0
 
corhavemanAuthor Commented:
LSMconsulting: thanks for the suggestion.
Set dbs=CurrentDB: I suppose this should be set dbs=opendatabase(<BackendDB>) otherwise the links are exported.
I 'll try SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE /compact after 'de-linking' the backend
0
 
corhavemanAuthor Commented:
LSM consulting: I'm wondering if  set dbs=opendatabase(<BackendDB>) works becasuse export is only possible from the current db in which case the links are exported ;-(
I'll give it a try.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Set dbs=CurrentDB: I suppose this should be set dbs=opendatabase(<BackendDB>) otherwise the links are exported.
You 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:

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 acExport, "Microsoft Access", Me.txDBPath, acTable, tdf.Name, tdf.Name
0
 
corhavemanAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
corhavemanAuthor Commented:
LSM: I'm sorry: pressed the button (accepted solution) on the wrong comment (my own)!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now