Seeking Cautionary Advice Re Linking Tables From Another Software Program

[Bill6868]

I inherited a piece of database software at work which is a good database but doesn’t give us the reports or allow us to make the statistical queries we need. All of the objects are protected, except for the data itself, which is stored in an Access database (just the tables). I can get all I need from these tables.

I’m being very cautious in accessing these tables as I don’t want to do anything to screw up the software program. I created my own Access database to create reports and queries by linking the tables from the software program. But I don’t do it directly – I copy the software’s Assess database and paste it in a separate folder and then link the tables I need to my Access database. This works fine but my copied database is only a snapshot of the data at the time I copy and paste it.

I’d like to link the software’s tables directly to my own database so I’d have real-time data but I am concerned that doing so may have adverse affects on the running of the software program.

I’m also concerned that a novice user may create a select query and not realize that when data is displayed it is “live” data and intentionally or inadvertently change data in the table (thus bypassing the data entry screens in the software which run update and append queries when they close). God forbid a user inadvertently creates an action query and then runs it. Although these are unlikely scenarios, it does give me the heebie-jeebies just thinking about it.

Any comments or suggestions would be most helpful. We are a non-profit organization working with the elderly so our mission also appreciates any feedback you may offer.

Bill6868

 

[dhookom]

I would caution against connecting directly to the data tables. You might cause some record locking or performance issues.

If you really need to, you can create a query for each table and use SQL like:

SELECT *
FROM Employees IN 'c:\temp\access\NorthWind.mdb';

Then make sure the Recordset Type is Snapshot. This should render the data as read-only. It doesn't keep your users from changing this. You could hide the original query and create a new query based on the original query.

Duane
Hook'D on Access
MS Access MVP

 

[madonnac]

Copy the access database to another location - this may be automated/run on a regular basis

Use that database as a backend to your database. That way, you are linking to a snapshot of the database, and not connecting to the original in any way, so if someone does affect the data, you can simply copy over a new one.