원본출처 : http://sevainc.com/Access/

Porting Access97 data to PostgreSQL

This web site has been updated for PostgreSQL 7.1 (2/1/2001).

Here is a little background: I had an Access97 database with 80+ tables. The largest table has about 200K rows. There are 10 users of the system. The Access application had grown to over 20M in size and it contains no data. It became more difficult to maintain and it was taking longer to open and develop. Additionally Periodic corruption was causing me to rework code changes more often than I liked. As a result, I split the one large application into several libraries and a small main module. This helped the performance of opening the database and reduced my corruption issues. However, this required me to duplicate queries because several libraries relied on them. PostgreSQL views will eliminate this problem.

Why I ported to PostgreSQL

The up sizing utility called ConvertToPostgreSQL.mdb

What is does

What it does not do

Download the Software (an Access97 database called ConvertToPostgreSQL.mdb)

Exporting your data to PostgreSQL

Installing and configuring the PostgreSQL ODBC driver

Things to look out for

Requirements

Copyright

Questions or Comments

Why I ported my Access97 data to PostgreSQL:

Please note that I only ported the data to PostgreSQL. I continue to use Access97 as a front end or user interface. While Access97 is not my first choice for a user interface, it still is one of the better choices.

To reduce the size and complexity of my Access application. Heres how:

Migrated most Access queries to PostgreSQL views.

Eliminated duplicate views in several libraries.

Eliminated all Access code that maintains data integrity. I migrated this code to triggers in PostgreSQL (where it should be).

Ported most procedures and processes to PostgreSQL. Because of the many language choices and the effectiveness of PL/PGSQL, I expect this to simplify the code. This will allow me to provide html interfaces when appropriate and not have to duplicate the code.

PostgreSQL allows me to take advantage of triggers to do routine maintenance rather than using events in Access. I expect this to improve the integrity of the data.

PostgreSQL provides better performance with multi user access. Please note that Access performs better than PostgreSQL when only 1 or 2 users are hitting the database. PostgreSQL performs much better than Access with multi user access.

PostgreSQL offers more choices for implementing stored procedures and processes. Stored procedures can be created in SQL, PL/PGSQL, Ruby, TCL, Perl, or C. Additionally, I easily created C functions that could be called from PostgreSQL for functions that needed to run quickly and or often. The performance difference was amazing.

PostgreSQL reduces network traffic. When Access97 process a query, even if it produces a small number of records, it reads every record from the database onto the client machine and processes the data on the client machine. This means we are constantly upgrading our client machines to keep performance up as the data grows. With PostgreSQL, the data is processed on the server and only qualified rows are sent to the client machine. In other words, PostgreSQL sends a small set of data to the client machine while Access sends all the data. With PosgtreSQL, slower computers make effective workstations and remote users with slow connections are now a possibility.

ConvertToPostgres.mdb

(Click here to download ConvertToPostgreSQL.mdb)

This is an Access database that will migrate data from an Access97 to PostgreSQL. Download it by clicking on the link above. Unzip, run it, set up the database you want export, and click on the "Create Files" button. When configured correctly, this Access97 database will read all data from all tables in your database(s) and insert them into a PostgreSQL database. See the section Exporting your data to PostgreSQL for details.

What it does:

Process multiple Access97 databases at the same time

It creates a .sql file containing create table SQL statements for each table in your database. This is great for making changes to the schema and for rebuilding the database if you ever need to. This can also be used with other databases that support SQL. Primary key and foreign key constraints are created and supported. Table and column level descriptions are exported as comments.

It creates a .sql file containing create index SQL statements for each table (there could be many create index statements for each table) in your database).

It creates your Access tables and indexes directly in PostgreSQL (or you can run the .sql file in PostgreSQL and create the tables yourself).

It creates a sequence for every Auto Number field in Access and sets the sequence vales up correctly when copying the data.

It creates foriegn key constraints in PostgreSQL and in the .sql create table statements (with the "ON DELETE CASCADE" clause) when table relationships have been defined in your Access database.

It copies the data from Access97 to PostgreSQL.

It Creates a .sql file containing create index commands for every index in your Access 97 database. It also creates the indexes after it imports the data.

It Creates a .sql file that will create users and permissions for all tables for all users. This is only useful if you have defined and joined a workgroup in Access 97.

It Creates a .sql file that will re-create all sequences used for primary keys. When you select the load data feature, the sequences are automatically created and setup correctly.

What it does not do:

It will not port Access queries to PosgtreSQL views

It will not port any code to PostgreSQL

It will not port any Access97 forms to PostgreSQL or PgAccess

Exporting your data to PostgreSQL:

Download and install PostgreSQL. More info can be obtained at www.postgresql.org. It you want to install PostgreSQL on NT then you may want to also look at Running PostgreSQL on NT 4.0.

Make sure you have run initdb and createdb to create a database. For example:

initdb

createdb testdb

Download and install the PostgreSQL ODBC driver from ftp://ftp.postgresql.org/pub/odbc/psqlodbc_download.html.

Make sure that you turn "Read Only" off when configuring the ODBC driver. With release 6.5, the default was read only.

Open the ODBC Administrator tool and create an ODBC driver for PostgreSQL. Here is a web page that may help a little: Configuring an ODBC driver for PostgreSQL.

Download the ConvertToPostgreSQL.mdb utility

Unzip ConvertToPostgreSQL.mdb and open ConvertToPostgreSQL.mdb.

Review/set the following:

ODBC Data Source Name - set the Path column to the PostgreSQL Data Source Name (DSN) that you created using the ODBC Administrator. The check box to the left is ignored.

UserName - set the Path column to a PostgreSQL user name. This user will own the tables. The check box to the left is ignored.

Password - set the Path column to the password for the user name. The check box to the left is ignored.

Create Tables - set Path to the location where you want to store the .sql files for each table. Un check the check box to the left and the .sql files will not be created and the tables will not be created. You might do this if you have already created the tables in PostgreSQL and now you just want to export the data.

Include Comments - check this off and "CREATE COMMENT" statements will not be added to the database and .sql files.

Create Indexes - set Path to the location where you want to store the .sql files that will create indexes for each table. Remove the check mark if you don't want to automatically create the indexes.

ReCreate Sequences - set Path to the location where you want to store a file called recreate_sequences.sql. This file contains SQL statements that will update the sequence values for all sequences for the current set of data. Check this off if you don't want the sequences exported. Sequences are used for the Auto Number field in Access. When the data is first imported every sequence needs to be correctly updated. This both creates a script to do this and updates the database after the data is exported. This file can be useful when you need to import your data into a new or different database.

Permissions - set Path to the location where you want to store a file called permissions.sql. This file contains SQL statements that will create groups and users based on your Access security file. Check this off and this file will not be created.

Copy Data - check this on and the data is copied from Access to PostgreSQL. Check this off and the data is not copied. There were times during my testing when I created the tables without any data and then I would run the process again to export only the data.

So what can you do with all those script files? I check them all into cvs and use them to make modifications to the system. I apply the change to the database, test the changes, and check the changes into cvs. Nice, simple, and granular. Here is one way to apply the .sql files to the database:

psql -f tables/addresses.sql testdb test_user_name

psql -f indexes/addresses_i.sql testdb test_user_name

or

psql testdb test_user_name

\i tables/addresses.sql

\i indexes/addresses_i.sql

Open your Access database application (not the data files), delete all table links, and re-link all the tables using the newly created DSN for the PostgreSQL ODBC driver. The nice thing about PostgreSQL views is that Access97 thinks they are tables and will allow you create a table link to them.

Test your Access database application. The next step for me is to migrate my Access97 queries to PostgreSQL views, and write triggers and stored procedures in PostgreSQL.

Things to look out for:

I used fewer indexes with PostgreSQL. You could say I over used indexes with Access.

Fields defined with the boolean data type in Access97 are 2 byte integers in Access. In PostgreSQL they are a char. Most things work okay as long as you used code and views like "where boolean_value = true". If you have views or code that uses "where boolean_value = -1 or 0", then you have some code changes to make in your Access application.

Fields defined with the currency data types in Access do not map to the Money data type in PostgreSQL. I map all currency field to the numeric(9,2) data type in PostgreSQL. There are limitations in 7.1 with Access97 and numeric(9,2). I am in the process of finding solutions for this.

By default, views are not updatable in PostgreSQL. To remedy this, you will need to create insert, update, and delete rules on any view that you want to update. Please see the PostgreSQL documentation for details.

Make sure every table that you need to update from Access97 has a unique key index or a primary key. Access97 will not allow you to update a table that does not have a unique key index.

If you link a view or table and then change the view or table definition in PosgreSQL, you will need to drop the view/table link and recreate it in Access. Access appears to store information about the view/table. Future updates to the view/table will not be visible until you re-link the view/table. I created a VBA procedure in Access that will drop and relink all tables and views.

If you change any information in the ODBC Data Source Name (DSN) or create a new ODBC data source that you would like to use with Access, then you will need to drop all table links and recreate then in Access. Access appears to store information about the view/table. Future updates to the view or table will not be visible until you re-link the view or table. I created a VBA procedure in Access that will drop and relink all tables.

Your Access 97 forms will not always allow to insert data correctly if the primary key is not a part of the form. To fix this, I had to create a before insert event in each form and assign the primary key getting the next value from the sequence. Here is an Access97 VBA example:

Public Function get_primary_key(SequenceName As String) As Long

Dim qry As QueryDef

Dim rst As Recordset

Set qry = CurrentDb().CreateQueryDef("")

'UID=psql_user;PWD=psql_pass; are needed only if your Access users are different from your PosgtreSQL users"

qry.Connect = "ODBC;DATABASE=mp-dev;DSN=PostgreSQL;"

qry.SQL = "select nextval('" & SequenceName & "') as seq_value;"

qry.ReturnsRecords = True

Set rst = qry.OpenRecordset()

get_primary_key = rst!seq_value

rst.Close

qry.Close

Set qry = Nothing

Set rst = Nothing

End Function

Public Sub test_get_primary_key()

Dim rc As Long

rc = get_primary_key("membership_s")

Debug.Print rc

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

me.primary_key_field_name = get_primary_key("membership_s")

End Sub

I converted all currency data in Access to Numeric(9,2) in PostgreSQL. The numeric data is fairly new in PostgreSQL and is not supported completely enough for Access at this time. When updating or inserting into any table with a numeric field will fail from Access. To correct this error I had to apply the following patch to PostgreSQL and then rebuild and reinstall PostgreSQL:

cvs diff -r1.44 src/backend/parser/parse_oper.c

493a494,495

>

> /* Michael J Davis, 12/15/2000, since there is more than one canidate, lets return the first one

495c497,498

< return NULL; /* more than one remaining candidate */

---

> return candidates->args;

> */

In other words, this patch tells the parser to return candidates->args rather than NULL. This patch has been submitted to the PostgreSQL team. They rejected this patch because they desire a more complete solution. The above patch will work until a more complete solution is implemented.

The ODBC driver needs to be correctly configured:

Remove the "read only" flag from both the "Drivers" and "Configure" screens

Set "Use Unique Index" to on. This will tell Access to automatically enable a Primary key index on table with unique key indexes. A unique key index in PostgreSQL is the same thing as a primary key index in Access.

Requirements:

PostgreSQL 7.1 (installed and configured)

Access97 database(s), Access97 must be installed

Copyright:

This application is intended to assist people in migrating an Access97 database to PostgreSQL. An ODBC driver for PostgreSQL must be installed on your machine for the copy data feature to work. This can also be used as a learning tool to learn more about how Access works. Since it creates standard SQL code for creating tables, it could be used to migrate to any database that supports standard SQL and supplies an ODBC driver.

This is distributed as freeware. Source code is included. You may download and use it at your own risk as often as you like, free of charge (this is subject to change at any time. You may NOT copy, distribute, redistribute, sell, nor cut any paste any part of this application into any other application or document without written consent from Seva Inc.

Thanks for visiting this web site. If you find anything on this web site to be incomplete or difficult to understand, please drop us a note at the email address provided below. If you find this web site useful and helpful, we would also like to hear from you.

Please send comments, suggestions, or corrections about this web site to webmaster_access@sevainc.com.