pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC and Access 2000: linked tables problem
4+ messages / 3 participants
[nested] [flat]
* ODBC and Access 2000: linked tables problem
@ 1999-06-29 10:43 Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
1999-06-29 13:35 ` Re: [INTERFACES] ODBC and Access 2000: linked tables problem Byron Nikolaidis <byronn@solipsys.com>
1999-06-30 09:36 ` Re: [INTERFACES] ODBC and Access 2000: linked tables problem Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Moray McConnachie @ 1999-06-29 10:43 UTC (permalink / raw)
To: pgsql-interfaces
I was hoping someone could help me out with this, although
it's probably more of an Access problem than a Postgres
ODBC one...
I am using Access 2000 and postgres, with the postgres ODBC
drivers.
Everything worked fine under Access 97.
Importing tables from the ODBC connection is fine.
*Linking* is fine as far as the table selection and the importing of
fields and selecting of unique index goes.
However, when I try and open the linked table, it says "Access cannot
open the table in datasheet view". Nor can it access it via a query. I
have also tried hardcoding the table in a way which worked in Access 97
(code appended below), and it fails in exactly the same way.
SQL tracing appears to reveal that when a table is opened,
Access doesn't even get as far as putting a command to the
remote database.
This applies to new entirely blank databases. I have uninstalled and
reinstalled Office 2000. I have tried the same thing with the Openlink
ODBC drivers, which also used to work under 97, and now display the same
problem. Nothing has changed on the postgres database. I
have tried it on a different machine, and again it works
under Access 97, but not under 2000. I have looked on
technet/on-line support and posted to
comp.databases.ms-access.
It's driving me bonkers, particularly since there is
obviously nothing wrong with the basic link, as it will
import and link tables, just not open them!
Thanks,
Moray McConnachie
Here's the code I used to link the table...
Public Function CreateTable(Tablename As String) As Boolean
Dim remotetable As String, maintdb As Database, connectstring As String,
tdf As TableDef
remotetable = Tablename
On Error GoTo error
Set maintdb = CurrentDb
connectstring =
"ODBC;DSN=PSQLmaint;DATABASE=maint;SERVER=somewhere.me.here.now;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS="
Set tdf = maintdb.CreateTableDef(Tablename, dbAttachSavePWD,
remotetable, connectstring)
maintdb.TableDefs.Append tdf
maintdb.TableDefs.Refresh
CreateTable = True
Exit Function
error:
On Error GoTo 0
CreateTable = False
End Function
----------------------
Moray.McConnachie@computing-services.oxford.ac.uk
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] ODBC and Access 2000: linked tables problem
1999-06-29 10:43 ODBC and Access 2000: linked tables problem Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
@ 1999-06-29 13:35 ` Byron Nikolaidis <byronn@solipsys.com>
1 sibling, 0 replies; 4+ messages in thread
From: Byron Nikolaidis @ 1999-06-29 13:35 UTC (permalink / raw)
To: Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>; +Cc: pgsql-interfaces
Moray McConnachie wrote:
> I was hoping someone could help me out with this, although
> it's probably more of an Access problem than a Postgres
> ODBC one...
>
> I am using Access 2000 and postgres, with the postgres ODBC
> drivers.
>
> Everything worked fine under Access 97.
>
> Importing tables from the ODBC connection is fine.
> *Linking* is fine as far as the table selection and the importing of
> fields and selecting of unique index goes.
>
> However, when I try and open the linked table, it says "Access cannot
> open the table in datasheet view". Nor can it access it via a query. I
> have also tried hardcoding the table in a way which worked in Access 97
> (code appended below), and it fails in exactly the same way.
>
> SQL tracing appears to reveal that when a table is opened,
> Access doesn't even get as far as putting a command to the
> remote database.
>
Send the sql trace log.
Byron
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] ODBC and Access 2000: linked tables problem
1999-06-29 10:43 ODBC and Access 2000: linked tables problem Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
@ 1999-06-30 09:36 ` Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Moray McConnachie @ 1999-06-30 09:36 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@solipsys.com>; +Cc: pgsql-interfaces
On Tue, 29 Jun 1999 09:35:29 -0400 Byron Nikolaidis
<byronn@solipsys.com> wrote:
> Send the sql trace log.
Original problem (summary):
*Linking* is fine as far as the table selection and the importing of
fields and selecting of unique index goes.
However, when I try and open the linked table, it says "Access cannot
open the table in datasheet view". Nor can it access it via a query. I
have also tried hardcoding the table in a way which worked in Access 97
(code appended below), and it fails in exactly the same way.
The pgsql driver (6.40.00.06) log for the creation of the
initial link goes as follows. There is no driver log from
the attempt to open the link. If it is the driver's fault
(and I really don't have any evidence to that effect!),
then it must be to do with the way it creates the link in
the first place. The failure of the final call
(SQLGetInfo) worries me a little...
Thanks,
Moray
CONN ERROR: func=SQLGetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=163257928, conn=163264528, status=0, num_stmts=16
sock=163257944, stmts=163257992, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=163270864, buffer_out=163274968
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
CONN ERROR: func=SQLSetConnectOption, desc='fOption=30002, vParam=161946784', errnum=205, errmsg='Unknown connect option (Set)'
------------------------------------------------------------
henv=163257928, conn=163264528, status=0, num_stmts=16
sock=163257944, stmts=163257992, lobj_type=-999
---------------- Socket Info -------------------------------
socket=-1, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=163270864, buffer_out=163274968
buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=163264528, SQLDriverConnect( in)='DSN=PSQLmaint;', fDriverCompletion=1
DSN info: DSN='PSQLmaint',server='163.1.14.116',port='5432',dbase='maint',user='moray',passwd='enflexen'
readonly='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
Global Options: Version='06.40.0006', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=4094
disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=0
extra_systable_prefixes='dd_;', conn_settings=''
conn=163264528, query=' '
conn=163264528, query='set DateStyle to 'ISO''
conn=163264528, query='set ksqo to 'ON''
conn=163264528, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=163264528, SQLDriverConnect(out)='DSN=PSQLmaint;DATABASE=maint;SERVER=163.1.14.116;PORT=5432;UID=moray;PWD=enflexen;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=163264528, query='SELECT Config, nValue FROM MSysConf'
[ fetched 0 rows ]
conn=163264528, query='select relname, usename, relhasrules from pg_class, pg_user where relkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)order by relname'
[ fetched 56 rows ]
conn=163264528, query='select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c, pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname like 'callouts' order by attnum'
[ fetched 14 rows ]
conn=163264528, query='select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c, pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname like 'callouts' order by attnum'
[ fetched 14 rows ]
conn=163264528, query='select c.relname, i.indkey, i.indisunique, i.indisclustered from pg_index i, pg_class c, pg_class d where c.oid = i.indexrelid and d.relname = 'callouts' and d.oid = i.indrelid'
[ fetched 3 rows ]
CONN ERROR: func=SQLGetInfo, desc='', errnum=209, errmsg='Unrecognized key passed to SQLGetInfo.'
------------------------------------------------------------
henv=163257928, conn=163264528, status=1, num_stmts=16
sock=163257944, stmts=163257992, lobj_type=-999
---------------- Socket Info -------------------------------
socket=476, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=163270864, buffer_out=163274968
buffer_filled_in=250, buffer_filled_out=0, buffer_read_in=249
conn=163264528, SQLDisconnect
----------------------
Moray.McConnachie@computing-services.oxford.ac.uk
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] ODBC and Access 2000: linked tables problem
@ 1999-06-29 12:00 Jim Ridenour <jridenou@ee.net>
0 siblings, 0 replies; 4+ messages in thread
From: Jim Ridenour @ 1999-06-29 12:00 UTC (permalink / raw)
To: Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>; +Cc: pgsql-interfaces
I agree it sounds like an Access difference. I'd try one of MS's Access
newsgroups or the Knowledge Base.
At 11:43 AM 6/29/99 +0100, you wrote:
>I was hoping someone could help me out with this, although
>it's probably more of an Access problem than a Postgres
>ODBC one...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-06-30 09:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-29 10:43 ODBC and Access 2000: linked tables problem Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
1999-06-29 13:35 ` Byron Nikolaidis <byronn@solipsys.com>
1999-06-30 09:36 ` Moray McConnachie <Moray.McConnachie@computing-services.oxford.ac.uk>
1999-06-29 12:00 Re: [INTERFACES] ODBC and Access 2000: linked tables problem Jim Ridenour <jridenou@ee.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox