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