Help with code

Hello all,

I was given the advice below by Justin Hoffman for my problem of
refreshing my tables the first time the DB is opened and not again until
the next day after 11am, but as a newbie i am not sure how to do it,
could somebody help me out please?

Create a new table to hold general info about the database e.g.
tblDbInfo
give it an ID column of type long with a validation rule of "=1" so the
table can only ever have one row. Another column should be
LastImportDate =
Date/Time.
The import routine should update the LastImportDate column after it has
finished, so then when somebody ones the database the macro only runs if
this field is at least 1 day old.


Tempy

*** Sent via Developersdex http://www.developersdex.com ***

re: Help with code

"Tempy" <anonymous@devdex.com> wrote in message
news:7HNwe.73$Vz1.47251@news.uswest.net...[color=blue]
> Hello all,
>
> I was given the advice below by Justin Hoffman for my problem of
> refreshing my tables the first time the DB is opened and not again until
> the next day after 11am, but as a newbie i am not sure how to do it,
> could somebody help me out please?
>
> Create a new table to hold general info about the database e.g.
> tblDbInfo
> give it an ID column of type long with a validation rule of "=1" so the
> table can only ever have one row. Another column should be
> LastImportDate =
> Date/Time.
> The import routine should update the LastImportDate column after it has
> finished, so then when somebody ones the database the macro only runs if
> this field is at least 1 day old.
>
>
> Tempy[/color]



Create a new module called modImport and paste in the following. Can you
see how to proceed from here?

Public Sub ImportData()

On Error GoTo Err_Handler

If IsImportDue() Then
' Need to refresh data
' Code for import here
'
If SetImportDate() Then
MsgBox "Updated"
End If
'
'
Else
' No need to refresh data
End If


Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub


Private Function IsImportDue() As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dteImport As Date

If Hour(Now()) < 11 Then
Exit Function
End If

strSQL = "SELECT LastImport FROM tblDbInfo WHERE ID=1"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

If Not rst.EOF Then
If Not IsNull(rst.Fields("LastImport")) Then
dteImport = rst.Fields("LastImport")
End If
End If

If DateDiff("d", dteImport, Now()) > 0 Then
IsImportDue = True
End If

Exit_Handler:

On Error Resume Next

rst.Close

Set rst = Nothing

Set dbs = Nothing

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


Private Function SetImportDate() As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "UPDATE tblDbInfo SET LastImport='" & _
Format(Now(), "yyyy-mm-dd hh:nn:ss") & "'"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffected = 1 Then
SetImportDate = True
End If

Exit_Handler:

On Error Resume Next

Set dbs = Nothing

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

re: Help with code

Thanks Justin, will attempt if i cant will come back.

Thanks for your help, it is appreciated.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***