Solved

compact and repair

Posted on 2013-01-23
7
654 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
  • 4
  • 3
7 Comments
 
LVL 84

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 84
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 84
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

821 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