Hi geo,
As Pat suggested in her discussion about decompiling, you
may do something that I do -- construct lots of SQL in code.
For grins, run find out what the db has for stored
queries... this may surprise you...
'~~~~~~~~~~~
Sub listQuerySQL()
Dim qdf As dao.QueryDef, db As dao.Database
Dim i As Integer
Set db = CurrentDb 'or whatever
i = 0
For Each qdf In db.QueryDefs
i = i + 1
If MsgBox(qdf.SQL, vbOKCancel, _
i & " " & qdf.Name) = vbCancel Then Exit Sub
'space in debug widnow is limited
' -- not all will show... look at counter (i)
'to see what you are missing
Debug.Print "--- " & i & " -- " & qdf.Name & " ---"
Debug.Print qdf.SQL
Next qdf
Set qdf = Nothing
Set db = Nothing
End Sub
'~~~~~~~~~~~~~~
if you do have a lot of these temporary queries, you may
wish to set up a routine to delete them. They start with ~
I don't know how this will work for mde files... but here is
some code that works for mdb files that you can perhaps
adapt to compile your db
'~~~~~~~~~~~~~~~~~
'NEEDS REference To Microsoft DAO Library
'make sure DAO is as high up on the order as it will go
'------------------------------------------ CompactDB
Sub CompactDB(pDB As String)
On Error GoTo CompactDB_error
' make sure db is there
If Len(Dir(pDB)) = 0 Then
MsgBox pDB & " does not exist", , "Missing File"
Exit Sub
End If
Dim mTempDB As String
'always compact/repair on a local drive
mTempDB = "c:\tmp.mdb"
'erase temporary file
If Len(Dir(mTempDB)) > 0 Then
DeleteFile mTempDB
End If
DBEngine.CompactDatabase pDB, mTempDB
' make sure compacted db is there
If Len(Dir(mTempDB)) > 0 Then
' delete original
DeleteFile pDB
' rename compacted db back to original name
Name mTempDB As pDB
WaitForFile pDB
End If
CompactDB_exit:
Exit Sub
CompactDB_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
CompactDB"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume
GoTo CompactDB_exit
End Sub
'------------------------------------------ WaitForFile
Sub WaitForFile(pFile As String)
On Error GoTo WaitForFile_error
SysCmd acSysCmdSetStatus, "Waiting for " & pFile & " ..."
Do While Len(Dir(pFile)) = 0
' wait for file to be there
DoEvents
Loop
WaitForFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub
WaitForFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " WaitForFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume
GoTo WaitForFile_exit
End Sub
'------------------------------------------ DeleteFile
Sub DeleteFile(pFile As String)
On Error GoTo DeleteFile_error
Kill pFile
SysCmd acSysCmdSetStatus, _
"Waiting for " & pFile & " to get erased ..."
Do While Len(Dir(pFile)) > 0
DoEvents
Loop
DeleteFile_exit:
SysCmd acSysCmdClearStatus
Exit Sub
DeleteFile_error:
MsgBox Err.Description _
, , "ERROR " & Err.Number & " DeleteFile"
'press F8 to step through code and fix problem
'comment out next two lines after code is debugged
Stop
Resume
GoTo DeleteFile_exit
End Sub
'~~~~~~~~~~~~~~`
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
