Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

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,
0
pdvsa
Asked:
pdvsa
  • 3
  • 3
  • 3
  • +3
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now