Link to another Linked Table (1 Viewer)

I have an MS Access 2010 database that is split, with the tables back-end on a network share. In another folder of the network share (a folder that nobody but me has access to) lies a second database for a different set of data unrelated to the first one.

Is there a way that I can get a VIEW of the data in the SECOND database (in the protected folder) that users of the FIRST database can see? Keep in mind, these users do NOT have access to that protected folder. I just want them to be able to see the data but not touch it in any way, and moving it from where it is wouldn't be a very good idea for a multitude of reasons.

I was hoping there was some tricky way to create - in the FIRST database - a view of the data in the SECOND (protected) database, using some trick or another. I was even considering somehow making a 'copy' of the second database's table and dumping that data in the first database when a user executes some command or another, but I'll be darned if I can figure out how to do it.

You guys have any ideas? Thanks a million!

It seems a simple matter to write a tool that copies data from the private BE to the public one. There's no fancy trick here, just create an Access FE that you run on your machine with your credentials--so you can link it to both the private AND the public BEs--and write a bunch of code and/or queries to move the data you need your users to see from private to public.

If you want to get fancy: make the whole process automatic when you start the FE, and make the FE shut itself down when it's done. Then run it as a Scheduled Task in Windows at 5:30am every day. That way you can auto-synchronize your private data with the public data, and it's a one-way street.

hth
Mark

you cannot make a link table to
a link table in another db.

you can however create a Link table to your fe
pointing to the table to your second db.

or if you don't want to compromise the
location of your second db, use either DAO or
ADODB recordset through VBA to access the table.
therefore the the path to your second db will not
be known by inquiring on MSysObject table.

below is a sample of how would you open it
in both methods:

using ADO:


using DAO:

One issue with the solutions presented:

I will NOT be running this database myself, the USERS will be running it, and the application (fe) they open needs to be able to see the data. I understand that I could write a script that I can execute if I log in, but I won't be logging in or executing any scripts. I'm going to write this thing, then probably never touch it. It is a tool that the USERS (people who DO NOT have access to that second DB) will be using, and I need to provide them with a method by which they can get a snapshot view of the data in that second DB.

Does that make sense? Thanks!

From your description, there should be someone on site that has access to the secure folder that should be able to run the utility MarkK described. This is a network rights issue more than a DB issue so it should be fixable by having who ever manages their network handling the "Snapshot".

This could also be the same person responsible for maintaining your second DB.

That's the cornerstone of the issue. There won't be anyone onsite that handles that. The app is specifically for low-level employees, and there won't be a server admin on site unless there's a specific server problem, or server maintenance going on (once a month, maybe), and I'm not going to be getting off my rear and driving out there to perform the 'export', so it's not feasible at this time to have a person given the rights to get onto that server. It's got too much sensitive data for those users to be messing with.

I was really hoping there could be a tricky way to bridge a connection between the server they CAN see (where they can access and download the front-end of the app for their own workstations) and the data in that one column in that one table on the server they CAN NOT see.

From your description since this is a network rights issue I'd talk to their network administrator about setting up a server side fix. Their net admin should be able to do a batch file that runs on a schedule with admin rights to do this.

I do wonder though, is this a configuration file of some type that you want to keep users out of or is it something closer to a product price file where you don't want someone giving discounts?

Write a purpose-built FE on your machine that when opened, automatically copies data from the private to the public BE, and then run it as a scheduled task in Windows.

For anybody that cares, I figured out a solution, based upon some of the helpful suggestions herein. What I did was to create a small Access app that uses a wait script to run a function every 60 minutes which makes a copy of the data from tableset 1 (the restricted one) and pastes the data into tableset 2 (unrestricted) which is then linked-to in the users' app. So the restricted table remains where it is, in the restricted location, and the copier app (that's what I call the little copy-paste database I built) runs continuously on my computer, and once per hour, it sniffs for any changes made to the restricted tables by comparing them to the most recent copy, then updates the copy if any changes are detected. It's a little rough around the edges, and seems to me like a spit-and-glue kinda fix, but it works, so I'm not complaining. Thanks again for all your suggestions, folks!