By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,731 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Export Access Data to Oracle

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
An**********@gmail.com (Andrew Arace) wrote in
news:26**************************@posting.google.c om:
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.


What I did was use the Oracle data exchange tool to make my Oracle schema
from the Access database. Then, go back into Access, link the Oracle
tables, and write some code kind of like this:

public sub LoadDataToOracle
dim db as dao.database
dim t as dao.tabledef
dim sql as string

set db = dao.currentdb

for each t in db.tabledefs
'notice how Oracle's tables are linked in via ODBC... <user>_tablename...
if t.connect = "" then 'it's a local table...
sql = "insert into <user>_" & _
t.name & " select * from " & t.name

DoCmd.runsql sql
end if
next t
end sub

At the time, the Oracle data pumper had problems loading all the data from
Access to Oracle.

This all works fine, assuming your Access table names aren't stupid (i.e.,
they have spaces in them and other non-SQLish characters in them.
Otherwise, you need to mangle the local tablenames in t.name to match the
Oracle table's equivalent.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.