Next Step - sync offline and online DB

[txbakers]

I have it worked out in theory, but not in practice, and I'd appreciate some brainstorming. I might even have to hire this portion out.

Once a user is finished working offline, I need a way for them to send the MDB file to me and have it upload all the data into the master DB. My vision is to have a "one-click" solution for the user and have the MDB file sent to my server somehow, and when received, let the process begin.

Does anyone know of a method for this, or will I have to hire a "real" programmer?

Thanks.

[dmorison]

I don't know for sure; but "knowing" Microsoft; you can probably instantiate an MS Access database object; open the uploaded file, retrieve what you will and squirt it into your master DB.

This would probably involve installing Microsoft Office on your server; and could then be done in a single .ASP script.

[dmorison]

Actually, can't you "just" create an on the fly DSN to open an ODBC connection to the uploaded database?

Shouldn't be any need to mess about with an Access object then, you just use the standard Active Data Objects (ADO).

[aspdaddy]

I think the problem is knowing what records to update, insert and delete. AFAIK Access doesnt support this type of merge, though there may be some
workarounds [databasejournal.com].

If it is just inserts, they could be recorded in a text file and uploaded, a server side script could then read the text file and create and execute the insert statements.

[txbakers]

thanks for that - I'll bookmark it and see what I can do.

[richardb]

Could not get through to the work arounds link... but on a netwrok you can use the synchronize option via creating a replica however,

1. I'm not sure if you can use this option via the net
2. It's open to abuse unless they upload to a backup, which then after checking becomes the master.

Rich

[txbakers]

which then after checking becomes the master.

Yes exactly. That's the issue I have now with people uploading data to the database. I don't allow it, period. If they have a file to upload, they send it to me and I validate it, add the special codes and then upload it.

They can download to their hearts content.

So for this project, the same issue comes up. Somehow I need to make it seamless for them.

Here's the pseudo-idea:
1) person clicks on "check-out files" which first populates an MDB file then downloads it to their computer. That user code gets makred as "locked" in the masterDB so no other user can modify entries while stuff is checked out.

2) The user then launches their "offline" version of the program and does what they want to.

3) The user clicks "Check-in files" and that MDB file gets sent to the office, is compared with the master file, changes to the master are made, and the lock is removed.

I think I might be able to get the file automatically from their HD since I'll be installing it. Yep, I need some quiet time to figure it out.