Unanswered: Linking Access to Accting software package
I'm trying to link my accounting software to an access database. I have linked the table, which seems to work fine. My problem is I want the table from the accounting program to be read-only. I don't want to corrupt the raw data by allowing the user to add/delete or change the data.
I want to be able to add a table to the access program that the user can update. The linked table and the access table will have a join based on the salesID field.
I have tried the following 2 steps
1. I have defined the primary key on the linked table. This allows both the linked table and the access table to be updated. I don't want the linked table to be updated so this method doesn't work
2. If I don't define the primary key when setting up the link, the linked table becomes read-only. A new problem occurs when I try to setup a query. Both the linked table and the access table are read-only
Does anybody know how to set this up so I can update my access table but have the linked table read-only.
I seem to have fixed the original problem by using a subform in my main form. The next problem I have is not all the SQL tables I link to ask me to choose the unique record identifier. On the tables that it prompts me to choose the unique record identifier I don't choose one and this makes the table read-only.
In a few of the tables I link to its doesn't prompt me for this and thus the tables can be updated.
If you define a uniques key for 'your' table, and enure that the primary key in the 'other' table is not in you quereis then any query using that data will not be updateable.
However in order to ensire separation, if for audit purposes only I'd make certain that your users NEVER see the live data from the accounting system, you could import the data using an overnight batch process, or if theat degree of lag was unnaceptable comsider running the batch proicerss over lunchtime aswell.
I've done the same thing but was ultimately scared off my having a direct link to the acctg database (I didn't want to be responsible for bad code destroying permanent data). Instead, I created vb code that deletes my work table, re-imports from acctg a new table with the same name.
I further made this code run only when the database is running on my machine (by comparing the login id). Then, using Windows task schedule, I have it open the database and close it as often as it needs updated. In this case, it did not need to be up-to-the-minute current, so it updates at 7:30 each Monday and Thursday mornings.