Solved

compact and repair

Posted on 2013-01-23
7
621 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now