Connecting to PostgreSQL from Windows Platforms, Page 2
I won't be discussing how to create users and granting access rights. This information is readily available in the pgsql documentation (which comes package in the source distribution as well as an HTML version on the pgsql website). Besides, that could be a topic for a whole article in and of itself.
A side effect of the export process is that whatever indexes you created in your Access database won't be mirrored on the pgsql side. You will have to manually create your indexes on the pgsql side of the house after you complete the export process.
Say, for example, you have a tabled named INVOICE with an AutoNumber field named INVNUM. When the INVOICE table is exported to pgsql you are left with a standard numeric field with no special auto numbering property. Pgsql has a feature called a Sequence. A sequence in pgsql operates much like an AutoNumber field in Access. It's a manual process (read: you have to type a few commands) to create a sequence and modify a table but it isn't all that difficult.
To continue the example above you have a table named INVOICE and a numeric field named INVNUM. How do you go about changing the INVOICE table to use a sequence? First you have to create a sequence with the following command:
x' is the starting point for the sequence. If you don't specify a starting point for the sequence it will default to one. Not a good thing if you already have an invoice numbered one. There are other options available for the CREATE SEQUENCE command. Consult the website or documentation which comes packaged with the source.
CREATE SEQUENCE INVNUMSEQ START x;
Now that we have the sequence defined we need to modify the INVOICE. You do this with an ALTER TABLE command:
From this point onward whenever a record is inserted into INVOICE the INVNUM field will be set automatically to the next available invoice number.
ALTER TABLE INVOICE ALTER INVNUM SET DEFAULT NEXTVAL(INVNUMSEQ');
At this point we have your data stored safely in pgsql and you have created a slew of users to access it. What we're going to do now is replace the tables in your Access application with links to those now stored in pgsql. Before we do this you will want to make a copy of your Access database. Bad things happen to those who modify vast amounts if bits with wild abandon. Don't be one of those people okay?
Now we want to rename the existing Access tables to a name other than what they are presently named. For the time being rename all the existing tables by append OLD to their current names.
Now right-click on the table space in your Access database and click the LINK option. You will be presented with a dialog box quite similar to the one shown when exporting the data to pgsql. As with the last dialog you will see a field labeled Files of Type. Select this item and select ODBC Databases.
You will then be presented with a DSN list. Select the DSN you created for your pgsql server. Once you select the proper DSN you will be presented with a Link Tables dialog. Depending on the options you chose when creating the DSN you will either see all the tables in the pgsql database (system catalogs and your tables) or just a list of the tables we just exported. Select one all the tables you just created and click OK.
Access will now proceed to link the pgsql tables to your Access database. For each table you link Access will ask you about a primary key: when prompted select the fields that comprise the primary key for each table you link.
At this point in the game you will have an Access database with links to pgsql. The name of the Access links will coincide with the name of the table you linked to. We're now ready to start playing with your Access database, which isn't a database now, so much as a code repository. Your logic is in Access while your data now resides on pgsql.
At this point you will want to start testing your Access application to verify the operation. When you encounter a problem fix it on the spot. Continue till you're satisfied that everything went well and start using your application.
You can see that using pgsql from Windows is straightforward. Using the ODBC driver from the likes of VB, Excel, or Visual C++ is simply an extension of the topics covered here. If you need help connecting with Visual Basic send me email and I'll help you out.
Page 2 of 2