Link to home
Start Free TrialLog in
Avatar of ec462

asked on

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.
Avatar of Shanmuga Sundaram D
Shanmuga Sundaram D
Flag of India image

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.
Avatar of ec462


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.

To ec462:

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

Sorry: wrktable = "Table" + Format(j, "##") ? ;)
Avatar of ec462


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, "##")

Avatar of ec462

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ec462


I was able to solve the problem. The contributor did help.
>>The contributor did help. <<
Then why don't you do the right thing and award points to whoever help you.
Avatar of ec462


Thanks for youjr input.