Copy Data from Access to Oracle or SQL Server

Initial Requirements

• The tables must first be built in Oracle or SQL Server (use the scripts).
• You need a copy of Microsoft Access (preferrably Access 2000)
• You need a fast network connection from the machine with Access to the machine with Oracle.
• Access and Oracle can be installed on the same machine (for Windows or Personal version of Oracle).
• Be sure Oracle network (and ODBC) is installed on the Access machine: Install Oracle Client.

Data transfer

• Create an ODBC link on the Access machine.
• Start - Settings - Control Panel - Administrative Tools - Data Sources (ODBC)
• Tab: System DSN
• Button: Add
• Select: Oracle ODBC (or SQL Server)
• Button: Finish
• Data Source Name: Rolling Thunder
• Service Name (your Oracle Service Name) or the name of the machine running SQL Server
• OK/Close - use default choices
• Link files into Access
• Start Access, open Rolling Thunder
• File - Get External Data - Link Files
• Files of type: ODBC Databases()
• Tab: Machine Data Source
• Select: Rolling Thunder (data source name you entered in ODBC)
• Log in to Oracle/SQL Server
• Select all of the RT tables you created (click each one).
• Button: OK
• Copy the data
• Look at the list of Tables in Access,
• The new links will be listed with an icon and the schema: e.g., RT_
• Access - Forms - Open - zzCopyDataToOracle
• Enter the schema prefix, e.g., RT (without the underscore)
• Do not check either of the two check boxes.
• Button: CopyData
• Wait for all data to be copied (could be quite a while.)