Compare 2 tables

Experts, is there a canned function in Access to compare two tables and report the differences as far as new data, deleted data, what was changed (ie an amount, or company name)?  

thank you,
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Microsoft has the tablediff command line utility.
0
Rey Obrero (Capricorn1)Commented:
there is no canned function in access that will do that.

you need to create queries to compare the tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
AFAIK not in Access.
Many threads were added,  in access zone, regarding the comparison of the contents of two tables. Those attempts did not include deleted data.

Search this site for "compare two tables in access".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Here is a utility that will do the compare for you.

http://www.fmsinc.com/MicrosoftAccess/DatabaseCompare.html

For $199, it's a good deal.  Your employer/client will probably pay for it since it would be significantly cheaper than having you develop something yourself.  I haven't used this particular product but I have other FMS products that I use regularly.
0
pdvsaProject financeAuthor Commented:
Russell, I don't know about the table diff  and it seems from other responses that there is nothing canned inside of access.   Let me know if I do jot understand... Thank you
0
Gustav BrockCIOCommented:
Russel is right - just upgrade to Access 2013:
DatabaseCompare.png/gustav
0
pdvsaProject financeAuthor Commented:
Thank you for the response.  What I found about DB compare: " Database Compare doesn't look for changes in the data stored in tables, but by exporting tables or query results to Excel workbooks, you can compare those two workbooks in Spreadsheet Compare. Learn how to Export Access data to see record updates."   Found here:
http://office.microsoft.com/en-us/support/basic-tasks-in-database-compare-HA102834118.aspx?CTT=5&origin=HA103516111

I am not sure if the other experts were aware of database compare since it's a new feature in 2013 but it wouldn't surprise me if they did and didn't mention Db compare because from the above, it doesn't seem like its a canned function inside of access and doesn't compare table data differences but excel. 2013 does and the data must be exported to excel.  

From the limited info in know about DB compare, to me it doesn't seem to compare data within tables and report the differences.

I am trying to be fair.   Let me know if what I have said sounds accurate.  Thank you.
0
Rey Obrero (Capricorn1)Commented:
@pdvsa
you are very right.
0
PatHartmanCommented:
Gus,
I have Office 2013 and I don't see the database compare.  Where is that picture from?
0
Gustav BrockCIOCommented:
The start screen. Perhaps you can choose not to install it? I chose full install.

/gustav
0
pdvsaProject financeAuthor Commented:
I think Rey has the answer then.   if any objections let me know.  I appreciate the other very helpful comments though but my question was directed at a canned function in Access.  Again I dont want to be unfair.
0
PatHartmanCommented:
Gus, Looks like you're showing a picture of Win 8.  We have 7.  We have O365 and I don't see that tool as an option anywhere.  It seems to be available for Office Professional Plus 2013 or Office 365 for enterprises (which is what we have).  It also doesn't compare data.  It only compares objects --
You can use Database Compare to compare two Access 2013 databases (or two versions of the same Access 2013 database) and see differences in structure between the databases. Database Compare does not track changes to the data. You can use Database Compare to identify structural changes, additions, deletions, renamed objects, and potential issues in the databases.
http://technet.microsoft.com/en-us/library/dn205149(v=office.15).aspx
0
Gustav BrockCIOCommented:
OK. Thanks. It makes sense.

/gustav
0
Russell FoxDatabase DeveloperCommented:
No, you're right, my response was geared towards SQL Server. Apologies!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.