We help IT Professionals succeed at work.

Access VBA

Murray Brown
Murray Brown asked
on
Hi

What VBA code would I use to detect any table or column differences between two Access databases. I am looking at two versions of the same back end and constantly need to check this

Thanks
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT
Commented:
would I use to detect any table or column differences between two Access databases


basically a SQL checking is needed.

you can refer to this article:

Compare two tables and find records without matches
https://support.office.com/en-us/article/compare-two-tables-and-find-records-without-matches-cb20ad48-4eba-402a-b20d-eaf10a5d1cb4

If you're about to comparing difference of data in all columns, in Step 9 as mentioned in the article, try to drag all the fields for comparison.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Well ...i had a similar case and i dealt it with concatenation
I made a simple code that iterated all fields on every record and produced a concatenated version of the row.
With this you can easily perform a join and see what is the same and what is not.
A variation of this was again to concatenate and produce a hash (search VBA hash) number and use this as field to join..
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:

At the risk of being the person that always asks questions, what specifically do you mean by "detect any table or column differences between two Access databases"?


I can see several interpretations, like:

·        Comparing two databases in their entirety identifying tables and columns present in one and not the other, or columns that have different schema definitions (type, scale, null, etc)

·        Comparing two tables with the same name between databases looking for schema differences

·        Comparing two tables with the same name between databases looking for data content differences

·        . . .


»bp


Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:

You'll need to code:


1. A query with an outer join to detect records in database "A" not in database "B"

2. A query with an outer join that does the opposite (record in "B" not in "A").

3. Code like John suggested, although I don't know that I'd bother with a hash.   Just open two recordsets, one for each table, then walk through the records and for each, loop through the fields collection and compare each.


  I guess though it depends on how often you would do this, if it would be considered a "normal" operation of the DB (not you as a developer checking things, but rather some form of sync utility between two DB's).


Jim.

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
you could also export your data out to Excel, and do cell matching, which is fast and easy, if that make sense to you.
President, J Street Technology
CERTIFIED EXPERT
Commented:
Hi Murray,

Because you mentioned "table and column differences", you seem to be asking about the structures of the two databases, not the actual data.  If so, you can buy FMS Total Access Detective and use it to report all differences between two Access database structures.  Might be a lot cheaper than taking the time to write something yourself.

Cheers,
Armen
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi
I was hoping that there would be VBA code to do this
I am working with a database backend where tables and columns are altered regularly on the front end then need to be changed
on each customer backend at different times
Thanks
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:


 Years ago, there was a product called "Data Angel", which did just this.   But it never took off, why?    Because in a well designed DB, it's rare to have to actually alter tables and when you do, often it involves much more than simply altering fields.   Data typically needs to be transformed as well (i.e. you put in a new invoicing system, changing all the tables).


 So there is no "one size" fits all type of approach.


Jim.

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks for the advice