Compact DB in Code

Several times over the years I have set up small maintenance programs
that compact backend databases at clients. I generally write a small
application and log results in a table so the client can check that the
compact worked. I then set up a scheduled event to run this
application and all seems to work great.

I was at a client this last week and wanted to set up the same thing.

My logic is using the dbengine.compactdatabase statement. I check if
the newdb exists and if so, I delete it. I then compact the sourcedb
into the newdb. Then I delete the sourcedb and copy the newdb to the
sourcedb. I also copy the newdb to a "play" db so the client has a
current play area of his data that he can work in and test different
things.

I log each of these activities in a table so the client can check if
the compacts have been working.

This has always been no issue.

This time though, for some reason, the newdb that is created comes back
with an unrecognizeable database format. The sourcedb is 275 meg and
the newdb is only 7 meg.

The other issue is that the code doesn't get any errors. Normally, the
compactdatabase will send back an error if someone is in the program or
anything like that and I log the error. This doesn't do that for some
reason. It continues on like nothing happened which would then put a
corrupt db out there as my product db and my play db.

Anyway, that obviously isn't good. The only nuance with this
application is that the database I am doing this with is an access
secured database. I open my compact application with the administrator
user and password but it still bombs. Not really sure what the issue
is. I can do a command line compact and repair with no issues so I am
at a loss on this one.

Any ideas would be greatly appreciated.

Here is the code if that would be helpful:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_form_open

Dim customix As Database
Dim filename As String
Dim filename2 As String
Dim filename3 As String
Dim sqlstring As String
Dim comphist As Recordset
Dim currfile As String
Dim playcomp As Recordset
Dim fs

Set customix = CurrentDb()

sqlstring = "Delete * from compacthistory where starttime#" & Date
- 7 & "#;"
customix.Execute sqlstring

Set comphist = customix.OpenRecordset("CompactHistory",
DB_OPEN_DYNASET, DB_APPENDONLY)
comphist.AddNew
comphist![DBName] = "MyData.mdb"
comphist![starttime] = Now()


currfile = "FP"

filename = "Z:\Data\AAA_TEMP.MDB"
filename2 = "Z:\Data\MyData.mdb"
filename3 = "z:\data\MyData_Play.mdb"

' delete the temporary file if it exists

Set fs = CreateObject("Scripting.FileSystemObject")
If (fs.FileExists(filename)) Then
Kill filename
End If

' compact the live db to the temporary db

DBEngine.CompactDatabase filename2, filename

' delete the live db

Kill filename2


' copy the temp db to the live db

FileCopy filename, filename2

Kill filename

' delete the play db

Kill filename3

' create a new play db

FileCopy filename2, filename3

Set customix = CurrentDb()
Set playcomp = customix.OpenRecordset("tblCompanyProfile",
DB_OPEN_DYNASET)
playcomp.MoveFirst
playcomp.Edit
playcomp![companyname] = "Play Area Created " & Format(Date,
"mm/dd/yy")
playcomp.Update

comphist![stoptime] = Now()
comphist![errormsg] = "Operation Successful!"
comphist.Update


Exit_form_open:
DoCmd.Quit
Exit Sub

Err_form_open:
comphist![stoptime] = Now()
comphist![errormsg] = Error & " - " & Err.Description
comphist.Update

Resume Exit_form_open
End Sub

Posting the identicall question twice makes it appear that there has been a
response to the question, which may mean that it is overlooked by busy
people who could answer it. For other good suggestions on effective use of
newsgroups, see the FAQ at http://www.mvps.org/access/netiquette.htm.

Start by looking for what may have changed between the times that this
worked just fine and "this time". Is, perhaps, "this time" a euphemism for
"I have developed what I thought was the same small maintenance program
anew."? If so, compare it to a previous one that worked. Can you reproduce
the error?

You have a "debug" problem, and we don't have enough information to do more
than suggest approaches to debugging.

Larry Linson
Microsoft Access MVP


"CaptainBly" wrote in message
oups.com...
Several times over the years I have set up small maintenance programs
that compact backend databases at clients. I generally write a small
application and log results in a table so the client can check that the
compact worked. I then set up a scheduled event to run this
application and all seems to work great.

I was at a client this last week and wanted to set up the same thing.

My logic is using the dbengine.compactdatabase statement. I check if
the newdb exists and if so, I delete it. I then compact the sourcedb
into the newdb. Then I delete the sourcedb and copy the newdb to the
sourcedb. I also copy the newdb to a "play" db so the client has a
current play area of his data that he can work in and test different
things.

I log each of these activities in a table so the client can check if
the compacts have been working.

This has always been no issue.

This time though, for some reason, the newdb that is created comes back
with an unrecognizeable database format. The sourcedb is 275 meg and
the newdb is only 7 meg.

The other issue is that the code doesn't get any errors. Normally, the
compactdatabase will send back an error if someone is in the program or
anything like that and I log the error. This doesn't do that for some
reason. It continues on like nothing happened which would then put a
corrupt db out there as my product db and my play db.

Anyway, that obviously isn't good. The only nuance with this
application is that the database I am doing this with is an access
secured database. I open my compact application with the administrator
user and password but it still bombs. Not really sure what the issue
is. I can do a command line compact and repair with no issues so I am
at a loss on this one.

Any ideas would be greatly appreciated.

Here is the code if that would be helpful:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_form_open

Dim customix As Database
Dim filename As String
Dim filename2 As String
Dim filename3 As String
Dim sqlstring As String
Dim comphist As Recordset
Dim currfile As String
Dim playcomp As Recordset
Dim fs

Set customix = CurrentDb()

sqlstring = "Delete * from compacthistory where starttime#" & Date
- 7 & "#;"
customix.Execute sqlstring

Set comphist = customix.OpenRecordset("CompactHistory",
DB_OPEN_DYNASET, DB_APPENDONLY)
comphist.AddNew
comphist![DBName] = "MyData.mdb"
comphist![starttime] = Now()


currfile = "FP"

filename = "Z:\Data\AAA_TEMP.MDB"
filename2 = "Z:\Data\MyData.mdb"
filename3 = "z:\data\MyData_Play.mdb"

' delete the temporary file if it exists

Set fs = CreateObject("Scripting.FileSystemObject")
If (fs.FileExists(filename)) Then
Kill filename
End If

' compact the live db to the temporary db

DBEngine.CompactDatabase filename2, filename

' delete the live db

Kill filename2


' copy the temp db to the live db

FileCopy filename, filename2

Kill filename

' delete the play db

Kill filename3

' create a new play db

FileCopy filename2, filename3

Set customix = CurrentDb()
Set playcomp = customix.OpenRecordset("tblCompanyProfile",
DB_OPEN_DYNASET)
playcomp.MoveFirst
playcomp.Edit
playcomp![companyname] = "Play Area Created " & Format(Date,
"mm/dd/yy")
playcomp.Update

comphist![stoptime] = Now()
comphist![errormsg] = "Operation Successful!"
comphist.Update


Exit_form_open:
DoCmd.Quit
Exit Sub

Err_form_open:
comphist![stoptime] = Now()
comphist![errormsg] = Error & " - " & Err.Description
comphist.Update

Resume Exit_form_open
End Sub

I certainly apologize to you Larry for posting a question twice and I
appreciate your appropriate flogging for it. In reality, I have never
posted on this particular news group before. I posted the question and
it didn't show up for over an hour so I thought maybe I did something
wrong so I posted it again. Thus the two posts. Again, I apologize
for my obvious and ridiculous error.

Now as for your response. Isn't responding with no answer the same
issue as accidentally posting a question twice? Did you not do the
same thing as you have accused me of doing? By posting a disciplinary
action on this thread, did you not indeed accomplish the same thing?
Just a thought.

It turns out, my code was fine and yes it has worked successfully
numerous times before at other clients. For some reason the
compactdatabase did not work on the network drive at this client.
Still not sure if it is a permissions issue or what but I was running
this as an administrator on the network and an administrator in the
workgroup security file. As soon as I copied the sourcedb to the local
drive on the machine I was running this on (actually the server), all
was well and the code worked fine.

Thanks again for your insight though. With answers like that, I see
how this forum is such a success.

aksksjdksjkdj
"Larry Linson" wrote in message
...
Posting the identicall question twice makes it appear that there has been
a response to the question, which may mean that it is overlooked by busy
people who could answer it. For other good suggestions on effective use of
newsgroups, see the FAQ at http://www.mvps.org/access/netiquette.htm.

Start by looking for what may have changed between the times that this
worked just fine and "this time". Is, perhaps, "this time" a euphemism
for "I have developed what I thought was the same small maintenance
program anew."? If so, compare it to a previous one that worked. Can you
reproduce the error?

You have a "debug" problem, and we don't have enough information to do
more than suggest approaches to debugging.

Larry Linson
Microsoft Access MVP


"CaptainBly" wrote in message
oups.com...
Several times over the years I have set up small maintenance programs
that compact backend databases at clients. I generally write a small
application and log results in a table so the client can check that the
compact worked. I then set up a scheduled event to run this
application and all seems to work great.

I was at a client this last week and wanted to set up the same thing.

My logic is using the dbengine.compactdatabase statement. I check if
the newdb exists and if so, I delete it. I then compact the sourcedb
into the newdb. Then I delete the sourcedb and copy the newdb to the
sourcedb. I also copy the newdb to a "play" db so the client has a
current play area of his data that he can work in and test different
things.

I log each of these activities in a table so the client can check if
the compacts have been working.

This has always been no issue.

This time though, for some reason, the newdb that is created comes back
with an unrecognizeable database format. The sourcedb is 275 meg and
the newdb is only 7 meg.

The other issue is that the code doesn't get any errors. Normally, the
compactdatabase will send back an error if someone is in the program or
anything like that and I log the error. This doesn't do that for some
reason. It continues on like nothing happened which would then put a
corrupt db out there as my product db and my play db.

Anyway, that obviously isn't good. The only nuance with this
application is that the database I am doing this with is an access
secured database. I open my compact application with the administrator
user and password but it still bombs. Not really sure what the issue
is. I can do a command line compact and repair with no issues so I am
at a loss on this one.

Any ideas would be greatly appreciated.

Here is the code if that would be helpful:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_form_open

Dim customix As Database
Dim filename As String
Dim filename2 As String
Dim filename3 As String
Dim sqlstring As String
Dim comphist As Recordset
Dim currfile As String
Dim playcomp As Recordset
Dim fs

Set customix = CurrentDb()

sqlstring = "Delete * from compacthistory where starttime#" & Date
- 7 & "#;"
customix.Execute sqlstring

Set comphist = customix.OpenRecordset("CompactHistory",
DB_OPEN_DYNASET, DB_APPENDONLY)
comphist.AddNew
comphist![DBName] = "MyData.mdb"
comphist![starttime] = Now()


currfile = "FP"

filename = "Z:\Data\AAA_TEMP.MDB"
filename2 = "Z:\Data\MyData.mdb"
filename3 = "z:\data\MyData_Play.mdb"

' delete the temporary file if it exists

Set fs = CreateObject("Scripting.FileSystemObject")
If (fs.FileExists(filename)) Then
Kill filename
End If

' compact the live db to the temporary db

DBEngine.CompactDatabase filename2, filename

' delete the live db

Kill filename2


' copy the temp db to the live db

FileCopy filename, filename2

Kill filename

' delete the play db

Kill filename3

' create a new play db

FileCopy filename2, filename3

Set customix = CurrentDb()
Set playcomp = customix.OpenRecordset("tblCompanyProfile",
DB_OPEN_DYNASET)
playcomp.MoveFirst
playcomp.Edit
playcomp![companyname] = "Play Area Created " & Format(Date,
"mm/dd/yy")
playcomp.Update

comphist![stoptime] = Now()
comphist![errormsg] = "Operation Successful!"
comphist.Update


Exit_form_open:
DoCmd.Quit
Exit Sub

Err_form_open:
comphist![stoptime] = Now()
comphist![errormsg] = Error & " - " & Err.Description
comphist.Update

Resume Exit_form_open
End Sub