Solved

compact and repair

Posted on 2013-01-23
7
671 Views
Last Modified: 2013-01-28
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
Comment
Question by:corhaveman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38809644
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
 

Author Comment

by:corhaveman
ID: 38813267
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
 

Author Comment

by:corhaveman
ID: 38813287
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 85
ID: 38813799
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
 

Author Comment

by:corhaveman
ID: 38826395
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
 
LVL 85
ID: 38826396
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
 

Author Closing Comment

by:corhaveman
ID: 38826484
LSM: I'm sorry: pressed the button (accepted solution) on the wrong comment (my own)!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question