Import from external form to master database table

[tim.cassey]

Hi there,



I have a master access database in use in the office, which has been split so that users can have their own front-end which then updates the separate back-end (allowing multiple user to enter data at the same time essentially).

What I now have is a situation where we have some people out of the office (in the field) who need to be able to use an external form (a new database with one form in it) to check some boxes associated with a serial number... then they would email this to the office, where the data could be imported to update a table in the master (to show whether certain serial numbers now had boxes checked).

The people in the field don't always have internet / any connection to the back-end back in the office, which was the reasoning behind the emailing.

Is something like this possible, and how would I go about it?

[CJ_London]

it is possible but needs careful consideration

if the nature of what the field users are doing is just updating existing records which they have copied from the office master db before leaving, you can have a simple routine for access to export a text file of the updated records and email to the office. And in the office you would have a routine to parse through emails and update the master record with the changes.

if the users are always in the field they will need updates for new records, you can have a similar routine in reverse.

On the other hand if field users are creating new records, you will need something that identifies new records and treat them as an append rather than an update - you can do this with a separate text file. The difficulty is creating a unique ID. The way I handle this is that you need a field called say TempID that is completed at the field user end - it can't be an autonumber, but might be say a combination of userid and a timestamp. The alternative is to use what is called a replicationID rather than an incremental autonumber. If the former, this gets appended to the master db where an autonumber is created and then mailed back to the field user to update their records.

The upshot is that the both ends will need a variety of import/export routines which can be triggered with the click of a single 'refresh' button.

[tim.cassey]

it is possible but needs careful consideration

if the nature of what the field users are doing is just updating existing records which they have copied from the office master db before leaving, you can have a simple routine for access to export a text file of the updated records and email to the office. And in the office you would have a routine to parse through emails and update the master record with the changes.

if the users are always in the field they will need updates for new records, you can have a similar routine in reverse.

On the other hand if field users are creating new records, you will need something that identifies new records and treat them as an append rather than an update - you can do this with a separate text file. The difficulty is creating a unique ID. The way I handle this is that you need a field called say TempID that is completed at the field user end - it can't be an autonumber, but might be say a combination of userid and a timestamp. The alternative is to use what is called a replicationID rather than an incremental autonumber. If the former, this gets appended to the master db where an autonumber is created and then mailed back to the field user to update their records.

The upshot is that the both ends will need a variety of import/export routines which can be triggered with the click of a single 'refresh' button.

Hi Ajax,

Appreciate the comments.

To expand, this is purely updating existing records. How would I achieve this? Would I put a local copy of the database on each field users laptop and then have them export a text file once they've completed 'checking boxes' of fixes to be emailed to the office?

I'm really not sure how I could go about coding it as I haven't dealt with exporting/importing before.

[tim.cassey]

In addition to this, the database will be getting updated by those in the office with new items, however these won't need to be seen by the field users as they are only updating older items (fixes in the field)... will the import of these text files be replacing the entire table structure (thus deleting the new records inputted in the office), or will it only update the fields that the field users have changed?

[CJ_London]

this is purely updating existing records. How would I achieve this?

As outlined in my initial post - I don't know how your organisation works so maybe you provide your users with a local copy of the backend, maybe not.

depends on your coding but I would expect to only update existing records where there are changes - I certainly would not expect to replace the entire table.

with regards coding, time to learn or use a developer. There are plenty of code examples for exporting data and emailing files if you google for them - some will be close to your requirements, some not - I can see a couple of possibilities at the bottom of this thread.

[tim.cassey]

Thanks again, has given me some ideas. I appreciate it