Access + Microsoft Access FAQ (Long) - Mailing list pgsql-interfaces

POSTGRESQL & ACCESS FAQ DRAFT -*- outline-mode -*-
Jul 26 2000 v0.1


Contributors:
WJB: Joel Burton, joel@scw.org

This is the start of a draft of a FAQ. It needs:

(a) questions
(b) answers
(c) ideas/tips/tricks, etc.

I'm happy to coordinate this. If you have anything to add, feel free 
to email it to joel@scw.org. (For best results, tag [PGFAQ] at the 
start of your subject line to keep it out of my email slush file.)

In draft form, this is in emacs outline mode (* = major topic, ** = 
2nd level, *** = 3rd...).

An important note:

The initial author (WJB) uses Access 97 and PostgreSQL 7.0.2 
(though, of course, I've recently used earlier versions of 
PostgreSQL...) Most of tips & questions here work for these two 
versions, but have not been tested on other versions. If you're 
using other versions and find these don't work OR do work, please 
let me know.

SUGGESTED SECTIONS:
- Creating your database in PostgreSQL
- ODBC setup and options
- Linking tables into Access
- Query strategies & differences
- Database administration
- Programming tips
- Other sources of help

TABLE OF CONTENTS:

. Where I can I find updated draft versions of this mini-faq?
. Why would I want to use Access as a front-end for PostgreSQL?
. Why would I want to use PostgreSQL as the back-end for Access?
. What other front-ends are available?
. How do I create an ODBC data source?
. How do I create the linked table in Access?
. Can I create a linked table with VBA?
. How can I work with mixed-case table / field names in Access?
. In VBA, how can I tell if a table is local or linked?
. Can I use views in Access?
. What is the difference between a regular query and a pass-
through query
. How do I create a pass-through query?
. What is ODBCDirect?
. What books cover the subject of client-server programming in 
Access?
. Should I use bound or unbound forms in Access?
. How can I trap PostgreSQL errors in Access?
. What are optimal settings in the ODBC driver?
. What field types work well in Access?
. How can I reconcile the limit of Access Memo types (~2 billion 
chars) w/the limit of PostgreSQL Text types (~8k)?
. What considerations should I take in designing PostgreSQL tables 
for using with Access?
. Can I use Access security with PostgreSQL?
. What tools are available to convert Access (Jet) databases to 
PostgreSQL
. Is there a way to have PostgreSQL table/field comments appear as 
table/field descriptions in Access?
. Can I get Access to give me better error messages than the 
default?
. Are there any procedural language functions that are useful for 
Access-PostgreSQL databases?
. Can I / Should I show OIDs in Access?
. Can Access use LISTEN/NOTIFY?
. Can Access receive RAISE NOTICE messages returned by 
functions/queries?
. How do I setup my MSysConf table?
. Are there Windows program to help me create/manage my 
PostgreSQL databases?
. Can I create Crosstab queries in PostgreSQL
. What data-analysis tools are available under Windows?
. How can I find out what users are logged into my database?
. Are there differences in how Access 2, 95, 97, and 2000 work 
w/PostgreSQL?
. How do I backup my Access/PostgreSQL database?
. Where can I learn more about using Access + PostgreSQL
. Can someone from across the Internet use Access as front-end for 
my database?
. Help! I can't connect to PostgreSQL from Access
. How can I tighten security around my PostgreSQL server?
. How can I tune my PostgreSQL server for performance?
. Are there any books that can help a competent Access 
programmer learn standard SQL?
. What are the major feature differences between Access SQL / 
PostgreSQL]
. How can I keep my parameter query from always asking me for my 
datasource?
. Can I use PostgreSQL transactions from Access?
. Can other Micro$oft programs use a PostgreSQL database?
. Can Access and PostgreSQL agree on a character set for extended 
characters (accents, etc.)?
. Other Windows DB front-ends
. Help! I get "Another user has changed your data..." message after 
I try to edit a new record I just entered
. Help! I know I have a person named "Smith" in my table, but when 
I write a query to find them, they don't show up!
. How can I handle case-sensitivity differences between Access and 
PostgreSQL?
. Help! When I use "Find..." in Access, I never find the text record 
I'm looking for!
. Help! Every time I run a query, I get an error: "EOF the client 
closed the connection unexpectedly."
. Help! If I run a query with a boolean field in a WHERE clause, I get 
the following error "Unable to identify an operator '=' for types 'bool' 
and 'int4'"
. How do I compact my Access/PostgreSQL database?
. How can I make editing changes to the result of my pass-through 
query?
. Why can't I edit the results of my query?
. When I REVOKE privileges from a user INSERTing into a table, 
Access still allows them to add a record, but then gives an error 
message. Is there a way to have Access not allow them to try to 
add a record?
. How can I store OLE Object-type fields in PostgreSQL?
. How can I create an append-only table in Access/PostgreSQL?
. Can I use parameter queries against linked PostgreSQL tables?
. Can I make my bound forms open faster?

On to the minifaq...

* Where I can I find updated draft versions of this mini-faq?

http://www.scw.org/pgaccess/

Hopefully this will move to the postgresql site once it has more 
content and form, but, until then, you can download the most 
recent copy at the above address.

* Why would I want to use Access as a front-end for PostgreSQL?

- Performance over a network
- Triggers & rules
- Easier for serving over web / to other applications
- Cross-platform compatible
- Non-proprietary solution
- Very stable

* Why would I want to use PostgreSQL as the back-end for Access?

** PostgreSQL v. MySQL

For the same reasons one would prefer to use PostgreSQL generally 
over MySQL:
- transactions
- triggers / rules
- stored procedures
- indexes on functions

These become even more important in Access, as Access does not 
allow for hooking of events into table changes; therefore, triggers 
must be used for this.

** PostgreSQL v. MDAC (the free version of SQL Server that comes 
w/Access 2000)

Advantages of MDAC:
- MDAC & SQL Server are more tightly integrated in Access
- Better support for data types
- Easier to find support & documentation

Disadvantages of MDAC:
- MDAC limited to 5 connections
- MDAC runs only on Win32 machines
- MDAC is proprietary software

* What other front-ends are available?

** Web-based front-ends using CGI scripts
** Custom-made Tk/wxWindows solutions
** VB, C++ solutions that use DAO <-> ODBC

* How do I create an ODBC data source?

[TODO Someone?]

** Differences between data source types (File, User, Machine)

[TODO... Someone?]

* How do I create the linked table in Access?

First, you must have created your ODBC data source.

Then, in your Access database:

1. Go to the main database window
2. Choose File:Get External Data:Link Tables
3. Choose "Files of Type" = "ODBC Databases"
4. Find your data source under File Data Source or Machine Data 
Source
5. Select which table(s) you would like to link. You can select more
than one table.
6. If your connection options do not automatically detect your 
primary
key, you will be prompted for a unique index for each table.

During testing phases, you will frequently create a link, test it,
then delete the link, re-CREATE the table in pgsql, and need to relink
the table. It will be much easier to use VBA to create the link to
save yourself all this mousing around. See "Can I create a linked
table with VBA?"

* Can I create a linked table with VBA?

Function Link_ODBCTbl(rstrTblSrc As String, rstrTblDest As String)   Dim dbs As Database   Dim tdf As TableDef      Set
dbs= CurrentDb   Set tdf = dbs.CreateTableDef(rstrTblDest)      ' You should change this string to match your settings,
or  ' read this information from a global variable.
 
   tdf.Connect = "ODBC;DSN=my_dsn_name;"
   tdf.SourceTableName = rstrTblSrc   dbs.TableDefs.Append tdf
End Function

Use example: Link_ODBCTbl "students","tblStudent"

Note that in pgsql, you can refer to tables using mixed case, but 
pgsql handles this as all lower case (eg "CREATE TABLE Foo..." can 
be used later with "SELECT FROM Foo" as well as "SELECT FROM 
FOO" or "SELECT FROM foo"). However, when Access goes to find 
the table in the function above, you will need to specify the table in 
all lower case (Link_ODBCTbl("foo","tblFoo") ). If you create your 
table using double-quotes around the table name ('CREATE TABLE 
"Foo"...'), then the case is preserved, and you will need to find that 
table by using the exact case you created the table as.

* How can I work with mixed-case table / field names in Access?

When creating tables in PostgreSQL, you must use double quotes (") 
around all table and field names you wish to keep case sensitive:

CREATE TABLE "Widgets" ("WidgetID" INT PRIMARY KEY, 
"WidgetName" CHAR(10));

When writing queries in psql, you must use the double quotes:

SELECT * FROM "Widgets" WHERE "WidgetName"=10;

In Access, with normal (not pass-through) queries, you do not have 
to do any special. Access will handle case sensitive and case-
insensitive tables normally.

With Pass-through queries:

You must edit the query so that all case-sensitive tables and 
classes are double-quoted.

...

If you find facing an Access DB window full of all lower case tables 
to confusing (it does lead to names like trelstudentclassregs), you 
can always rename the linked table _in_Access_ to be mixed case. 
There's no problem having tblFoo (in Access) linked to tblfoo (in 
PostgreSQL).

* In VBA, how can I tell if a table is local or linked?

Look at the Connect property of the TableDef. This will be filled in 
for linked tables.

Function IsLinked(tbl) As Boolean   Dim dbs As Database      Set dbs = CurrentDb   IsLinked =
Len(dbs.TableDefs(tbl).Connect)> 0
 
End Function

Note that this doesn't tell us if this is a *PostgreSQL* linked table, 
just that it is a linked table.

* Can I use views in Access?

Yes, but to Access, a view appears as a table. Therefore, it is best 
to make views non-editable, either by:

(a) Access Jet security, or
(b) Create a form for the view, make this form read-only (change 
the Data Properites to Allow Edits=No, AllowDeletions=No, 
AllowAdditions=No), and have users use the Datasheet view of this 
form in stead of the table directly.

Currently (v7.0), views cannot contain ORDER BY clauses, 
aggregate functions / GROUP BY, or be UNION queries, so there 
usefulness is somewhat limited. It sounds like this may change in 
upcoming versions of PostgreSQL.

** Can I create a view in Access?

Not directly. You can, however, write a pass-through query that 
contains a CREATE VIEW... statement.

* What is the difference between a regular query and a pass-
through query

Regular queries are executed by Access. A query that involves 
Students and Classes, for instance, would get Student and Class 
data from PostgreSQL, then perform joins and criteria locally in 
Access. Regular queries are useful for writing queries that are either 
difficult or impossible using only PostgreSQL (such as crosstab 
queries and outer joins.)

A pass-through query is handled entirely by PostgreSQL. Access will 
not parse or modify the query statement in any way.

* How do I create a pass-through query?

TODO

** What kinds of changes are needed to make my SQL pass-
through?

*** Outer joins
*** " vs '
*** Table names using [] vs "
*** JOIN syntax
*** Aggregate/domain functions: STDEV, VAR, FIRST, LAST
*** Access & PostgreSQL differ in treatment of nulls
Access: 'Hello' & NULL = 'Hello'
PostgreSQL: 'Hello' || NULL = NULL


* What is ODBCDirect?

TODO

* What books cover the subject of client-server programming in 
Access?

Lesynski, Stan. Access 97 Expert Solutions. Que.

Litwin, Getz, & Gilbert. Access 97 Developer's Handbook. Sybex.

* Should I use bound or unbound forms in Access?

Bound forms are those that connect directly (via linked tables) to a
datasource. Unound forms are forms that do not connect directly to 
a
datasource, but have VBA routines that can get data and save data.

Advantages of bound forms:
- Much faster to develop (can just drag & drop or use Form Wizard)
- Can view form in datasheet view to see multiple records at once

Disadvantages of bound forms:
- Cannot trap ODBC error messages. You can notice that an ODBC error has happened; however, you 
cannot
find out which exact ODBC error happened (eg you can't distinguish 
a
primary key violation from a  CHECK clause violation.)


* How can I trap PostgreSQL errors in Access?

[discussion of trapping errors in bound forms v using DAO code]

* What are optimal settings in the ODBC driver?

[WJB: my settings are: (checked): Disable genetic, KSQO, 
Recognize unique, Parse stmt; Max unknown sizes, Text as 
LongVarChar, Cache 100, MaxVarChar 254, MaxLong 8190. I find the 
ODBC Faq a bit terse about the 
advantages/disadvantages/meanings of some of the options. Can 
anyone shed any light on some of the misc options, like genetic 
queries, etc.?]

* What field types work well in Access?
** CHAR vs. VARCHAR vs. TEXT

CHAR is space-padded, so the field actually contains 'foo   '. 
However, searching for 'foo' will match, as will 'foo ' and 'foo   '. This 
works directly in pgsql, as well as in Access (either with Jet queries 
or pass-through queries.)

** MONEY vs. DECIMAL vs. FLOAT

MONEY is deprecated by the PostgreSQL development team; they 
recommend DECIMAL(9,2) instead. I find DECIMAL (which Acess 
interprets as a text field) to be a bit weird. I recommend using a 
double float (FLOAT), which Access will interpret as a "Double", or 
double-precision floating point number. *Very occassionally*, this 
mean give rounding errors (you ask for 20/2, it could give 
10.000000001), however, the precision should be excellent.

** BOOLEAN vs. CHAR(1)

** DATE, TIME, DATETIME, TIMESTAMP

DATETIME is just a synonym now in PostgreSQL for TIMESTAMP.

DATE fields map to Access Date/Time fields, and seem to work fine 
for storing just dates.
TIME fields map to text fields in Access, and are a little difficult to 
work with [anyone have any tips?]
TIMESTAMP fields map to Access Date/Time fields, and are your 
best choice for dates & times or just times.

Access has no direct support for the exotic time interval times. 
Access will import & show these as text fields.

* How can I reconcile the limit of Access Memo types (~2 billion 
chars) w/the limit of PostgreSQL Text types (~8k)?

You can recompile PostgreSQL to allow longer Text fields. [I think--
How do you do this?]

* What considerations should I take in designing PostgreSQL tables 
for using with Access?

Access will show you the name of CONSTRAINTs on an error, so you 
may want to use these to give semi-meaningful information:

CREATE TABLE Foo (ID SERIAL PRIMARY KEY, Age INT CONSTRAINT 
"Age must be between 10 and 20" CHECK ( Age BETWEEN 10 AND 20 
) );

will cause the full constraint name to appear as an error message in 
Access.

It's not pretty, but at least it works. Also, see error messages & 
Access for ideas about how else to use constraint names.

* Can I use Access security with PostgreSQL?

** Using PostgreSQL security

You can bypass Access security (has everyone join DB as Admin) 
and use only postgresql security. As users open the database, you 
can prompt them (via an unbound form or an InputBox() call for 
their PostgreSQL username and pwd). Then, using these settings, 
connect your back-end tables.

Advantages: by using PostgreSQL security model, you can have 
users have the same access rights and passwords whether they use 
Access as a front end, or other use PostgreSQL directly.

Disadvantages: no protection offered against users deleting a linked 
table, modifying a form/report/query, etc.

** Using Access security

Don't use PostgreSQL security--just hardwire a username/password 
into the Access database for PostgreSQL into the Access database. 
Assign security permissions to tables using the normal security 
features and use Access security to limit access to database 
objects.

Advantages: Can protect forms, reports, queries, etc. Users can 
show passwords between Access databases.
Disadvantages: No protection against users connected to 
PostgreSQL directly (via pgsql, etc) and making changes. Limited 
control over table security? [is this true?]

** Using both

There's no reason why you can't use both security models. This 
way, you can use Access security to protect forms & reports, and 
use Access security to prevent linked tables from being 
deleted/renamed, while using PostgreSQL security to protect table 
data.

Disadvantages: have to synchronize passwords between two 
security models.

* What tools are available to convert Access (Jet) databases to 
PostgreSQL

There is an Access DB that will do this. [WJB: find the reference to 
this...]

* Is there a way to have PostgreSQL table/field comments appear 
as table/field descriptions in Access?

** Via automation

A longer version of the table-linking function can also pull in 
PostgreSQL comments and store these as Access descriptions.

Sub Link_ODBCTbl(rstrTblSrc As String, rstrTblDest As String)   Dim dbs As Database   Dim tdf As TableDef      Dim cnn
AsConnection   Dim wsp As Workspace   Dim qry As QueryDef   Dim rst As Recordset   Dim prp As Property
 
   Set dbs = CurrentDb   Set tdf = dbs.CreateTableDef(rstrTblDest)      tdf.Connect =
"ODBC;DSN=testdb;DATABASE=testdb;"  tdf.SourceTableName = rstrTblSrc   dbs.TableDefs.Append tdf
 
   ' Now get comments      Set wsp = DBEngine.CreateWorkspace("ODBC", "user_name", 
"user_pwd", dbUseODBC)   Set cnn = wsp.OpenConnection("", dbDriverCompleteRequired, 
False, "ODBC;DATABASE=dbname;DSN=dsnname")      Set qry = cnn.CreateQueryDef("", "select 
obj_description(pg_class.oid) AS descrip from pg_class where 
relname='" & rstrTblSrc & "'")   Set rst = qry.OpenRecordset(dbOpenSnapshot)   If Len(rst!descrip) > 0 Then       Set
prp= tdf.CreateProperty("Description", dbText, 
 
rst!descrip)       tdf.Properties.Append prp   End If      ' and field comments      Set qry = cnn.CreateQueryDef("",
"selecta.attname, 
 
obj_description(a.oid) as descrip from pg_class c, pg_attribute a 
where c.relname = '" & rstrTblSrc & "' and a.attnum > 0 and 
a.attrelid = c.oid")   Set rst = qry.OpenRecordset(dbOpenSnapshot)   With rst       Do While Not rst.EOF           If
Len(rst!descrip)> 0 Then               Set prp = 
 
tdf.Fields(rst!attname).CreateProperty("Description", dbText, 
rst!descrip)               tdf.Fields(rst!attname).Properties.Append prp           End If           rst.MoveNext
Loop  End With   Application.RefreshDatabaseWindow   
 
End Sub

** By hand

You can do this by hand (just Design your linked table), but given 
how often you delete the linked table and relink (everytime a table 
structure changes, etc.), you'll really get tired of retyping those 
descriptions.

** A decent compromise

Don't add comments to tables directly (until an automated tool is 
developed). Instead, add comments to the Description property of 
your form. It looks the same, but won't be lost if the table is re-
linked.

* Can I get Access to give me better error messages than the 
default?

[WJB: explain use of zstlkpPgErr to show a better error message 
given a constraint name]

* Are there any procedural language functions that are useful for 
Access-PostgreSQL databases?

** commaify

Often it is helpful in a query to return one record for a parent table 
with a comma-separated list of child information (rather than a 
separate record for each child.) You can create a comma operator 
and aggregate function to do this:

DROP FUNCTION comma_func(TEXT,TEXT);
CREATE FUNCTION comma_func(TEXT, TEXT) RETURNS TEXT AS '
BEGINIF (LENGTH($1) > 0 ) THEN   RETURN $1 || '', '' || $2;ELSE   RETURN $2;END IF;
END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE comma TEXT;
CREATE AGGREGATE comma(SFUNC1 = comma_func, BASETYPE = 
TEXT, STYPE1=TEXT, INITCOND1='');

COMMENT ON AGGREGATE comma TEXT IS 'Aggregate each item 
w/commas';
COMMENT ON FUNCTION comma_func(TEXT,TEXT) IS 'Aggregate 
each item w/commas';

Now, you can write:

SELECT student.name, comma(class.title) as classes FROM student, 
class WHERE student.sid = class.sid;

You can't use this aggregate operator in Access, but you can:

- Use it in pass-through queries
- Create a view that uses this aggregate operator, then import that 
view into Access

** imp (>>>)

Jet allows use of Imp for Implies, however, PostgreSQL does not 
have this. You can create an implies operator using:

CREATE FUNCTION imp (bool, bool) returns bool as '
BEGIN       IF ($2) or  (NOT $1) THEN RETURN ''t'';       END IF;       RETURN ''f'';
END;
' language 'plpgsql';


create operator >>> (procedure=imp, leftarg=bool, rightarg=bool);

comment on operator >>> (bool,bool) is 'logical implication';

You can then saying (boolean value) >>> (boolean value). So, if the 
presence of a subtitle implies that there must be a titleCHECK ( (Subtitle NOTNULL ) >>> (Title NOTNULL ) )
Not that you parens around (Subtitle NOTNULL) and (Title NOTNULL) 
are required. Without these, it seems that PostgreSQL does not 
case Subtitle NOTNULL into a boolean expression, and therefore the 
expression is not handled correctly.

** xor

Access has an XOR operator. This can be used in access by just 
comparing boolean expressions. For example, to ensure that a 
payment has *either* a credit card number or a check number (but 
not both). In Access, we could say:

ChkNum Is Null Xor CCNum Is Null

In PostgreSQL, this would be:

(ChkNum NOTNULL) <> (CCNum NOTNULL)


** string hashing routines

To help solve the case-insensitivity disagreement, and to help find 
matches in general, a phonetic algorithm can be used so that you 
can find all people/items that SOUND LIKE something, rather than 
having to match exactly.

In the contrib/ directory of PostgreSQL is a SOUNDEX algorithm. 
SOUNDEX is a quick but fairly crude sounds-like algorithm that 
converts English words to a single letter and a number.

There is also a Metaphone algorithm for PostgreSQL. Metaphone is a 
more sophisticated algorithm for hashing names. This (will be) 
available (soon) at www.scw.org/pgaccess.

** sending email from PostgreSQL

** Is it better to use Access modules for library code, or to create 
PostgreSQL functions?

Advantages of Access: 
- VBA is a more complete language than the procedural languages 
included in PostgreSQL
- can tightly integrate with Access environment (open/close forms, 
pop up dialog boxes, etc.)

Advantages of PostgreSQL:
- Can build business logic into database, so later environments 
(such as web interfaces) don't require as much programming.
- Access cannot trap actions to tables (only actions to forms, 
reports, etc.) So, to track changes made to a table, you must use 
PostgreSQL.

* Can I / Should I show OIDs in Access?

In the ODBC data source properties, you can decide to see OIDs.

You can't (as of 7.0) use OIDs as foreign keys in relationships, so 
their use is somewhat limited.

If you use OIDs and rely on them, you must dump and restore 
database while preserving the OIDs. See the PostgreSQL manual for 
details.

[I don't use OIDs for anything. Anyone have any good tips?]

* Can Access use LISTEN/NOTIFY?

[I don't think so. Anyone know how?]

* Can Access receive RAISE NOTICE messages returned by 
functions/queries?

Yes. [WJB: explain protocol as shown in AES w/Admin - 00, etc.]

* How do I setup my MSysConf table?

[WJB: TODO]

* Are there Windows program to help me create/manage my 
PostgreSQL databases?

** Windows pgsql

** Windows isql

** pgAdmin

[WJB: Never used this. Anyone want to write up some help?]

* Can I create Crosstab queries in PostgreSQL

You can create them in Access and use PostgreSQL data. You 
cannot create them directly in PostgreSQL.

* What data-analysis tools are available under Windows?

** Crystal Reports

[WJB: I don't use. Volunteers?]

** R

[WJB: I use a little. Anyone have experience?]

** Excel

* How can I find out what users are logged into my database?

[?]

* Are there differences in how Access 2, 95, 97, and 2000 work 
w/PostgreSQL?

Volunteers?

* How do I backup my Access/PostgreSQL database?

You can back up the Access file (*.mdb) as normal: by just copying 
it to another folder on your hard drive, server, or to a floppy 
disk/Zip disk. This will save your forms, reports, queries, and any 
local tables.

To back up your PostgreSQL database, you should make sure all 
connections are closed, then dump all tables to a file:

$ pg_dumpall

* Where can I learn more about using Access + PostgreSQL

The PostgreSQL INTERFACES electronic mailing list: pg-
interfaces@postgresql.org

* Can someone from across the Internet use Access as front-end 
for my database?

Sure, you can use any TCP/IP network to connect to your database 
backend: your local file server, or the Internet (assuming there are 
no firewalls in between that block the port your PostgreSQL server 
is using.)

See questions on how to set up PostgreSQL for remote access for 
more information.

* Help! I can't connect to PostgreSQL from Access

(1) Did you run pg_ctl with the -i switch?This switch is required for PostgreSQL to accept network 
connections. Without this switch, it only acceepts local connections 
over Unix named sockets.
(2) Have you edited pg_hba.conf? This file contains permission 
statements for remote connections.

* How can I tighten security around my PostgreSQL server?

Volunteers?

* How can I tune my PostgreSQL server for performance?

-F. Anything else? Anything ODBC-specific?

* Are there any books that can help a competent Access 
programmer learn standard SQL?

Celko, Joe. SQL for Smarties.

* What are the major feature differences between Access SQL / 
PostgreSQL]

[very incomplete so far & in no particular order...]

Access:
StdDev()
Var()
First()
Last()
Crosstab queries
Outer joins
DISTINCTROW


PostgreSQL:
Exotic types (geometry, network addresses, time intervals)
INTERSECTS
EXCEPT
Natural joins
SELECT 'Foo' (w/no FROM) is legal

* How can I keep my parameter query from always asking me for my 
datasource?

In the properties for the parameter query, fill in the CONNECT 
property. (You can copy & paste this from a linked table.)

* Can I use PostgreSQL transactions from Access?

** In pass-through queries:

Yes, just have your query include the BEGIN; and COMMIT. A pass-
through query can contain more than one SQL statement, so this 
would be legal:

BEGIN;
UPDATE Foo SET bar=12 WHERE baz>1;
COMMIT;

* Can other Micro$oft programs use a PostgreSQL database?

** Excel:

Excel can pull data from ODBC data source, either as a live or non-
live link. [Anyone else done this / have any tips?]

** Word:

Word cannot mail merge directly from an ODBC datasource (Word97 
at least--can anyone confirm/deny for W2000?). However, you can 
either link your tables into Acess, and use these Access tables as 
your data source, or (especially if you don't have Access), you can 
use Microsoft Query to write a query, save it as a *.qry file, and 
use this from Word.

** Powerpoint

[Anyone?]

** Outlook

[WJB: I don't use. Anyone?]

** Project

Project can store project data in an ODBC data source. However, 
when I (WJB) try to do this, I get an error that it can't find type "lo" 
in database. Can anyone shed any light on this? Is this just a large 
object problem?

* Can Access and PostgreSQL agree on a character set for 
extended characters (accents, etc.)?

You can enter accented characters in Access and they will be 
stored in PostgreSQL so that Access can later find and extract 
them. However, in PostgreSQL, they appear as strange symbols, 
*not* as the original Access character you entered.

[WJB: This is obvious a charset problem, a murky area well beyond 
my depths. Anyone have any suggestions? Is there a way at least 
to trim the funkiness in PostgreSQL, so a n-with-a-tilde could at 
least come out as 'n'?]

Unless a good solution is made, a workaround might be:

- two fields, one for the straight text, a second for the 
accented/formatted version


* Other Windows DB front-ends

** StarOffice

StarOffice is a free (& soon to be GPL'd) office package, similar in 
scope to Microsoft Office.

Works just fine using ODBC. StarOffice's capabilities for databases 
are fairly limited compared to Access (though I think many part of 
StarOffice equal Office in capability.) You can use your PostgreSQL 
data in all parts of StarOffice directly, though, so you can mail 
merge/use in charts, etc. with relative ease.

www.sun.com

** VisualBasic

[No experience. Anyone?]

* Help! I get "Another user has changed your data..." message after 
I try to edit a new record I just entered

[WJB: copy recent message from list]

* Help! I know I have a person named "Smith" in my table, but when 
I write a query to find them, they don't show up!

Access queries (using Jet) are normally case insensitive ("smith" == 
"Smith"); PostgreSQL queries are not. Did you search for "smith"?

See next question for more help.

* How can I handle case-sensitivity differences between Access 
and PostgreSQL?

Most of the time, you can expect your users to learn to give case-
proper queries. 90% of the time, it should obvious how to case 
something (eg., person names, widget names, etc.) The other 10% 
of the time, your users may not know how to properly case an item 
they're querying for.

In these cases:

Write your query so that you criteria against a lower-case version 
of the field:

SELECT tblClass.title FROM tblClass WHERE 
(LCase([title])="introduction to postgresql");

(In a pass-through query, you can't use the VBA function LCase. 
Instead, your query would be "SELECT tblClass.title FROM tblClass 
WHERE lower(title)='introduction to postgresql'")

I think this is safer than the usual advice given re: PostgreSQL and 
case-sensitivity, to use a case-insensitive regexp operator (*~). To 
use the *~, you have to (a) be writing a pass-through query, and 
(b) expect your users to understand a little about regular 
expressions (otherwise, they might get a different answer than they 
expected, if their pattern has regexp metacharacters in it.)

If you will use the lower() pass-through queries more than 
occassionally, you can create an index on a lowercased version of 
your table:

CREATE INDEX class_lower ON tblClass ( lower(title) );

This should make pass-through queries that use lower() on the title 
much faster.

- [WJB: I only can do this when my field type is TEXT, not CHAR or 
VARCHAR. Am I missing something?]

If you need faster searching w/non-pass-through queries, you can 
create a new column that holds the lowercase version of the field, 
index that, and search against that.

Of course, then, you'll have to maintain both fields. A trigger will 
help with this:

CREATE tblClass(ID SERIAL PRIMARY KEY, title VARCHAR(50) NOT 
NULL, ltitle VARCHAR(50) NOT NULL);

CREATE FUNCTION tblClass_LowTitle() RETURNS OPAQUE AS '
BEGIN  NEW.ltitle := lower(NEW.title);  RETURN NEW; 
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER lowtitle BEFORE UPDATE OR INSERT ON class  FOR EACH ROW EXECUTE PROCEDURE tblClass_LowTitle();

This will automatically update the ltitle field. For better 
performance, of course, you'll want an index on ltitle:

CREATE INDEX class_ltitle ON tblClass (ltitle);

* Help! When I use "Find..." in Access, I never find the text record 
I'm looking for!

This is because Access and PostgreSQL disagree about the case-
sensitivity of searches. You have two options:

1. Create, and search against, a lower-cased version of the field. 
See the previous question for details.
2. Turn on "Search Fields as Formatted". For some reason, this 
seems to work just fine [for WJB, at least, in Access 97 and 
PostgreSQL 7.0.2]

* Help! Every time I run a query, I get an error: "EOF the client 
closed the connection unexpectedly."

This seems to be caused by a timeout on the connection (ie, too 
much time passed since the connection was last used.)

To keep these errors from appearing, you can set the StartUp 
property for your Access database to open a hidden, bound form. 
This should keep a connection alive, and this message should no 
longer appear.

[WJB: works for me. does this work for other people?]

Or, set \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft 
Jet\3.5\Engines\ODBC registry key .ConnectionTimeout to a higher 
value. (from Litwin, Getz Access_97_Developer's_Handbook)

* Help! If I run a query with a boolean field in a WHERE clause, I get 
the following error "Unable to identify an operator '=' for types 'bool' 
and 'int4'"

(answer from Yury Don <yura@vpcit.ru>)

You need to create operator "=" for int4 and bool. I used the 
following (you need to have a plpgsql language installed in 
postgresql):

DROP OPERATOR = (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
CREATE FUNCTION MsAccessBool (bool, int4) RETURNS BOOL AS '
BEGIN IF $1 ISNULL THEN   RETURN NULL; END IF;
 IF $1 IS TRUE THEN   IF $2 <> 0 THEN     RETURN TRUE;   END IF; ELSE   IF $2 = 0 THEN     RETURN TRUE;   END IF; END
IF;RETURN FALSE;
 
END;
' LANGUAGE 'plpgsql';

CREATE OPERATOR = ( LEFTARG = BOOL, RIGHTARG = INT4, PROCEDURE = MsAccessBool, COMMUTATOR = '=', NEGATOR = '<>',
RESTRICT= EQSEL, JOIN = EQJOINSEL
 
);

[WJB: It would be nice to have this coded in C; given how often 
one would use this, the speed increase might help.]

* How do I compact my Access/PostgreSQL database?

You can compact your Access front end as usual, with Tools-
>Database Utilities->Compact; however, this only compacts the 
Access forms, reports, and native tables.

To clean up and re-organize your PostgreSQL database, you should 
use VACUUM;

You should probably not run a vaccum from a query in Access--it's 
probably safest to do this once everyone is out of Access, and to 
do it using psql.

You can create a cron job (man crontab for help) that will call psql 
to VACUUM. This could be set up to happen every night.

* How can I make editing changes to the result of my pass-through 
query?

Pass-through queries are not editable in Access. Rewrite as a local 
Jet query, or move into code using ODBCDirect (which returns 
editable recordsets.)

* Why can't I edit the results of my query?

- your query is a pass-through query, which is never editable
- your table lacks a primary key that Access recognizes

* When I REVOKE privileges from a user INSERTing into a table, 
Access still allows them to add a record, but then gives an error 
message. Is there a way to have Access not allow them to try to 
add a record?

You can have your users make data changes through forms instead 
(or form datasheet view). This way, the form can be set for 
AllowAdditions=No. (You'll have to figure out a way for Access to 
know whether a form should be editable or not given the user ID.)

Or, you could use Access Jet security on top of PostgreSQL security.

* How can I store OLE Object-type fields in PostgreSQL?

[WJB: I've never figured out how. I know there is the lo type in 
PostgreSQL, but couldn't get this to work w/Access. Any ideas?]

* How can I create an append-only table in Access/PostgreSQL?

(1) Create a table w/o UPDATE privileges, but having INSERT 
privileges. [This is more secure than option #2, but slower, as 
existing records will be sent to the front-end]

or

(2) Create a SELECT query that never finds anything (ie SELECT 
Class.*, FROM Class WHERE False)
Have your users append data to this query, which will never show 
previous records, but will allow appending of new records. [This is 
faster than option #1, as the server will never have to send any 
existing records to Access, but is less secure, as a user could just 
open the table directly]

or 

(3) Do both at once.

* Can I use parameter queries against linked PostgreSQL tables?

Yes. You can use these directly as queries, or via DAO code by 
filling in the .Parameters() properties of the QueryDef, just as with a 
non-ODBC query.

* Can I make my bound forms open faster?

Yes. Build a filter into your form that never finds any records, then 
have the form user change this to a filter that finds the records 
they want.

** Prefiltering

For example, with a customer table with 30,000 records, a bound 
form will try to open 100-1,000 records (as determined by by 
MSysConf values). This can take a few seconds. Instead, ahve the 
form initially be filtered to "LastName = 'ZZZ'". The user can then 
come in, Filter By Form, change that Filter to "LastName = 
'Mitchell'", and find just the records she needs.

** SELECT just what you need

Also, check that your recordsource for your bound form only brings 
in fields that you need. If your table has 50 fields, but your form 
only shows 10, don't have your form RecordSource = the entire 
table; rather, having the RecordSource = a SELECT query finding 
just those 10 fields.

** Leave off large object fields

When possible, leave Memo fields off forms. (You can always have a 
separate form for editing these comments.)
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)