CompactDataBase runtime 3356 HELP NEEDED

[Pete]

I am using the following code to compact a backend database. However,
I am getting the 3356 error and can not thnk of what to try next.

The base program requires a logon process and then display a form with
with data from the backend. The backup command is issued through a
macro which results in the tables being dumped to an XLS file. Once
the tables have been dumped, I execute the code. I am missing
somthing. Any ideas would be helpful. THANKS in advance.

Sub CompactDB()
Dim conFilePath
Dim intX As Integer, _
intCOUNT As Integer
Dim rs As Recordset
Dim QD As QueryDef
On Error GoTo CompactDB_Err

' Make sure all forms are closed
intCOUNT = Forms.Count - 1
For intX = intCOUNT to 0 step -1
If Forms(intX).Name <> "frmBackup" Then
DoCmd.Close acForm, Forms(intX).Name
End If
Next intX

' Make sure all recordsets are closed
For Each rs In CurrentDb.Recordsets
rs.Close
Next rs

' make sure all querys are closed
For Each QD In CurrentDb.QueryDefs
QD.Close
Next QD

conFilePath = CurrentDb.TableDefs("Roster").Connect
conFilePath = Mid$(conFilePath, InStr(1, conFilePath, "DATABASE=")
+ 9)
conFilePath = Mid$(conFilePath, 1, InStr(1, conFilePath,
"ScoutRoster_Tables.mdb") - 1)

' Compact the database to a temp file.
DBEngine.CompactDatabase conFilePath & "DBASE1_Tables.mdb", _
conFilePath & "DBase1_Temp.mdb"

' Delete the previous backup file if it exists.
If Dir(conFilePath & "DBase1_Tables.bak") <> "" Then
Kill conFilePath & "DBase1_Tables.bak"
End If

' Rename the current database as backup and rename the temp file to
' the original file name.
Name conFilePath & "DBase1_Tables.mdb" As conFilePath &
"DBase1_Tables.bak"
Name conFilePath & "DBASE1_Temp.mdb" As conFilePath &
"DBase1_Tables.mdb"


Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox "Routine=" & Me.Name & ": CompactDB" & vbCrLf & _
"Error=" & Err.Number & vbCrLf & _
"Description=" & Err.Description

 

[Pete]

Forgot to add. I am the only user on the system and the system and the
system was started fresh for the testing.

 

[George Nicholson]

AFAIK, Jet won't compact a db while it is open. If the mdb you are executing
the code from has linked tables (which it seems to, since you're reading the
Connect sting to get the BE path), then the BE is open and can't be
compacted while in that state.

It doesn't require an open form, query or recordset to open the BE. Linked
tables automatically open it as well.

HTH,

 

[Pete]

Is there a way to force the lsystem to delink. I have logic for
closing all recordsetrs and queries. Something else??

 

[Pete]

Is there a way to force the system to delink. I have logic for
closing all recordsets and queries. Something else??

 

[Rick Brandt]

AFAIK, Jet won't compact a db while it is open. If the mdb you are executing
the code from has linked tables (which it seems to, since you're reading the
Connect sting to get the BE path), then the BE is open and can't be compacted
while in that state.

It doesn't require an open form, query or recordset to open the BE. Linked
tables automatically open it as well.

That is incorrect. The back end file is not "open" unless the front end file
is actually making use of one of the tables in the back end. If the front end
has no bound forms, bound reports, queries, tables, or recordsets actually in
use then the back end file should be just as "closed" as if Access were not even
running.

One can easily demonstrate this by watching the creation and deletion of the LDB
file for the back end. As soon as all such objects are closed the back end LDB
is deleted while the front end LDB remains until that file is closed outright.

 

[George Nicholson]

Curse me for a novice...

Rick, you are right (of course). When I checked behaviour, I saw an ldb
file, but of course it was for the FE file.

D'oh

 

[david epsom dot com dot au]

You are using the path for ScoutRoster_Tables.mdb,
but you are compacting DBASE1_Tables.mdb. Is that
deliberate?

(david)

 

[Albert D.Kallal]

Well, that 3356 means something is open...

' Make sure all recordsets are closed
For Each rs In CurrentDb.Recordsets
rs.Close
Next rs

The above will not work...you must go

dim mydb as database


set mydb = currentdb

Now, you can open reocrdsets as

set rstCustomers = mydb.openrecordset("tblCustomers")

remember, when you use currentdb, it is a function that returns an INSTANCE
of the database...but is then destroyed!!!

unless you open ALL databases through a created object as above, and use

eg:

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set rst = CurrentDb.OpenRecordset("test11")
Debug.Print CurrentDb.Recordsets.Count ---> will produce 0

Set db = CurrentDb
Set rst = db.OpenRecordset("test11")
Debug.Print db.Recordsets.Count ---> will produce 1

So, anytime you use need to modify tables, or structures (such as adding a
field to a table), you can't use currentdb, as it is function that returns a
instance.... (it is NOT a global SINGLE instance of the current database.,
but a NEW reference is created each time currentdb is used).

So, you will have to code a routine called "closeall", and CAREFULLY put
code in there that closes any open reocrdset.

 

[Albert D.Kallal]

AFAIK, Jet won't compact a db while it is open. If the mdb you are
executing the code from has linked tables (which it seems to, since you're
reading the Connect sting to get the BE path), then the BE is open and
can't be compacted while in that state.

That is incorrect. I do a compact all the time this way. Even my back up
code in fact uses the compact to do a copy of the back end.


as long as any bound form is closed, and all reocrdsets are closed, then no
connection is made....