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
CopyAccessDataToOracle() 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 strReturnedErrorMessage As String
Me.txtUserName.SetFocus
user = Me.txtUserName.Text
Me.txtUserPassword.SetFocus
pass = Me.txtUserPassword.Text
Me.txtServerName.SetFocus
server = Me.txtServerName.Text
Me.cmdExport.SetFocus
If Not CopyAccessDataToOracle(server, user, pass,
strReturnedErrorMessage) Then
MsgBox "Failed to copy data to Oracle." & vbCrLf &
strReturnedErrorMessage, 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 CopyAccessDataToOracle(ByVal sServer As String, ByVal
sUser As String, ByVal sPassWord As String, ByRef strErrorMessage As
String) As Boolean
On Error GoTo CopyAccessDataToOracle_Error
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim i As Integer
Dim strReturnedError 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) = "tblKeywordID"
tblNames(2) = "tblSection3"
tblNames(3) = "tblSection4"
tblNames(4) = "tblSection5"
tblNames(5) = "tblSection7"
tblNames(6) = "tblSection2"
tblNames(7) = "tblSection8"
tblNames(8) = "tblSection6"
tblNames(9) = "luFileFormat"
tblNames(10) = "tblSection9"
tblNames(11) = "tblSection10"
tblNames(12) = "tblSection1"
tblNames(13) = "luRegionType"
tblNames(14) = "luDataType"
tblNames(15) = "tblMetadataInfo"
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDAORA.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=MSDAORA.1;Data Source=" & sServer &
";User ID=" & sUser & "; Password=" & sPassWord & ";"
CopyAccessDataToOracle = False
Exit Function
End If
'step through the tables and delete all data in oracle
For i = 0 To 15
strReturnedError = ""
If Not DeleteDataFromOracleTable(tblNames(i), conn,
strReturnedError) Then
MsgBox strReturnedError, 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.ActiveConnection = 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
strReturnedError = ""
Debug.Print "Exporting table " & tblNames(i) & " to Oracle."
If Not ExportDataToOracleTable(tblNames(i), conn,
strReturnedError) Then
MsgBox strReturnedError, 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
CopyAccessDataToOracle_Exit:
CopyAccessDataToOracle = True
strErrorMessage = ""
GoTo CopyAccessDataToOracle_Cleanup
CopyAccessDataToOracle_Cleanup:
If Not cmd Is Nothing Then Set cmd = Nothing
If Not conn Is Nothing Then Set conn = Nothing
Exit Function
CopyAccessDataToOracle_Error:
strErrorMessage = Err.Description
CopyAccessDataToOracle = False
Err.Clear
GoTo CopyAccessDataToOracle_Cleanup
End Function
'Deletes all data in table strTableName
'in the database connected to by conn
Private Function DeleteDataFromOracleTable(ByVal strTableName As
String, conn As ADODB.Connection, ByRef strErrorMessage) As Boolean
On Error GoTo DeleteDataFromOracleTable_Error
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM " & UCase(strTableName)
cmd.Execute
DeleteDataFromOracleTable_Exit:
DeleteDataFromOracleTable = True
strErrorMessage = ""
GoTo DeleteDataFromOracleTable_Cleanup
DeleteDataFromOracleTable_Cleanup:
If Not cmd Is Nothing Then Set cmd = Nothing
Exit Function
DeleteDataFromOracleTable_Error:
DeleteDataFromOracleTable = False
strErrorMessage = "Error occured deleting data from table " &
strTableName & vbCrLf & Err.Description
Err.Clear
GoTo DeleteDataFromOracleTable_Cleanup
End Function
'Push the data in table strTableName into the same table (including
column names) on connection conn
Private Function ExportDataToOracleTable(ByVal strTableName As String,
conn As ADODB.Connection, ByRef strErrorMessage) As Boolean
On Error GoTo ExportDataToOracleTable_Error
Dim cmd As ADODB.Command
Dim strTableInsert As String
Dim i As Integer
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
Set tableRs = CurrentDb.OpenRecordset("select * from " &
strTableName)
tableRs.MoveFirst
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(tableRs.Fields(i).Value)) 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.Fields(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 =
GetInsertStatementColumnListFromTable(strTableName ) & strTableInsert &
")"
'Print the final Insert Statement
'Debug.Print strTableInsert
cmd.CommandText = strTableInsert
cmd.Execute
tableRs.MoveNext
Wend
ExportDataToOracleTable_Exit:
ExportDataToOracleTable = True
strErrorMessage = ""
GoTo ExportDataToOracleTable_Cleanup
ExportDataToOracleTable_Cleanup:
If Not cmd Is Nothing Then Set cmd = Nothing
If Not tableRs Is Nothing Then Set tableRs = Nothing
Exit Function
ExportDataToOracleTable_Error:
ExportDataToOracleTable = False
strErrorMessage = Err.Description
Err.Clear
GoTo ExportDataToOracleTable_Cleanup
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 GetInsertStatementColumnListFromTable(ByVal
strTableName As String) As String
Dim i As Integer
Dim strColList As String
strColList = "INSERT INTO " &
UCase(CurrentDb.TableDefs(strTableName).Name) & " ("
For i = 0 To CurrentDb.TableDefs(strTableName).Fields.Count - 1
strColList = strColList & """" &
CurrentDb.TableDefs(strTableName).Fields(i).Name & """"
If i <> CurrentDb.TableDefs(strTableName).Fields.Count - 1
Then
strColList = strColList & ", "
End If
Next i
strColList = strColList & ") Values "
GetInsertStatementColumnListFromTable = strColList
End Function
