CODEWITHSUNDEEP
sql-serverms-access
Kram_Koorbse
Kram_Koorbse

Reputation: 492

how to compare sql server data with MS Access data

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?

Upvotes: 1

Views: 778

Answers (2)

Farid Z
Farid Z

Reputation: 1018

Disclaimer: I am the developer of the CompareData app.

You can use ODBC-based CompareData app. The app is free for comparing data.

You create two ODBC data sources to connect to the databases and then you can compare the data of any two tables.

Upvotes: 0

PowerUser
PowerUser

Reputation: 11801

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!

Upvotes: 2

Related Questions