Table problem

[Tym]

I have 2 databases - one is frontend (dbA) and one is backend(dbB)

I link to tableA in dbB

Is there a way for dbA to see the last modified date of linked table
tableA in dbB?



Tym

 

[Marshall Barton]

I have 2 databases - one is frontend (dbA) and one is backend(dbB)

I link to tableA in dbB

Is there a way for dbA to see the last modified date of linked table
tableA in dbB?

Depends on what you mean by "last modified". If you mean
the last time a record was changed, then, No, there is no
built in way to do that. If each record in the table has a
field with the date the record was changed, then you could
run a query to find the max date.

If you mean the last time the table's design was changed,
then you can use OpenDatabase to get to the backend db's
TableDefs collection and use the LastUpdated property.

 

[Tym]

Depends on what you mean by "last modified".

dbA has a table called, say [table1], which is deleted and recreated
every time a report is run.

I would like to have dbB look at dbA.table1 to see when it was
created/modified.

 

[Marshall Barton]

Depends on what you mean by "last modified".

dbA has a table called, say [table1], which is deleted and recreated
every time a report is run.

I would like to have dbB look at dbA.table1 to see when it was
created/modified.

You seem to have switched the A and B databases from your
first post. Assuming dbA is the front end and it has a
linked tableA2 to some table in dbB, you can use this kind
of code in dbA:

Dim dbB As DAO.Database
Dim tdfB As DAO.TableDef
Dim strPath2B As String

strPath = Mid(CurrentDb.TableDefs!tableA2.Connect, 11)
Set dbB = OpenDatabase(strPath)
Set tdfB = dbB.TableDefs!table1

Debug.Print = tdfB.DateCreated

Set tdfB = Nothing
dbB.Close : Set dbB = Nothing

How are you managing the bloat issue caused by this
temporary table? It is almost always better to create the
temp table in a temporary database. See:
http://www.granite.ab.ca/access/temptables.htm

 

[Tym]

How are you managing the bloat issue caused by this
temporary table? It is almost always better to create the
temp table in a temporary database. See:
http://www.granite.ab.ca/access/temptables.htm

Thanks for the code. I'll try that. Bloat? I use /compress regularly.

 

[Tym]

Tym wrote:
strPath = Mid(CurrentDb.TableDefs!tableA2.Connect, 11)
Set dbB = OpenDatabase(strPath)
Set tdfB = dbB.TableDefs!table1

Thanks... works fine when specifying the table name directly.

Is there a way of having the table name specified in a variable so I
could perhaps have it as a routine, or function to return the date of
any table?

Dim sNAME as String
strPath = Mid(CurrentDb.TableDefs!sNAME.Connect, 11)

Sort of thing.


Sorry if I sound a bit think o this,, but I'm not too up on this side
of things.

Thanks for your help

Tym

 

[Marshall Barton]

Thanks... works fine when specifying the table name directly.

Is there a way of having the table name specified in a variable so I
could perhaps have it as a routine, or function to return the date of
any table?

Dim sNAME as String
strPath = Mid(CurrentDb.TableDefs!sNAME.Connect, 11)

Note that the line with the Connect property can use any
table that's linked to the back end db. All tables linked
to the same back end will have the same path in the Connect
property, so, unless there is only one linked table, you do
not need to specify the same table as the one you want to
operate on. But, you can if you want to:

strPath = Mid(CurrentDb.TableDefs(sNAME).Connect, 11)

To refer to the specific TableDef object once the back end
db is opened, use this syntax:

Set tdfB = dbB.TableDefs(sNAME)

 

[Tym]

strPath = Mid(CurrentDb.TableDefs(sNAME).Connect, 11)

Excellent - Thanks