Hello,

With my Access "split" DB application, I have the following scenario that I need help with.

Backend is hosted on a server
Client is loaded on each person's local computer.

"Client" side has all the forms and what were thought to be fairly static tables (tblEmployees being one of them).
On the backend is a linked table and several other (unused tables). One of these unused tables is tblEmployees (the same setup as it's frontend counterpart). Now, what I'd like to do is update the backend table... and then anytime a user launches their frontend application, have it check the modified date of the FE table and compare it to the modified date of the BE table. If they are the same do nothing, but if the BE table has a more recent modified date, then replace the existing local table with a local copy of the BE table.

So far, all I've been able to do is get the local (Client side) table information using the following code

    Dim obj As AccessObject, dbs As Object, refcreate As String, refmod As String
    Set dbs = Application.CurrentData
    Set obj = dbs.AllTables!tblEmployees
        refcreate = obj.DateCreated
        refmod = obj.DateModified

Where I'm getting stuck is in how to get the same information, but from the BE table (named tblEmployees)

I assume I have to establish a connection to the BE database, which I have code for... but then what?? Do I need to open a recordset? I wouldn't think so being that that gives me all the contents of the table when all I want are the properties of it instead.

Is there even a way to get JUST the table information only?

Any help would be appreciated.

Rick

__________________
Rick

Hi,

   I would eliminate the tblEmployees from the front end, and just link it to the back end. All of your forms and reports etc will still work. It would just be linked instead of local.

   I know this means redistributing your front end, but you are going to have to do that anyway once you get this cludgey code working.



mmcdonal

Thanks. That is an option we've discussed, however due to performance issues (over the server), it is one we'd like to avoid if at all possible.

That's why I thought if there was a way to compare the modified dates of the two tables first, and then update only when the server side is "newer" than the client side, it would save on performance overall. (make sense?)

Any other ideas?

Rick

I did the same thing (copying from a BE table to a temporary FE table) to speed up the query for searching process in the FE forms, process which took too much time to run directly on the server data.

The data entering is in the linked table, on BE, and to keep the data in the FE updated the users have a button to refresh the temporary table. Pressing the button deletes all the record for this year and inserts all the records for this year from the BE table.
If this suits you, I can give details.

However, the solution we are thinking now is shifting the database from Access to SQL Server, 3tier etc, you might know more than me.
Regards,

Mihai

Hi,

   You could use a button to dump the local table and pull in data from the BE table. I have used that when I had monthly data, and I wanted users to be able to pull data for any month in the year. They could choose the month they wanted data from this way.

   You still have the full data pull issue, and the user has to decide to push the button as well.

   You may link the tables and then just filter your forms and just pull the data you need for one employee at a time, or a group of employees. That way your form only gets one or a few records at a time from the be.



mmcdonal

Thanks for the input. I like the idea of having a button available to the users to "refresh" the tables. This gives them the opportunity to refresh whenever they like, or when I indicate that updated information is available.

Thanks again for all the help!!

Best regards,

Rick