Programmatically compare a SQL DB schema to Access Database Schema

I have an application that transfers data between an access database and a SQL server database. I am trying to copy the data from tables that I think are exactly alike with the same tables and same fields. I am having to add new fields at times and this is where the problem is. Some of the users access database is out of sync with the SQL database.

I have tried cycling through all of the definitions on each and before I make the attempt to transfer, I take the name of the field and display it on a form. But I am getting a value that crashes my program. I am looking for a way to programmatically compare an Access table to the SQL table inside of my SQL server...

Thanks in advance for any ideas.
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.

Shanmuga SundaramDirector of Software EngineeringCommented:
The attached zip file will import data from access to sql. please check and let me know your comments.Please take a backup of your data before you use.
Shanmuga SundaramDirector of Software EngineeringCommented:
The file is attached
ec462Author Commented:
I actually do something a bit simpler. I establish a connection to the Access and SQL Server. I know my table names, so I a routine that looks like this:

For j = 1 To 18

    Select Case j
     Case Is = 1
        wrktable = "Table1"
     Case Is = 2
        wrktable = "Table2"
     Case Is = 3
        wrktable = "Table3"
     Case Is = 4
        wrktable = "Table4"
     Case Is = 5
        wrktable = "Table5"
     Case Is = 6
        wrktable = "Table6"
     Case Is = 7
        wrktable = "Table7"
     Case Is = 8
        wrktable = "Table8"
     Case Is = 9
        wrktable = "Table9"
     Case Is = 10
        wrktable = "Table10"
     Case Is = 11
        wrktable = "Table11"
     Case Is = 12
        wrktable = "Table12"
     Case Is = 13
        wrktable = "Table13"
     Case Is = 14
        wrktable = "Table14"
     Case Is = 15
        wrktable = "Table15"
     Case Is = 16
        wrktable = "Table16"
     Case Is = 17
        wrktable = "Table17"
     Case Is = 18
        wrktable = "Table18"
    End Select

    If adoTable1.state = 1 Then adoTable1.Close
    If adoTable2.state = 1 Then adoTable2.Close
    'Select the case related data from the Local Archive
    rdquery = "select * from " & wrktable & " where mainkey = '" & MDIAudit.StatusBar.Panels.Item(4).Text & "'"
    adoTable1.Open rdquery, adocn, , , adCmdText
    'Drop all case related data from the Destination Database
    If LoginIndicator = 3 Then
      rdquery = "delete  " & wrktable & " where mainkey = '" & MDIAudit.StatusBar.Panels.Item(4).Text & "'"
        rdquery = "delete * from " & wrktable & " where mainkey = '" & MDIAudit.StatusBar.Panels.Item(4).Text & "'"
    End If
    AdoConn.adoparish.Execute rdquery
    'Start on a blank record to add
    rdquery = "select * from " & wrktable & " where transid is null"
    adoTable2.Open rdquery, AdoConn.adoparish, adOpenForwardOnly, adLockOptimistic, adCmdText

    frmStatus.Cancelled = False
    frmStatus.DoCancel = True
    B = 0
    'Check all of this logic
    While Not adoTable1.EOF
        'Add a destination database record
        Set tblDef2 = adoTable2.Fields
        Set tblDef1 = adoTable1.Fields
        With tblDef1
           For i = 0 To .Count - 1
             If UCase(adoTable1.Fields(i).Name) <> "TRANSID" Then
               'Grab the data from transfer from field adotable1 and put it in adotable2
                adoTable2.Fields(adoTable1.Fields(i).Name).Value = adoTable1.Fields(i).Value
             End If
           Next i
        End With
Next j

This works, but the problem is when I have an access database that is out of date and doesn't have the latest fields in it. I need a way to compare them and output the fields that are not in the SQL server, which will be the current Schema.

Any ideas on that?

I did look at your code. It was real nice and very generalized. Which is really handy.

Thanks for that submission.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

To ec462:

Why select ...  instead of wrktable = "Table" + Format(13, "##") ? ;)

Sorry: wrktable = "Table" + Format(j, "##") ? ;)
ec462Author Commented:
That is just a display item I am wrting to the screen of the current table and its number in which it has been processed.
For j = 1 To 18
    Select Case j
     Case Is = 1
        wrktable = "Table1"
     Case Is = 2
        wrktable = "Table2"
  End Select

not equal to

For j = 1 To 18
  wrktable = "Table" + Format(j, "##")

ec462Author Commented:
Thanks for your input. I got it figured out. If you use Excel automation, you can dump both tables their and when it bombs out then you can see where the mismatch occurred.

Thanks for you submission.

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
ec462Author Commented:
I was able to solve the problem. The contributor did help.
Anthony PerkinsCommented:
>>The contributor did help. <<
Then why don't you do the right thing and award points to whoever help you.
ec462Author Commented:
Thanks for youjr input.
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

From novice to tech pro — start learning today.