Comparing Access Tables?
Hi there! I think this might be a simple one but...
I have an Access DB (with multiple tables) that is user changable. However, when my program ends, I write data to the DB that must be the same when my program starts again. My problem is ensuring the user has not changed the DB between executions of my program.
My thought is to write out a main, user accessable table, and a second duplicate hidden copy. Then, when I restart I can compare the two tables and if they are dissimilar, I know we have an invalid situation and I can reset myself.
If this is the best way (and please if you have a better idea LET ME KNOW!) how do I do a compare of tables within VB? I must remain independant of the data and fields in the DB, as these are user definable. So really what I need is "if DBTable1 <> DBTable2..."
Thanks for any help you can offer!
SerJase
[902 byte] By [
SerJase] at [2007-11-17 17:07:59]
# 1 Re: Comparing Access Tables?
my first thought on this matter is as your program closes and just after it closes the database, make a copy of the database and place it in a hidden folder in the windows directory and then when your program starts up, just before it opens the data base, it renames the original one in the program directory and moves the one in the windows directory back to the program directory and deletes the one it renamed. Will that work?
# 2 Re: Comparing Access Tables?
That would work, however if they do make changes, I need to know about it. If I understand you correctly, your method would enforce the use of the original DB. I need to know, upon startup, whether or not to configure based on the last runs info, or to reset to system defaults. This thus requires knowing if the DB has changed... =/
# 3 Re: Comparing Access Tables?
could loop through the two database tables and compare, they should be exact, if they arent then they been changed. could also take the sizes of both and compare the number of bytes, if someone changes one then the bytes are gonna be different. Also could look at the date modified time.
# 4 Re: Comparing Access Tables?
I'm not an expert in VB but i will try to give my opinions regarding this problem. My understanding is that you want to know if your DB has changed upon starting and ending your program, is that right?
My suggestion is that upon starting your program, make a duplicate copy of your DB and rename it as to preserve the original structures and values of your DB. In this way, you can restore your original values anytime you want. You can also compare the original DB to the DB being used for modifications. One simple way of comparing two tables if they are the same or not is to compare the values in each fields in every table.
# 5 Re: Comparing Access Tables?
Originally posted by nokiamania
I'm not an expert in VB but i will try to give my opinions regarding this problem. My understanding is that you want to know if your DB has changed upon starting and ending your program, is that right?
My suggestion is that upon starting your program, make a duplicate copy of your DB and rename it as to preserve the original structures and values of your DB. In this way, you can restore your original values anytime you want. You can also compare the original DB to the DB being used for modifications. One simple way of comparing two tables if they are the same or not is to compare the values in each fields in every table.
this is what ive said but reworded.
# 6 Re: Comparing Access Tables?
Lo mate
If you just want to see if the user has made changes but not keep them
why don't you make another field in your table called 'changed' or something and then :
on the willchangefiled ,willchangerecord or willchangerecordset methods have them modify that field to Yes and then exit the sub
this will make it so the table is not updated APART from the changed field
If thats not what your after then just use your method :)
# 7 Re: Comparing Access Tables?
So you want to compare tables ?
SQL might help you out.
Query 1 as follow :
Select * from table1
Union all
Select * from table2
SELECT Count(Query1.Field1), Query.field1, Query1.Field2, Query1.Field3
FROM Query1
GROUP BY Query1.Field1,Query1.field2,Query1.field3;
(Extend to all fields of your table)
if you get any row not = 2 then you have the records modified.
# 8 Re: Comparing Access Tables?
Originally posted by SerJase
Hi there! I think this might be a simple one but...
I have an Access DB (with multiple tables) that is user changable. However, when my program ends, I write data to the DB that must be the same when my program starts again. My problem is ensuring the user has not changed the DB between executions of my program.
Thanks for any help you can offer!
SerJase
Hi SerJase,
My suggestion is to compare the MODIFIED DATE of the DB. At the end of your program (Note: YOU MUST CLOSE THE DB and RELEASE ALL REFERENCES TO THE DB) get the LAST MODIFIED DATE and stamp it somewhere (REGISTRY/DATA FILE) that is hidden from the user.
When you load up again, get the current LAST MODIFIED DATE and compare with the previous one. If they are not the same, then user modified the data w/out your app.
My two cents,
-Cool Bizs
# 9 Re: Comparing Access Tables?
Is it tables he wants to compare or database ?
# 10 Re: Comparing Access Tables?
Originally posted by Boumxyz2
Is it tables he wants to compare or database ?
From what I understand is, he want to make sure that his DB is not changed outside of this application. I might be wrong :)
-Cool Bizs
# 11 Re: Comparing Access Tables?
You can password protect your database, and when opening it from VB, passing the password. In that case, nobody will be able to modify datas from the database except from your application. If you want to let user browse datas out of your application, on the closing of your application, copy the database and put a password in it (I don't really know how to do it, but it is surely possible), then, use the Boumxy3 Union SQL to compare the 2 tables at the application opening, it might take a long time if you have a lot of datas.
Alternative way could be to stock size of the databases in registry or in a file ( in a way it can't be changed from the user), or using CRC on your database instead of size.
Jeff
JeffB at 2007-11-10 0:32:52 >
# 12 Re: Comparing Access Tables?
passwording an access database is a waste of time, you can crack that password in a matter of seconds.
# 13 Re: Comparing Access Tables?
Originally posted by coolbiz
From what I understand is, he want to make sure that his DB is not changed outside of this application. I might be wrong :)
-Cool Bizs
Actually, I need to ensure a few tables within the database have not changed. For example, if my database has 5 tables, I need to ensure 3 of them have not changed. Really, what I need is a modified date per table. Looks like the code provided by Boumxyz2 might be the best solution thus far. I must admit however, I would have hoped Access provided a more direct way of telling whether or not a table has changed.
Thanks all for your discussion! This board is an invaluable tool!
