How to Set a Database Variable

  • Thread starter Thread starter Chaplain Doug
  • Start date Start date
C

Chaplain Doug

It was suggested to me that I use a "database variable" to
keep track of when I last imported an Excel spreadsheet
into a particular table. It was suggested that I use
something like:

CurrentDB().Properties("MyLastImport")

How would I create such a variable (MyLastImport) and how
would I set the variable? Thanks for the help.
 
Here's an example copied from the Help file:

This example tries to set the value of a user-defined property. If the
property doesn't exist, it uses the CreateProperty method to create and set
the value of the new property. The SetProperty procedure is required for
this procedure to run.

Sub CreatePropertyX()

Dim dbsNorthwind As Database
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Set the Archive property to True.
SetProperty dbsNorthwind, "Archive", True

With dbsNorthwind
Debug.Print "Properties of " & .Name

' Enumerate Properties collection of the Northwind
' database.
For Each prpLoop In .Properties
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop

Next prpLoop

' Delete the new property since this is a
' demonstration.
.Properties.Delete "Archive"

.Close
End With

End Sub

Sub SetProperty(dbsTemp As Database, strName As String, _
booTemp As Boolean)

Dim prpNew As Property
Dim errLoop As Error

' Attempt to set the specified property.
On Error GoTo Err_Property
dbsTemp.Properties("strName") = booTemp
On Error GoTo 0

Exit Sub

Err_Property:

' Error 3270 means that the property was not found.

If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = dbsTemp.CreateProperty(strName, _
dbBoolean, booTemp)
dbsTemp.Properties.Append prpNew
Resume Next
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop

End
End If

End Sub
 
CurrentDB().Properties("MyLastImport")

How would I create such a variable (MyLastImport) and how
would I set the variable? Thanks for the help.
Click to expand...

Also answered in m.p.a.tablesdbdesign ...


Tim F
 
Back
Top