Can queries/reports affect data in linked tables?

[irishandy]

This problem involves three databases:

DB1 = data only. This db contains 7 tables and nothing else.

DB2 = anything except data. This db uses linked tables (linked to DB1) and contains queries, reports, forms and code and is used for entering and viewing data.

DB3 = similar to DB2 except it is only used for viewing data.


The initial setup comprised DB1 and DB2. These are pre Access 97 databases and their structure is locked, i.e. no additions, deletions or amendments can be made to tables, queries, forms or reports.

DB3 was created by making a copy of DB2 and converting it to Access 97. The reason for creating this database was to give the user a way of running queries/reports that had not been included as part of the original DB2 specification. Similarly to DB2, DB3 does not store data. It too links to the tables contained in DB1. It should be noted that only queries and reports are run in DB3 - data is never entered or adjusted in any way.

A short while ago it was noticed that a large amount of data had disappeared from the largest table in DB1 (~50% of the records). I'm trying to track down how this happened.

Is there any conceivable way that DB3 could have had an effect on the data in DB1?

 

[MakeItSo]

This db uses linked tables (linked to DB1)

This means, that any action is performed on db1, no matter from which db the queries are run.
If db3 is a copy of db2, I assume that it is also linked to the db1 tables.
Thus all queries run in db3 will also affect db1.

As long as you only run SELECT queries, there shouldn't be any problem.
If you run any action query like INSERT, DELETE, APPEND, UPDATE... you will always perform the action on db1, since db2 and 3 do not physically store any data at all - all's in db1.

Hope you can restore missing data from some older backup.


Good luck,
Andy


[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[irishandy]

This means, that any action is performed on db1, no matter from which db the queries are run.

Precisely.

If db3 is a copy of db2, I assume that it is also linked to the db1 tables.
Thus all queries run in db3 will also affect db1.

Also correct.

As long as you only run SELECT queries, there shouldn't be any problem.
If you run any action query like INSERT, DELETE, APPEND, UPDATE... you will always perform the action on db1, since db2 and 3 do not physically store any data at all - all's in db1.

This is exactly what I had understood to be the case. Thanks for the reassurance. I just want to rule out the merest possibility of a problem with DB3 using the same data.

Thanks for your reply.

 

[MakeItSo]

So this is indeed what you intended? Sounds appeasing.

I just want to rule out the merest possibility of a problem with DB3 using the same data.

Hmmh.. there is a merest possibility of problems occuring:

...pre Access 97 databases

That's a point, I guess. I don't know about the changes on internal encoding / structuring of the tables in earlier versions.
If you want to be sure, I'd recommend creating a new Acc. 97 db, convert a copy of your old db1 to 97 and use this new db as data source.

Plus: Better keep regular backups. As Murphy says:
[blue]If anything simply cannot go wrong, it will anyway.[/blue]
;-)
Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[irishandy]

As Murphy says:
If anything simply cannot go wrong, it will anyway.

Don't I know it!