Linking to another database (1 Viewer)

[maclachlan]

I use a packaged software program at the dealership I work at. In the past I have been able to pull queries and information out of the database and into Excel. The query builder pulls the data as read-only and thus no fear of changing the main database. I have developed an access database for doing commissions. I want to be able to link my database with the companies database as read-only. This would save typing a lot of the duplicate information (ie customer name, date etc.). I have been able to successfully link the 2 databases but realized quickly that anything I change in msaccess will change on the main database. I don't want to corrupt the main database and really only want to pull the information for viewing. Any comments or help would be greatly appreciated.
Thanks
Rob

 

[IgorB]

You can create reports based on linked database, this will be read-only presentation to users or you.
Do your users have access to linked tables and can change data?
Is this your problem?

Igor.

 

[maclachlan]

yes the users have access to a form I have built in which they enter salesperson's commissions. There is really 2 parts to my MS access database.
1. Tables/Forms and Reports that I have built in access that I want the user to have permission to add/delete or change.
2. A linked table that comes from a SQL database that I only want the user to be able to reference. I have linked the sales ID in the SQL database with the sales ID in the access database. When the user enters the Sales ID on the form I want the header bar to fill in the appropriate information from the SQL table.
I also want the reports to reflect both of the tables info.

My main purpose behind this project is:
We enter a vehicle sale in a program called XSellerator (SQL database). All the customers information is entered by the salesperson. The commission database which I run out of access contains a lot of the same information. I would like to join the 2 tables so the user doesn't have to enter all the same information twice. The trick is I don't want the person using the access database to be able to change anything from the SQL database. I'm worried if they are able to change something I will corrupt our main database.

Thanks for the help as I'm relatively new to this stuff

 

[maclachlan]

Here is another way of trying to explain what I'm after

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.

Thanks

 

[jmurphy]

am sure others will have better solutions - but here's a couple of possible work arounds- import structure and data of linked table to your database and use autoexec macro to delete table and re-import each time your database is opened (so you're working off an updated copy each time and underlying data can't be corrupted) - only problem may be size of table and time-

second option - set up linked query - set up form for entry of new data (listing all data from linked database only in a linked subreport) so it can't be modified - and hide database window - good luck

 

[maclachlan]

Thanks jmurphy. IT WORKS!!
Linking the subform worked, I'm able to update my tables and have the SQL tables as read-only. I just need to work on how to make the subform look better, but that shouldn't be a problem.

 

[maclachlan]

Almost there
My next problem 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.
A few of the tables I link to its doesn't prompt me and thus the tables can be updated.