'---------------------------------------------------------------------------------------
' Module    : Transfer Antar Database
' Author     : Tatang Sutarman
' Date        : 03/02/2010
' Purpose   :
'---------------------------------------------------------------------------------------
Option Explicit

Dim CN              As ADODB.Connection
Dim SQL            As String
Dim AccessApp   As String

Private Sub KonekDB()
On Error GoTo KonekDB_Error

    AccessApp = Trim(App.Path & "\Data.mdb")
    Set CN = New ADODB.Connection
    CN.Open "PROVIDER=MSDASQL;" & _
            "DRIVER={Microsoft Access Driver (*.mdb)};" & _
            "DBQ= " & AccessApp & ";" & _
            "UID=sa;PWD=;"

   On Error GoTo 0
   Exit Sub

KonekDB_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure KonekDB of Form Form1"
End Sub

Private Sub ADOSQL2ACCESS()
On Error GoTo ADOSQL2ACCESS_Error

    KonekDB
    
    SQL = "SELECT * INTO " & _
          "AccessTbl " & _
          "FROM " & _
          "[ODBC;Driver=SQL Server; " & _
          "SERVER=(local);DATABASE=MSP;" & _
          "UID=sa;PWD=;].[SQLTbl];"

    CN.Execute SQL
    Set CN = Nothing

   On Error GoTo 0
   Exit Sub

ADOSQL2ACCESS_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ADOSQL2ACCESS of Form Form1"
End Sub

Private Sub ADOACCESS2SQL()
On Error GoTo ADOACCESS2SQL_Error

    KonekDB

    SQL = "Select * INTO " & _
          "[ODBC;Driver=SQL Server;Server=(Local);Database=MSP;" & _
          "UID=sa;PWD=;].[SQLTbl] " & _
          "From AccessTbl"

    CN.Execute SQL
    CN.Close
    Set CN = Nothing

   On Error GoTo 0
   Exit Sub

ADOACCESS2SQL_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ADOACCESS2SQL of Form Form1"
End Sub


Private Sub Command1_Click()
    ADOACCESS2SQL
End Sub

Private Sub Command2_Click()
    ADOSQL2ACCESS
End Sub

Semoga Bermanfaat,

Admin