Export Oracle user tables to MS Access automaticaly

Question

Which can be the easy way to export some user tables (DDL and data) from an Oracle database to a Microsoft Access database Automatically, it means without user interaction.

UPDATE: Linked tables on Access database raises the user/password dialog to connect oracle which is not a valid option.

Answer 1

You could set up linked tables in MS Access which 'point' to the actual Oracle data rather than copying it. That way the data is always up to date.

Otherwise, you will need to create a scheduled process, or maybe perform the import via VBA code each time the MS Access database is opened.

Personnally, I'd go with the linked tables.

Answer 2

Warning

Some notes about the code below:

1. It doesn't check if the table is already linked first. If it is, you'll get an error you'll have to handle.

2. The user name and password are passed as clear text, which is a security risk. Someone can open up your database code and get all the info they need to connect to your database and potentially do some harm. If this matters to you, you'll need to either compile your Access database to an MDE and distribute that, or find some way of encrypting/decrypting the connect info on the fly.

Answer

You can use ADOX to link the tables without prompting for user ID and password. First, set a reference to the "Microsoft ADO Ext. 2.8 for DDL and Security" libary (Tools\References... in the VBA editor). If you don't see this entry in the list of available references, browse for "msadox.dll" and add it manually. Your version number may be different, depending on your version of Access (I'm using 2003 here).

You can then use code like this to link to an Oracle table:

Sub LinkOracleTable(Server, UserID, Password, Schema, TableName)

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection

With tbl
    .Name = TableName

    Set .ParentCatalog = cat
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Link Provider String") = "ODBC;" & _
        "Driver={Microsoft ODBC For Oracle};" & _
        "Server=" & Server & ";" & _
        "Uid=" & UserID & ";" & _
        "Pwd=" & Password & ";"
    .Properties("Jet OLEDB:Cache Link Name/Password") = True
    .Properties("Jet OLEDB:Remote Table Name") = Schema & "." & TableName
End With

cat.Tables.Append tbl
cat.ActiveConnection.Close

End Sub

You would call the sub like this:

LinkOracleTable "TNS Service Name", "UserName", "Password", "MySchema", "MyTable"

Answer 3

Oracle can write to flat files using UTL_FILE. You then need to get them to a location where the Access Database can upload them.

Oracle Standard and Enterprise Edition include a JVM, so you could have Java code there which connects to Access through JDBC and pushes the data that way.

Also look up Heterogeneous connectivity and database links http://www.oracle-base.com/articles/9i/HSGenericConnectivity9i.php That may only be an option if your Oracle DB is on a server that can cope with ODBC.

Finally, following on the previous comments, you could push the data into either a separate schema or even a separate Oracle database (eg the free Oracle express edition) and have the Access database/application pick it up from there.