LastUpdated Method

[Chaplain Doug]

Access 2002. The LastUpdated method does not appear to be
working. I do the following on the table "Facilities"

dbs.Execute "DELETE * FROM Facilities;"
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "Facilities New", Facfile, -1
strSQL = "INSERT INTO Facilities SELECT * FROM [Facilities
New]"
dbs.Execute strSQL

All these "update" operations execute without error, yet
when I access CurrentDb.TableDefs("Facilities").LastUpdated
I get an old date(unchanged from before the operations).
Anyone know what's up?

 

[Tim Ferguson]

when I access CurrentDb.TableDefs("Facilities").LastUpdated
I get an old date(unchanged from before the operations).
Anyone know what's up?

The .LastUpdated property refers to the table design, not its contents --
i.e. the last time you made any change to the table structure. If you want
to remember when you last imported any records, you could:

1) use a database variable (e.g. CurrentDB().Properties("MyLastImport")

2) use a one-record table (or even record a history of the updates, and
just check the most recent)

3) Add a timestamp field to the Facilities table and use it to check when
each record is created (DefaultValue = Now() )

4) Keep the dates in a little red notebook or diary (but remember to back
it up with a photocopier!)

A couple of thoughts about the code you posted:

- Using db.Execute is good, but do use the dbFailOnError parameter so that
you can make sure the query has run okay. Without it, it will just fail
silently. You don't actually know that they are executing "without error"
unless you are checking the results by hand after every time.

- A lot of deleting and inserting will introduce a lot of db bloat. If
feasible, you might think about creating a new mdb file to hold the
temporary tables.

Hope that helps


Tim F

 

[Chaplain Doug]

The LastUpdated method appears to inconsistent then.
Becuase when I do the following on another table, it
indeed updates the LastUpdated date and time for the table
Chaplains:

dbs.Execute "DELETE * FROM Chaplains;"
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "Chaplains", Chapfile, -1

 

[Guest]

1) use a database variable (e.g. CurrentDB().Properties

("MyLastImport")

How does one create and update a database variable?

CurrentDB().Properties("LastChaplainUpdate") =Now()

Does not work nor does set work.

 

[Jeff Boyce]

Can we assume there's an underlying reason why you are trying to do this?
If you'll provide WHAT you want to accomplish, rather than HOW, 'group
readers may be able to offer alternative approaches.

 

[Chaplain Doug]

Bottom line . . . I periodically update some tables in my
DB using Excel spreadsheets from our home office. Before
running the update I need to determine the file dates of
the Excel spreadsheets (which I have no problem doing) and
I need to determine the last updates made to my tables
(i.e., last time the Excel spreadsheets were imported into
my tables). Since the tables have the
property .LastUpdated I thought this would be a good place
to get the information.

 

[Tim Ferguson]

CurrentDB().Properties("LastChaplainUpdate") =Now()

This is well documented in the Help file for the Properties collection. If
the property does not exist you have to create it first. Once it's there,
you can alter it and read it at will.

The most useful collection is perhaps the

db.Containers("Databases").Documents("UserDefined").Properties

because that can also be accessed from the menu File | Database
Properties... command, so if you get stuck it's easy to remove/ correct
them. The db.Properties can only be manipulated by VBA.

HTH


Tim F

 

[Tim Ferguson]

The LastUpdated method appears to inconsistent then.
Becuase when I do the following on another table, it
indeed updates the LastUpdated date and time for the table
Chaplains:

Can't help with that -- below is the description in the help file. It is
usually dangerous to rely on an undocumented feature, because it might not
always work.

: LastUpdated returns the date and time of the most
: recent change made to an object, or to a base table
: if the object is a table-type Recordset object
: (Microsoft Jet workspaces only).


HTH


Tim F