how to compare sql server data with MS Access data

Question

Working on a data data accuracy project. I have to find a way to compare data from a query from a SQL Server db with the data from a query from a MS Access db. The data on both db's should be identical, but sometimes there are errors. I have looked at data comparison tools but these seem to only be able to compare data from identical db vendors.

Is there a process that someone has used in the past to do this or an idea on how I might best approach this?

Answer 1

You can look at both data sets in Access, SQL, or Excel:

• If the data set is small enough, I recommend Excel.
• If you know SQL, you can export your Access data to text files, then do a Bulk Insert and get everything into SQL Server.

• If you want to look at both data sets in Access, try this:

  1. Go to your ODBC Data Source Administrator (searching for 'ODBC' from your Start menu should be sufficient)
  2. Create a new System DSN connecting to your SQL Server db
  3. Open your Access db (I'm using 2010, your version may be different)
  4. Go to External Data->ODBC Database->Machine Data Source
  5. Link to your tables of choice from your SQL Server
  6. Query away!