Back end data table question (Access 2K)

Topic

New Reply

Dear All

I

Reply | Quote

Replies

Is the imported spreadsheet linked to other tables? If so, you might have problems. Otherwise, you could use DoCmd.TransferDatabase to export the table from the front end to the back end. If the table already exists, and not in use, it will be overwritten by the new one.

Reply | Quote

Hans

Thanks for the suggestion, that’s one all to obvious solution I’d overlooked……

My concern is that it may be possible for some one to leave the database open all night, so when the ‘first’ user logs on and their front end detects the updated Excel files it will not be able to overwrite the old tables. This was one of the reasons for me looking at the idea of using two queries, a delete and an append. While this will mean more complexity I think it may be a more reliable method? So, are there any pitfalls I should avoid by using the query methods?

Thanks

Ian

Reply | Quote

Depends on whether the table is linked to other tables, but that problem would occur with any method. Otherwise, the delete and append queries should work OK.

Reply | Quote

Hans

Nope, no links. Whether this is good practice or not I’m uncertain but I tend to try and avoid table level links, unless absolutely necessary, and only link tables at the query stage.

I’ve now got the query method working, just got to work out the ‘Timer’ instruction to create a two minute delay if the ‘file in use’ error (error 1004) is generated. I’m most of the way there now, thanks for the help.

btw,my two minute delay is based on the longest time I’ve recorded the update taking, plus 200 percent, that should keep the code out of trouble.

Ian

Reply | Quote

Joins between tables are very useful, and in fact an essential part of a relational database. Enforcing relational integrity, and specifying cascading updates and deletes are important to ensure the quality of your data, and take a lot of work out of your hands.

Reply | Quote

Joins between tables are very useful, and in fact an essential part of a relational database. Enforcing relational integrity, and specifying cascading updates and deletes are important to ensure the quality of your data, and take a lot of work out of your hands.

Reply | Quote

Hans

Nope, no links. Whether this is good practice or not I’m uncertain but I tend to try and avoid table level links, unless absolutely necessary, and only link tables at the query stage.

I’ve now got the query method working, just got to work out the ‘Timer’ instruction to create a two minute delay if the ‘file in use’ error (error 1004) is generated. I’m most of the way there now, thanks for the help.

btw,my two minute delay is based on the longest time I’ve recorded the update taking, plus 200 percent, that should keep the code out of trouble.

Ian

Reply | Quote

Depends on whether the table is linked to other tables, but that problem would occur with any method. Otherwise, the delete and append queries should work OK.

Reply | Quote

Hans

Thanks for the suggestion, that’s one all to obvious solution I’d overlooked……

My concern is that it may be possible for some one to leave the database open all night, so when the ‘first’ user logs on and their front end detects the updated Excel files it will not be able to overwrite the old tables. This was one of the reasons for me looking at the idea of using two queries, a delete and an append. While this will mean more complexity I think it may be a more reliable method? So, are there any pitfalls I should avoid by using the query methods?

Thanks

Ian

Reply | Quote

Is the imported spreadsheet linked to other tables? If so, you might have problems. Otherwise, you could use DoCmd.TransferDatabase to export the table from the front end to the back end. If the table already exists, and not in use, it will be overwritten by the new one.

Reply | Quote