Copying Data From MS Access to DB2
I used Access 2000, but other versions should work.
- Create a database in DB2's Control Center. If those tables have
indexes, create them now. If you have SQL code for the indexes you may
want to use the 'Command Center'.
- Create a DSN using: Control Panel, ODBC, System DSN tab, then click
- Next, open Access database in MS Access.
- File, Get External Data, Link Tables.
- Next click on the "File of Type" popup list. Choose
"ODBC Databases()", "Machine Data Source" and then
select the DB2 database you want to copy the data to. Now follow the
- Once the tables are listed, use shift-mouse-left to select all the tables
you plan to copy data to. You should now see a link to the tables in
- Now you need to create one query for each table that you want to copy data
- Click on the Query tab, New, Design View, then choose the table you
want to copy from. Click 'Add', the 'Close'.
- IF the field names are an identical match, click on the '*', otherwise
you will have to match up each field.
- Click on the menu's 'Query' option, the choose 'Append Query'.
Next choose the DB2 table to append to.
- Save the query, then Run the query.
- Warning: In DB2 make sure the transaction log in DB2 can handle the
# of transactions before a commit or you will get an error. To change
the # do the following.
- Start, Programs, DB2 for Win NT, Command Window.
- db2 list applications (Make sure no one
is in the dabatase.)
- db2 update db cfg for <your db2 name> using logsecond 100.
If you still get errors, up the 100 #. I think the default is 10.