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)
