Compaction Code Question Acc2K3/2K format

[NoodNutt]

G'day Ppl

Grabbed this Compact Code from the following link but need some advice
please, am still a mushroom when it comes to DLookup & SQL.

http://www.tek-tips.com/faqs.cfm?fid=4998

Function MaintenanceCheck()

On Local Error GoTo MCError1

Dim stDatabaseName As String
Dim stLastCompacted As String
Dim stMessage As String
Dim stSQl As String
Dim stTimeNow As String
Dim stToday As String

stToday = Format$(Now, "yyyymmdd")
stLastCompacted = DLookup("[ParameterValue]", "tblControl1",
"[ParameterName] = 'LastCompacted'")
stDatabaseName = DLookup("[ParameterValue]", "tblControl1",
"[ParameterName] = 'DatabaseName'")

'My Code Interpretation

stLastCompacted = DLookup("[LastCompacted]", "tblCompactLog",
"[LastCompacted] = 'LastCompacted'")
stDatabaseName = DLookup("[DatabaseName]", "tblCompactLog",
"[DatabaseName] = 'DatabaseName'")

'My Table Structure Table Name = tblCompactLog
LastCompacted = Date/Time ("yyyymmdd")
DatabaseName = "MyDBNameV1" '

If stLastCompacted >= stToday Then
Exit Function
End If

stMessage = "You are the first person to use this database today." &
vbCrLf & vbCrLf

If intSecurityLevel = 1 Then
stMessage = stMessage & "Please ask someone with Data-entry or
Administrator permissions "
stMessage = stMessage & "to log in and run start of day maintenance."
MsgBox stMessage, vbInformation, stDatabaseName
Exit Function
Else
stMessage = stMessage & "When you click [OK], start of day maintenance
will take place." & vbCrLf & "Please wait ..."
MsgBox stMessage, vbInformation, stDatabaseName

stMessage = SysCmd(acSysCmdSetStatus, "Daily Maintenance In Progress ...
Please Wait")
End If

(Dunno what this is for, it doesn't work) stMessage =
WriteLogRecord("CompactDatabase", "MaintenanceCheck", "")


'My Code Interpretation

stSQl = "UPDATE tblCompactLog SET [tblCompactLog].[LastCompacted] = '" &
stToday & "' WHERE [tblCompactLog].[LastCompacted]= 'LastCompacted'"

This gives me an error message (Null Value)

stSQl = "UPDATE tblControl1 SET [tblControl1].[ParameterValue] = '" &
stToday & "' WHERE [tblControl1].[ParameterName] = 'LastCompacted'"
DoCmd.SetWarnings (False)
DoCmd.RunSQL (stSQl)
DoCmd.SetWarnings (True)

CompactDatabase

Exit Function

MCError1:

MsgBox CStr(Err) & " - " & Error$
Resume MCEnd

MCEnd:
End Function

TIA
Mark.

 

[Scott McDaniel]

G'day Ppl

Grabbed this Compact Code from the following link but need some advice
please, am still a mushroom when it comes to DLookup & SQL.

http://www.tek-tips.com/faqs.cfm?fid=4998

Function MaintenanceCheck()

On Local Error GoTo MCError1

Dim stDatabaseName As String
Dim stLastCompacted As String
Dim stMessage As String
Dim stSQl As String
Dim stTimeNow As String
Dim stToday As String

stToday = Format$(Now, "yyyymmdd")
stLastCompacted = DLookup("[ParameterValue]", "tblControl1",
"[ParameterName] = 'LastCompacted'")
stDatabaseName = DLookup("[ParameterValue]", "tblControl1",
"[ParameterName] = 'DatabaseName'")

'My Code Interpretation

Not sure what your "code interpretation" is, but the code above basically
returns 2 values ... each of those are housed in tblControl. The first gets
the value from the [ParameterValue] column IF the
[ParameterName]=LastCompacted ... the next looks for Databasename value.

When you built tblControl, did you populate it with at least 2 records - one
with a ParameterName of LastCompacted, another with ParameterName =
DatabaseName? You'd need at least those 2 for this to work, and I believe
you should also wrap this in an Nz:

stLastCompacted = Nz(DLookup("[ParameterValue]", "tblControl1", >
"[ParameterName] = 'LastCompacted'"), "01/01/1900")

This would insure that you return something even if the table contains
nothing ....

stLastCompacted = DLookup("[LastCompacted]", "tblCompactLog",
"[LastCompacted] = 'LastCompacted'")
stDatabaseName = DLookup("[DatabaseName]", "tblCompactLog",
"[DatabaseName] = 'DatabaseName'")

'My Table Structure Table Name = tblCompactLog
LastCompacted = Date/Time ("yyyymmdd")
DatabaseName = "MyDBNameV1" '

If stLastCompacted >= stToday Then
Exit Function
End If

stMessage = "You are the first person to use this database today." &
vbCrLf & vbCrLf

If intSecurityLevel = 1 Then
stMessage = stMessage & "Please ask someone with Data-entry or
Administrator permissions "
stMessage = stMessage & "to log in and run start of day maintenance."
MsgBox stMessage, vbInformation, stDatabaseName
Exit Function
Else
stMessage = stMessage & "When you click [OK], start of day maintenance
will take place." & vbCrLf & "Please wait ..."
MsgBox stMessage, vbInformation, stDatabaseName

stMessage = SysCmd(acSysCmdSetStatus, "Daily Maintenance In Progress
... Please Wait")
End If

(Dunno what this is for, it doesn't work) stMessage =
WriteLogRecord("CompactDatabase", "MaintenanceCheck", "")

This would fire the WriteLogRecord function; you probably don't have this
function in your database, so just comment it out.

'My Code Interpretation

stSQl = "UPDATE tblCompactLog SET [tblCompactLog].[LastCompacted] = '"
& stToday & "' WHERE [tblCompactLog].[LastCompacted]= 'LastCompacted'"

This gives me an error message (Null Value)

I'm betting you're getting a null value becuase your DLookups are
functioning correctly. The easiest way to see what's going on is to set a
Breakpoint immediately before your SQL is run, and then print the stSQL
variable to the Immediate window (type this in the Immediate window: ?stSQL)

stSQl = "UPDATE tblControl1 SET [tblControl1].[ParameterValue] = '" &
stToday & "' WHERE [tblControl1].[ParameterName] = 'LastCompacted'"
DoCmd.SetWarnings (False)
DoCmd.RunSQL (stSQl)
DoCmd.SetWarnings (True)

CompactDatabase

Exit Function

MCError1:

MsgBox CStr(Err) & " - " & Error$
Resume MCEnd

MCEnd:
End Function

TIA
Mark.

 

[NoodNutt]

G'day Scott

Thx heaps for your reply.

I decided to simplify the compacting process by calling the CompactDatabase
function via a command button, along with a stern warning message whilst it
is running. Fortunately it is stand-alone and only 1 monkey will be using
it. One has to assume he can read

So far all good, no hiccups.

Thx again

Regards
Mark.