Update data in split database

[Shawn29316]

I have a split database and I need to replace one of the data tables in the back end weekly. I'd like to have the user who gets the new data file each week to replace the old file but I don't really want him in the back end database. Is it possible to write a query or code in the front end that would allow the table in the back end to be replaced?

[jforbes]

Where would the Table come from? Does the user make it in Excel, or is it a text file from another system, or an Access Database from another department?

Writing a routine to clear out a table and repopulate it from an external source is usually easy enough. Usually it is the integrity of the Source data that determines how difficult this can become.

[Shawn29316]

We receive an Excel file weekly, which is an extract from one of the corporate systems. We're using that data file to build our local systems to help us manage our little piece of paradise! I'm confident the data is good and since this is an extract, I have no concerns over compromising the upstream data source.

[jforbes]

A cheap and easy way to accomplish this is to place the spreadsheet in a user friendly directory and then link to the Spreadsheet from the Client. The caveat with this is that the data would be read-only for the spreadsheet and there would be no integrity checks on the data so if the extract contains strange data you could get strange results.

To take the model a little further, you could do all the above, then create an import routine that would import all the data from the spreadsheet into your working table(s) in the Backend Database. Then create a Button for your User to execute the import routine. That way they could replace the spreadsheet and run the Import from a button as needed.

One last note. The more structured and more consistent the data that is provided the easier it will be to update the data. If it is very structured and consistent, linking to the raw spreadsheet may cover all your needs. If the opposite is happening, you may have to write code that goes through the spreadsheet line by line and massages the data before inserting it into your backend tables.