by Robert Henson (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 8th January 2000
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
This code allows you to take an Oracle Table structure and contents into Access. This example only allows for numeric and varchar data types.
VBcoders
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
No comments have been posted about This code allows you to take an Oracle Table structure and contents into Access. This example only . Why not be the first to post a comment about This code allows you to take an Oracle Table structure and contents into Access. This example only .
