Compact and Repair Database

[Mnewton911]

Is it possible to build a macro where you can compact and repair the database as one of the steps?

 

[Trevor G]

It is possible but you will have to create a Module and call the module in the MACRO.

Function RepairDatabase(strSource As String, _
strDestination As String) As Boolean
' Input values: the paths and file names of
' the source and destination files.
' Trap for errors.
On Error GoTo error_handler
' Compact and repair the database. Use the return value of
' the CompactRepair method to determine if the file was
' successfully compacted.
RepairDatabase = _
Application.CompactRepair( _
LogFile:=True, _
SourceFile:=strSource, _
DestinationFile:=strDestination)
' Reset the error trap and exit the function.
On Error GoTo 0
Exit Function
' Return False if an error occurs.
error_handler:
RepairDatabase = False
End Function

The code came out of the Help Menu, you will have to add some parts but if you read through the help file it will instruct you, then in your MACRO you add a runcode and find the function, remember to save before you create the macro.

I would ask why would compact as part of a procedure rather than when the database is closed?

 

[Mnewton911]

The Reason why i am using this as a step in a macro has to do with the fact that it resets an autonumber field within a table and i have a whole sequence that i run in order to do that.

 

[John Big Booty]

Do you mind if I ask you why you are resting an Auto Number? There may be an easier way of doing that if you used a Dmax() +1 arrangement, instead of the native Auto Number. Which by the way is only guaranteed to give you a unique number, not necessarily a consecutive number and not necessarily positive, there are a number of threads devoted to the discussion of this phenomena.

 

[Trevor G]

The Reason why i am using this as a step in a macro has to do with the fact that it resets an autonumber field within a table and i have a whole sequence that i run in order to do that.

I sometimes have to do something similar and I have created this code which will create a field and make it an autonumbering field as part of a process. I have to delete a table, re import it without a Autonumber so I created this and it works fine for my needs.

So I just call the Function as part of the process.

Function addID1()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld1 As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("Combined")

Set fld1 = tdf.CreateField("AnotherID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField
tdf.Fields.Append fld1
tdf.Fields.Refresh
Set tdf = Nothing
Set db = Nothing

End Function