• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This code allows you to take an Oracle Table structure and contents into Access. This example only

This code allows you to take an Oracle Table structure and contents into Access. This example only

This code allows you to take an Oracle Table structure and contents into Access. This example only allows for numeric and varchar data types.

Rate This code allows you to take an Oracle Table structure and contents into Access. This example only

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Dim adoAccess As ADODB.Connection
Dim rstAccess As ADODB.Recordset

Dim adoOracle As ADODB.Connection
Dim rstOracle As ADODB.Recordset

Dim fldArray As Variant
Dim tblName(2) As String
Dim strSQL As String
Dim strAlter As String
Dim fld As ADODB.Field
Dim rowNumber As Integer
Dim fldNumber As Integer

Set adoAccess = New ADODB.Connection
Set rstAccess = New ADODB.Recordset
rstAccess.CursorLocation = adUseClient

Set adoOracle = New ADODB.Connection
Set rstOracle = New ADODB.Recordset
rstOracle.CursorLocation = adUseClient

tblName(0) = "TEST_TABLE1"
tblName(1) = "TEST_TABLE2"


'the access database must already be created with no tables
adoAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                             "Data Source=C:\Program Files\Microsoft " & _
                             "Visual Studio\VB98\testmdb.mdb"
adoAccess.Open

'an oracle db must exist with the table names above.
adoOracle.ConnectionString = "Provider=MSDAORA.1;Data Source=your_source;" & _
                             "User ID=my_id;Password=mypass"
adoOracle.Open

For a = 0 To UBound(tblName) - 1
    'create the access table with no columns.
    adoAccess.Execute "create table " & tblName(a)
    'read the oracle table column information
    rstOracle.Open tblName(a), adoOracle, , , adCmdTableDirect
    MsgBox tblName(a)
    For Each fld In rstOracle.Fields
        Select Case fld.Type
            'only handle varchar and numeric data types.  If others are needed,
            'add them later.
            Case adNumeric
                'alter the table to add a numeric column
                strAlter = "alter table " & tblName(a) & " add column " & _
                            fld.Name & " NUMBER"
                adoAccess.Execute strAlter
            Case adVarChar
                'alter the table to add a text column, length of the definedsize
                strAlter = "alter table " & tblName(a) & " add column " & _
                            fld.Name & " TEXT(" & fld.DefinedSize & ")"
                adoAccess.Execute strAlter
        End Select
    Next fld
    'populate the information from oracle into access
    rstOracle.Close
    'select all records and put them into an array
    strSQL = "select * from " & tblName(a)
    rstOracle.Open strSQL, adoOracle
    fldArray = rstOracle.GetRows
    'open Access table     
    rstAccess.Open tblName(a), adoAccess, adOpenKeyset, _
                   adLockOptimistic, adCmdTable
    For rowNumber = 0 To UBound(fldArray, 2)
        'addnew method is used
        rstAccess.AddNew
        For fldNumber = 0 To rstAccess.Fields.Count - 1
            'loop through the fields and set the value of the field
            'according to the fldnumber and rownumber of the array
            rstAccess(fldNumber) = fldArray(fldNumber, rowNumber)
        Next fldNumber
        rstAccess.Update
    Next rowNumber
rstAccess.Close
rstOracle.Close

Next a

End Sub