I scoured the groups for some hands on code to perform the menial task
of exporting table data from an Access 2000 database to Oracle
database (in this case, it was oracle 8i but i'm assuming this will
work for 9i and even 10g )
No one had what I needed, so I wrote it myself.
I Rule.
This code isn't going for efficiency, and isn't trying to be dynamic.
It doesn't create the table structure in Oracle, that's up to you. (I
had to do it myself by importing the table into a new SQL Server
database, then generating SQLServer script for the table structure,
and editing the script so it was Oracle compliant)
The structure is assumed to exist in Oracle already, with the table
names and field names IDENTICAL to Access.
Also, a note that varchar2 fields in oracle have a maximum of 4000
characters, so any TEXT files in Access that are longer than 4000
character will be truncated by my code.
I created a new form in Access, with 3 text boxes (server, user,
password) and a button (named cmdExport).
Place this code into the form's code, and the cmdExport_Click method
is there.
watch out for line-wrapping, this is a newsgroup post.
the only trick to this code is the tblNames() string array in
CopyAccessDataT oOracle() function. This array needs the names of your
tables, in the order they need to be truncated (they are then
populated in reverse of that order). Read the comments in the code,
and pay attention.
Email me if you have any questions or suggestions.
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~
Option Compare Database
Private Sub cmdExport_Click ()
Dim user As String
Dim pass As String
Dim server As String
Dim strReturnedErro rMessage As String
Me.txtUserName. SetFocus
user = Me.txtUserName. Text
Me.txtUserPassw ord.SetFocus
pass = Me.txtUserPassw ord.Text
Me.txtServerNam e.SetFocus
server = Me.txtServerNam e.Text
Me.cmdExport.Se tFocus
If Not CopyAccessDataT oOracle(server, user, pass,
strReturnedErro rMessage) Then
MsgBox "Failed to copy data to Oracle." & vbCrLf &
strReturnedErro rMessage, vbCritical, "Error Exporting"
Else
Debug.Print "Exported all tables successfully"
End If
End Sub
'Entry point to copying
'connect to oracle server using user/pass
'delete all data in oracle tables listed in tblNames variable
'insert all data from access tables with same names into oracle tables
'note order in tables has to respect any constraints in oracle
Private Function CopyAccessDataT oOracle(ByVal sServer As String, ByVal
sUser As String, ByVal sPassWord As String, ByRef strErrorMessage As
String) As Boolean
On Error GoTo CopyAccessDataT oOracle_Error
Dim conn As ADODB.Connectio n
Dim cmd As ADODB.Command
Dim i As Integer
Dim strReturnedErro r As String
Dim tblNames(0 To 15) As String
'tables are in the drop order
'from 0 to 15, the data must be deleted
'then, data needs to be populated from 15 to 0
'** This order needs to respect all foreign key constraints **
tblNames(0) = "tbkwbridge "
tblNames(1) = "tblKeyword ID"
tblNames(2) = "tblSection 3"
tblNames(3) = "tblSection 4"
tblNames(4) = "tblSection 5"
tblNames(5) = "tblSection 7"
tblNames(6) = "tblSection 2"
tblNames(7) = "tblSection 8"
tblNames(8) = "tblSection 6"
tblNames(9) = "luFileForm at"
tblNames(10) = "tblSection 9"
tblNames(11) = "tblSection 10"
tblNames(12) = "tblSection 1"
tblNames(13) = "luRegionTy pe"
tblNames(14) = "luDataType "
tblNames(15) = "tblMetadataInf o"
Set conn = New ADODB.Connectio n
conn.Connection String = "Provider=MSDAO RA.1;Data Source=" &
sServer & ";User ID=" & sUser & "; Password=" & sPassWord & ";"
conn.Open
If conn.STATE <> 1 Then
strErrorMessage = "Failed to connect to database using
connection string: " & "Provider=MSDAO RA.1;Data Source=" & sServer &
";User ID=" & sUser & "; Password=" & sPassWord & ";"
CopyAccessDataT oOracle = False
Exit Function
End If
'step through the tables and delete all data in oracle
For i = 0 To 15
strReturnedErro r = ""
If Not DeleteDataFromO racleTable(tblN ames(i), conn,
strReturnedErro r) Then
MsgBox strReturnedErro r, vbCritical, "Error Deleting Data
In " & tblNames(i)
End If
Debug.Print "Deleted data in table " & tblNames(i)
Next i
'Not sure if this is needed
Set cmd = New ADODB.Command
cmd.ActiveConne ction = conn
cmd.CommandText = "commit"
cmd.Execute
DoEvents
'step through the tables and insert data from the access tables
into oracle
For i = 15 To 0 Step -1
strReturnedErro r = ""
Debug.Print "Exporting table " & tblNames(i) & " to Oracle."
If Not ExportDataToOra cleTable(tblNam es(i), conn,
strReturnedErro r) Then
MsgBox strReturnedErro r, vbCritical, "Error Copying Into "
& tblNames(i)
End If
Debug.Print "Exported."
Next i
'Not sure if this is needed
cmd.CommandText = "commit"
cmd.Execute
DoEvents
conn.Close
CopyAccessDataT oOracle_Exit:
CopyAccessDataT oOracle = True
strErrorMessage = ""
GoTo CopyAccessDataT oOracle_Cleanup
CopyAccessDataT oOracle_Cleanup :
If Not cmd Is Nothing Then Set cmd = Nothing
If Not conn Is Nothing Then Set conn = Nothing
Exit Function
CopyAccessDataT oOracle_Error:
strErrorMessage = Err.Description
CopyAccessDataT oOracle = False
Err.Clear
GoTo CopyAccessDataT oOracle_Cleanup
End Function
'Deletes all data in table strTableName
'in the database connected to by conn
Private Function DeleteDataFromO racleTable(ByVa l strTableName As
String, conn As ADODB.Connectio n, ByRef strErrorMessage ) As Boolean
On Error GoTo DeleteDataFromO racleTable_Erro r
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConne ction = conn
cmd.CommandText = "DELETE FROM " & UCase(strTableN ame)
cmd.Execute
DeleteDataFromO racleTable_Exit :
DeleteDataFromO racleTable = True
strErrorMessage = ""
GoTo DeleteDataFromO racleTable_Clea nup
DeleteDataFromO racleTable_Clea nup:
If Not cmd Is Nothing Then Set cmd = Nothing
Exit Function
DeleteDataFromO racleTable_Erro r:
DeleteDataFromO racleTable = False
strErrorMessage = "Error occured deleting data from table " &
strTableName & vbCrLf & Err.Description
Err.Clear
GoTo DeleteDataFromO racleTable_Clea nup
End Function
'Push the data in table strTableName into the same table (including
column names) on connection conn
Private Function ExportDataToOra cleTable(ByVal strTableName As String,
conn As ADODB.Connectio n, ByRef strErrorMessage ) As Boolean
On Error GoTo ExportDataToOra cleTable_Error
Dim cmd As ADODB.Command
Dim strTableInsert As String
Dim i As Integer
Set cmd = New ADODB.Command
cmd.ActiveConne ction = conn
Set tableRs = CurrentDb.OpenR ecordset("selec t * from " &
strTableName)
tableRs.MoveFir st
While Not tableRs.EOF
strTableInsert = "("
For i = 0 To tableRs.Fields. Count - 1
'Originally, i had a select case on the
tableRs.Fields( i).Type
'but Access returns strange types for some columns (i.e.
varchar for date fields)
'and this works.
If IsNull(tableRs. Fields(i).Value ) Then
strTableInsert = strTableInsert & "Null"
Else
If (IsNumeric(tabl eRs.Fields(i).V alue)) Then
strTableInsert = strTableInsert &
tableRs.Fields( i).Value
Else
If IsDate(tableRs. Fields(i).Value ) Then
'use CDate() to take care of situations
involoving "August 1998" which VB reports as
'a date, but Oracle can not to_date() that
format
strTableInsert = strTableInsert & "to_date('"
& CDate(tableRs.F ields(i).Value) & "', 'mm/dd/yyyy')"
Else
strTableInsert = strTableInsert & "'" &
MakeSafeVarchar (tableRs.Fields (i).Value) & "'"
End If
End If
End If
If i <> tableRs.Fields. Count - 1 Then
strTableInsert = strTableInsert & ", "
End If
Next i
DoEvents
strTableInsert =
GetInsertStatem entColumnListFr omTable(strTabl eName) & strTableInsert &
")"
'Print the final Insert Statement
'Debug.Print strTableInsert
cmd.CommandText = strTableInsert
cmd.Execute
tableRs.MoveNex t
Wend
ExportDataToOra cleTable_Exit:
ExportDataToOra cleTable = True
strErrorMessage = ""
GoTo ExportDataToOra cleTable_Cleanu p
ExportDataToOra cleTable_Cleanu p:
If Not cmd Is Nothing Then Set cmd = Nothing
If Not tableRs Is Nothing Then Set tableRs = Nothing
Exit Function
ExportDataToOra cleTable_Error:
ExportDataToOra cleTable = False
strErrorMessage = Err.Description
Err.Clear
GoTo ExportDataToOra cleTable_Cleanu p
End Function
'Makes the varchar safe to use in an Insert statement
'replaces single quotes ' with escape character ''
Private Function MakeSafeVarchar (ByVal strStringIn As String) As
String
Dim i As Integer
Dim swap(1, 2) As String
Dim strOut As String
strOut = strStringIn
swap(0, 0) = "'"
swap(0, 1) = "''"
For i = 0 To UBound(swap)
strOut = Replace(strOut, swap(i, 0), swap(i, 1))
Next i
'If, for some reason, the varchar goes beyond 4000 characters
(which is the maximum for Oracle)
'truncate.
'This isn't the best solution, but the Oracle database gets much
more complex if the CLOB
'data type is needed to replace for extra-long varchar fields
MakeSafeVarchar = Left(strOut, 4000)
End Function
'Constructs an insert statement for a table definintion
'this assumes that oracle has the exact same table and field names as
access
'note, double quotes " are used to wrap column names, because access
can have spaces
'and strange characters in the column names
Private Function GetInsertStatem entColumnListFr omTable(ByVal
strTableName As String) As String
Dim i As Integer
Dim strColList As String
strColList = "INSERT INTO " &
UCase(CurrentDb .TableDefs(strT ableName).Name) & " ("
For i = 0 To CurrentDb.Table Defs(strTableNa me).Fields.Coun t - 1
strColList = strColList & """" &
CurrentDb.Table Defs(strTableNa me).Fields(i).N ame & """"
If i <> CurrentDb.Table Defs(strTableNa me).Fields.Coun t - 1
Then
strColList = strColList & ", "
End If
Next i
strColList = strColList & ") Values "
GetInsertStatem entColumnListFr omTable = strColList
End Function
