[AccessD] Separate mdb for housing temp tables

Hi Borge

This works extremely well. We use a write-protected master which we copy, but you could for a simple temp database create it from scratch - it takes no time. Both methods have cons and pros.

Here's a code snippet where we create a verified copy of the master file by compacting it to a new file, the file that the app will use. If it was not deleted when the app closed, it will be reused. You may choose to delete it if found and replace it with a new copy - that frees you from emptying tables:

<snip>

  If Len(Dir(strTempFileMaster)) > 0 Then
    ' Master file exists.
    If Len(Dir(strTempFileData)) = 0 Then
      ' Work file is not found. Create new copy.
      DBEngine.CompactDatabase strTempFileMaster, strTempFileData
    End If
  Else
    strMsgPrompt = "A needed file, " & LCase(strTempFileMaster) & ", is missing." & strCrLf
    strMsgPrompt = strMsgPrompt & "The application cannot run without it."
  End If
  If (Len(Dir(strTempFileData)) > 0) Then
    ' Work file existed or has been created.
    booSuccess = True
  ElseIf Len(strMsgPrompt) = 0 Then
    strMsgPrompt = "A needed file, " & LCase(strTempFileData) & ", is missing." & strCrLf
    strMsgPrompt = strMsgPrompt & "The application cannot run without it."
  End If

  If Len(strMsgPrompt) > 0 Then
    DoCmd.Beep
    MsgBox strMsgPrompt, lngMsgStyle, strMsgTitle
  End If

</snip>

Let your function return booSuccess.

Note that if you do not create objects or write to internal tables in your frontend database, you may be able to write protect the frontend file itself. That frees you from any corruption or bloat.

/gustav

>>> pcs at azizaz.com 24-08-2006 07:01 >>>
Dear List,

I have an application that make use of many temp tables.
In an effort to reduce FE bloat and not having to automate an FE compact I've been thinking of placing all temp tables in a separate
mdb placed in same folder as the FE and link the tables to the FE.

This temp mdb will exist in two versions:

FE_tempORG, mdb in compacted state -
FE_temp , the one where the temp tables are linked to the FE

The startup procedure of the FE will first up shell out and copy the FE_tempORG over the FE_temp and then continue with checking
linking to BE and FE_temp, supply the login routine, etc. ...

I admit I haven't searched the archives, the answer may already be there, but...

Is this a way to go, or do you have other suggestions?

regards
Borge