Exporting/Importing

[gwt]

I've searched the world over for a control or already code
that will export tables from Access to SQL server or vice-versa. I haven't found anything. I can write the code to do it, but I just don't have the time right now. Does anyone have any information on where I can obtain some code that will do this exporting or importing of tables from db to db?

Thanks

[Clunietp]

here's some code I whipped up a while ago. Just supply the connection strings and it does the rest

'uses ADO 2.x

Public Sub CopyData(TableName As String, SourceConnectionString As String, DestinationConnectionString As String, DeleteExistingData As Boolean)
    
    On Error GoTo Err_Handler
    
    Dim cnSource As ADODB.Connection
    Dim cnDestination As ADODB.Connection
    Dim rsSource As ADODB.Recordset
    
    Dim strSQL As String
    Dim lngCounter As Long
    
    Set cnSource = New Connection
    Set cnDestination = New Connection
    
    'open connections
    cnSource.Open SourceConnectionString
    cnDestination.Open DestinationConnectionString
    
    'start transaction for destination in case of error
    cnDestination.BeginTrans
    
    'get source data
    Set rsSource = cnSource.Execute("Select * from [" & TableName & "]", , adCmdText)
    
    'delete data if necessary
    If DeleteExistingData = True Then
        cnDestination.Execute "Delete from [" & TableName & "]"
    End If
    
    Do Until rsSource.EOF = True
        'prepare SQL statement
        '==============================================
        strSQL = "Insert into [" & TableName & "] ("
        
        'get fields
        For lngCounter = 0 To rsSource.Fields.Count - 1
            strSQL = strSQL & "[" & rsSource.Fields(lngCounter).Name & "], "
        Next lngCounter
        
        'remove last comma & space
        strSQL = Left(strSQL, Len(strSQL) - 2)
        
        strSQL = strSQL & ") VALUES ("
        
        'get data
        For lngCounter = 0 To rsSource.Fields.Count - 1
            strSQL = strSQL & "'" & Replace(rsSource.Fields(lngCounter).Value & "", "'", "''") & "', "
        Next lngCounter
        
        'remove last comma & space
        strSQL = Left(strSQL, Len(strSQL) - 2)
        
        'add closing parenthesis
        strSQL = strSQL & ")"
        
        'update destination table
        cnDestination.Execute strSQL, , adCmdText
        
        rsSource.MoveNext
        DoEvents
    Loop
    'commit transaction
    cnDestination.CommitTrans
    Exit Sub
    
Err_Handler:
    cnDestination.RollbackTrans
    Err.Raise Err.Number, Err.Source, Err.Description
End Sub