Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

compact and repair

Posted on 2013-01-23
7
Medium Priority
?
678 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 2000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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