Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: DB Convert from Access

    I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.

    The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.

    Here is the basic code:

    The code that causes everything to error is commented out.

    Code:
    Private Sub CopySQLRecordSet(ByVal stTableName As String, _
                                 Optional ByVal blIdentity As Boolean = True, _
                                 Optional ByVal stSearch As String = "")
        Dim cat As ADOX.Catalog
        Dim tbl As ADOX.Table
        Set cat = New ADOX.Catalog
        Set tbl = New ADOX.Table
        Dim adoCommand As New ADODB.Command, stCommand As String
        Dim adoSQLCommand As New ADODB.Command
        Dim errorString As String
        
        On Error GoTo ErrorCopying
        PrgPart.Value = PrgPart.Value + 1
        If blCancelPressed Then End '???fix later
        cat.ActiveConnection = db1  'This doesn't seem to work with our normal settings for spectrumDbase
        tbl.ParentCatalog = cat
        tbl.Name = "dbo_" & stTableName
        tbl.Properties("Temporary Table") = False
        'possibly, this line will work for Oracle as well
        tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN=
    ;DATABASE=database;"
        tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName
        tbl.Properties("Jet OLEDB:Create Link") = True
        tbl.Properties("Jet OLEDB:Table Hidden In Access") = False
        tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False
        cat.Tables.Append tbl
        Suspend 1
        adoCommand.CommandType = adCmdText
        adoCommand.ActiveConnection = database
        adoSQLCommand.CommandType = adCmdText
        adoSQLCommand.ActiveConnection = rsDbase
       
        'spectrumDBase.BeginTrans
        'stCommand = "BEGIN TRANSACTION " & vbNewLine _
        '            & "go" & vbNewLine
        'If there is an identity field in the table, it must be temporarily disabled
        'to insert from an foreign DB.
        'If blIdentity Then
         '   stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine
            'adoCommand.CommandText = stCommand
            'adoCommand.Execute
        'End If
        
        'insert the records from the source table
        stCommand = ""
        stCommand = stCommand & "INSERT INTO dbo_" & stTableName _
                  & " SELECT * FROM " & stTableName & vbNewLine _
                  & "GO" & vbNewLine
        
        'stCommand = stCommand & "COMMIT TRANSACTION"
                    
        adoCommand.CommandText = stCommand
        adoCommand.Execute
    
        'clean up
    '    stCommand = stCommand & vbNewLine & "DROP TABLE dbo_" & stTableName & vbNewLine & "GO"
    '    adoCommand.CommandText = stCommand
    '    adoCommand.Execute
        
        'reinsert identity property
    '    If blIdentity Then stCommand = "SET IDENTITY_INSERT " & stTableName & " OFF" & vbNewLine
        
        'Block statement, going to leave this out for now
        'stCommand = stCommand & "go" & vbNewLine _
                  '& "COMMIT TRANSACTION"
    
        'adoCommand.CommandText = stCommand
        'adoCommand.Execute
        
        Set adoCommand = Nothing
        Set adoSQLCommand = Nothing
        Exit Sub
    ErrorCopying:
        'need to save this to a string so it doesn't reset when Resume occurs
        errorString = Err.Description
    '    MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _
    '         & "Error: " & errorstring
        Resume errorCatch
    errorCatch:
        On Error Resume Next
        'set a log
        Dim fso As FileSystemObject, fStream As TextStream
        Set fso = New FileSystemObject
        Set fStream = fso.OpenTextFile(App.Path & "\DBTransfer.log", ForAppending, True)
        fStream.WriteLine "***Error copying the [" & stTableName & "] table."
        fStream.WriteLine "   Error: " & errorString
        fStream.WriteLine " "
        fStream.Close
        Set fStream = Nothing
        Set fso = Nothing
        'clean up
        adoCommand.CommandText = "DROP TABLE dbo_" & stTableName
        adoCommand.Execute
        If blIdentity Then
            'reinsert identity property
            adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF"
            adoSQLCommand.Execute
        End If
        Set adoCommand = Nothing
        Set adoSQLCommand = Nothing
    End Sub

  2. #2
    Join Date
    Feb 2005
    Posts
    5
    No one has any ideas?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yes. I always have ideas. most of them bad.

    you are doing too much work here. Use Data Transfrmation Services (DTS) to pull the data from Access instead of trying to push the data to SQL Server. Import all of the columns let DTS create the tables for you. Do not define any primary keys or identity columns in the process.

    Use the wizard. The whole thing will take no time at all and you won't have to write any code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    PS after the import then define your keys. stop using access for everything.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2005
    Posts
    5
    This is more for a program that we have that is used across the world. Using the wizard isn't an option. I would love to be able to do that if I could.

    We have support for Access, Oracle and SQL in the program and when our customers need to upsize, we need a way for them to convert the data. We currently have a program written to convert the data, but it is not efficient at all. It will not transfer the same identity keys over as well, but it will keep the relationships the same. We would like to keep everything identical.

    It seems the main problem is just getting SQL database to recognize the commands. This is what I have tried just now.

    SET IDENTITY_INSERT dbo_tablename ON; INSERT INTO dbo_tablename SELECT * FROM tablename; DROP TABLE dbo_tablename; SET IDENTITY_INSERT dbo_tablename OFF

    This is what I get

    [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    odbc isnt going to recognize a set identity_insert statement.
    LINK


    why cant you use dts? you can create packages that will work on any server and can accept all kinds of parameters to enable runtime ( dbname, servername tablename. etc) and as far as heterogeneous data goes, i have consulted at 3 diff sites where a SQL server was the prime ETL db for the entire data mgmt system (Oracle, DB2, VAX, Access, Excel, Flat file, etc...)
    you could also use the upsizing wizard in access to carry across your keys and autonumbers(identities) however i have had mixed results with the upsizing wizard and have found it nowhere as near as consistent as DTS.

  7. #7
    Join Date
    Feb 2005
    Posts
    5
    I'll see if I can convince everyone to use DTS. I'm not too sure if it will work. I have pretty much figured out by now that this set identity was not going to work. I'm still looking for a programming option though.

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    do your research HERE

  9. #9
    Join Date
    Feb 2005
    Posts
    5
    thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •