Thread: ODBC - MSysConf
ok, in general it seems to be possible to connect Access97 ( not Access 2.0! ?right? ) to postgres. It works on ONE of my Computers in the office (486). On that computer I cannot start the 32Bit-ODBC in the systemconfiguration, but I can create DSNs within Access97, and I get connected to any postgres- database I wish (tried only 2). On my Pentium in the office and on ALL machines at home I get the error 4307, that there is a Table called MSysConf on my server but it does not have the expected format. I searched for this table using grep and pg_dumpall as well as psql \dS and could not find anything. So I created one as described in the MSAccess-Help, but this didn't help. Still the fomat was invalid. I do not want to use this table anyway ( do I? ), so what to do? Thanks for help, Karsten Kaus
Karsten Kaus wrote: > ok, in general it seems to be possible to connect Access97 ( not Access > 2.0! ?right? ) to postgres. I recall someone doing this successfully. The key here is that it needs to be running on Win95, Win98, or NT. > > It works on ONE of my Computers in the office (486). On that computer > I cannot start the 32Bit-ODBC in the systemconfiguration, but I can > create DSNs within Access97, and I get connected to any postgres- > database I wish (tried only 2). > On my Pentium in the office and on ALL machines at home I get the > error 4307, that there is a Table called MSysConf on my server but > it does not have the expected format. You do not need this table on the server. The driver will report it as an error in the log file, but it should not be a problem to the application. Activate the CommLog in the driver settings and send a copy of the errant session. Its located in C:\psqlodbc*.log. > > I searched for this table using grep and pg_dumpall as well as psql \dS > and could not find anything. So I created one as described in the > MSAccess-Help, but this didn't help. Still the fomat was invalid. > I do not want to use this table anyway ( do I? ), so what to do? > I would drop the table unless you know what you are doing with MSysConf. At least until you get on solid footing. It's improper configuration may cause other problems.
David Hartwig wrote:
>
> Karsten Kaus wrote:
>
> > ok, in general it seems to be possible to connect Access97 ( not Access
> > 2.0! ?right? ) to postgres.
>
> I recall someone doing this successfully. The key here is that it needs to
> be running on Win95, Win98, or NT.
>
as far as I understood, the PostgreSQL driver is a 32-Bit program, so it
does
not show up when I'm using Access 2.0 to link tables, pity, but I can
live with that,
even it will cost money again.
> >
> > It works on ONE of my Computers in the office (486). On that computer
> > I cannot start the 32Bit-ODBC in the systemconfiguration, but I can
...
> > error 4307, that there is a Table called MSysConf on my server but
> > it does not have the expected format.
>
> You do not need this table on the server. The driver will report it as an
> error in the log file, but it should not be a problem to the application.
>
I read that before, but the case is slightly different. The table does
not exist.
I created the table at home, not here in the office. I just veryfied
dropping the table and got the message it woldn't exist.
Now access should notice that the table is missing, but it states the
table would
be there having a wrong format.
> Activate the CommLog in the driver settings and send a copy of the errant
> session. Its located in C:\psqlodbc*.log.
I did that in the driversetup. but it didn't log to C:\psqlodbc*.log
(I've seen and read them before, but now they are not created any more).
The log is in c:\sql.log. This file is also mentioned in the driver
setup.
So I think it's ok. The info contained looks a little different than in
the
psql*-files, but as fas as I see - the same info is contained:
--------------Here comes c:\sql.log ---------------------
MSACCESS fff27265:fff26281 ENTER SQLAllocConnect
HENV 0x00b30204
HDBC * 0x0062c910
MSACCESS fff27265:fff26281 EXIT SQLAllocConnect with return
code 0 (SQL_SUCCESS)
HENV 0x00b30204
HDBC * 0x0062c910 ( 0x00b31668)
MSACCESS fff27265:fff26281 ENTER SQLSetConnectOption
HDBC 0x00b31668
UWORD 103
UDWORD 20
MSACCESS fff27265:fff26281 EXIT SQLSetConnectOption with return
code 0 (SQL_SUCCESS)
HDBC 0x00b31668
UWORD 103
UDWORD 20
MSACCESS fff27265:fff26281 ENTER SQLDriverConnectW
HDBC 0x00b31668
HWND 0x000002b8
WCHAR * 0x00b30560 [ -3] ""
SWORD -3
WCHAR * 0x00b31734
SWORD 510
SWORD * 0x0062c966
UWORD 1 <SQL_DRIVER_COMPLETE>
MSACCESS fff27265:fff26281 EXIT SQLDriverConnectW with return
code 0 (SQL_SUCCESS)
HDBC 0x00b31668
HWND 0x000002b8
WCHAR * 0x00b30560 [ -3] ""
SWORD -3
WCHAR * 0x00b31734 [ 362]
"DSN=PostgreSQL;DATABASE=faxlog;SERVER=192.168.0.3;PORT=5432;UID=kk;PWD=<censored>;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS="
SWORD 510
SWORD * 0x0062c966 (362)
UWORD 1 <SQL_DRIVER_COMPLETE>
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 51478
SWORD 2
SWORD * 0x0062c90e
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 51478
SWORD 2
SWORD * 0x0062c90e
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 51478
SWORD 2
SWORD * 0x0062c90e (2)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 9 <SQL_ODBC_API_CONFORMANCE>
PTR 51478
SWORD 2
SWORD * 0x0062c90e (2)
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0062c8ac
SWORD 100
SWORD * 0x0062c916
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x00b31838
SWORD 200
SWORD * 0x0062c916
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x00b31838 [ 24] "PSQLODBC.DLL"
SWORD 200
SWORD * 0x0062c916 (24)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x0062c8ac [ 12] "PSQLODBC.DLL"
SWORD 100
SWORD * 0x0062c916 (12)
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 46 <SQL_TXN_CAPABLE>
PTR 51328
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 46 <SQL_TXN_CAPABLE>
PTR 51328
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 46 <SQL_TXN_CAPABLE>
PTR 51328
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 46 <SQL_TXN_CAPABLE>
PTR 51328
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 27128
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 27128
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 27128
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR>
PTR 27128
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 27130
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 27130
SWORD 2
SWORD * 0x0062c742
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 27130
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
PTR 27130
SWORD 2
SWORD * 0x0062c742 (2)
MSACCESS fff27265:fff26281 ENTER SQLGetInfo
HDBC 0x00b31668
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 51332
SWORD 2
SWORD * 0x0062c87e
MSACCESS fff27265:fff26281 ENTER SQLGetInfoW
HDBC 0x00b31668
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 51332
SWORD 2
SWORD * 0x0062c87e
MSACCESS fff27265:fff26281 EXIT SQLGetInfoW with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 51332
SWORD 2
SWORD * 0x0062c87e (2)
MSACCESS fff27265:fff26281 EXIT SQLGetInfo with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
UWORD 1 <SQL_ACTIVE_STATEMENTS>
PTR 51332
SWORD 2
SWORD * 0x0062c87e (2)
MSACCESS fff27265:fff26281 ENTER SQLSetConnectOption
HDBC 0x00b31668
UWORD 101
UDWORD 0
MSACCESS fff27265:fff26281 EXIT SQLSetConnectOption with return
code 0 (SQL_SUCCESS)
HDBC 0x00b31668
UWORD 101
UDWORD 0
MSACCESS fff27265:fff26281 ENTER SQLAllocStmt
HDBC 0x00b31668
HSTMT * 0x0062c744
MSACCESS fff27265:fff26281 EXIT SQLAllocStmt with return code 0
(SQL_SUCCESS)
HDBC 0x00b31668
HSTMT * 0x0062c744 ( 0x00b31870)
MSACCESS fff27265:fff26281 ENTER SQLGetStmtOption
HSTMT 0x00b31870
UWORD 0
PTR 0x0062c6e8
MSACCESS fff27265:fff26281 EXIT SQLGetStmtOption with return
code 0 (SQL_SUCCESS)
HSTMT 0x00b31870
UWORD 0
PTR 0x0062c6e8
MSACCESS fff27265:fff26281 ENTER SQLSetStmtOption
HSTMT 0x00b31870
UWORD 0
UDWORD 60
MSACCESS fff27265:fff26281 EXIT SQLSetStmtOption with return
code 0 (SQL_SUCCESS)
HSTMT 0x00b31870
UWORD 0
UDWORD 60
MSACCESS fff27265:fff26281 ENTER SQLExecDirect
HSTMT 0x00b31870
UCHAR * 0x040ce9a0 [ -3] "SELECT Config, nValue FROM
MSysConf"
SDWORD -3
MSACCESS fff27265:fff26281 EXIT SQLExecDirect with return code
0 (SQL_SUCCESS)
HSTMT 0x00b31870
UCHAR * 0x040ce9a0 [ -3] "SELECT Config, nValue FROM
MSysConf"
SDWORD -3
MSACCESS fff27265:fff26281 ENTER SQLFetch
HSTMT 0x00b31870
MSACCESS fff27265:fff26281 EXIT SQLFetch with return code -1
(SQL_ERROR)
HSTMT 0x00b31870
MSACCESS fff27265:fff26281 ENTER SQLError
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
UCHAR * 0x0062c6ac (NYI)
SDWORD * 0x0062c6c8
UCHAR * 0x0089a9cc
SWORD 8192
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 ENTER SQLErrorW
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
WCHAR * 0x0062c25c (NYI)
SDWORD * 0x0062c6c8
WCHAR * 0x0062c268
SWORD 1024
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
WCHAR * 0x0062c25c (NYI)
SDWORD * 0x0062c6c8 (3)
WCHAR * 0x0062c268 [ 74] "Bindings were not allocated
properly."
SWORD 1024
SWORD * 0x0062c6de (74)
MSACCESS fff27265:fff26281 EXIT SQLError with return code 0
(SQL_SUCCESS)
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
UCHAR * 0x0062c6ac (NYI)
SDWORD * 0x0062c6c8 (3)
UCHAR * 0x0089a9cc [ 37] "Bindings were not allocated
properly."
SWORD 8192
SWORD * 0x0062c6de (37)
MSACCESS fff27265:fff26281 ENTER SQLError
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
UCHAR * 0x0062c6ac (NYI)
SDWORD * 0x0062c6c8
UCHAR * 0x0089a9fa
SWORD 8146
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 ENTER SQLErrorW
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
WCHAR * 0x0062c25c (NYI)
SDWORD * 0x0062c6c8
WCHAR * 0x0062c268
SWORD 1024
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 EXIT SQLErrorW with return code 100
(SQL_NO_DATA_FOUND)
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
WCHAR * 0x0062c25c (NYI)
SDWORD * 0x0062c6c8
WCHAR * 0x0062c268
SWORD 1024
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 EXIT SQLError with return code 100
(SQL_NO_DATA_FOUND)
HENV 0x00b30204
HDBC 0x00b31668
HSTMT 0x00b31870
UCHAR * 0x0062c6ac (NYI)
SDWORD * 0x0062c6c8
UCHAR * 0x0089a9fa
SWORD 8146
SWORD * 0x0062c6de
MSACCESS fff27265:fff26281 ENTER SQLFreeStmt
HSTMT 0x00b31870
UWORD 0 <SQL_CLOSE>
MSACCESS fff27265:fff26281 EXIT SQLFreeStmt with return code 0
(SQL_SUCCESS)
HSTMT 0x00b31870
UWORD 0 <SQL_CLOSE>
MSACCESS fff27265:fff26281 ENTER SQLFreeStmt
HSTMT 0x00b31870
UWORD 1 <SQL_DROP>
MSACCESS fff27265:fff26281 EXIT SQLFreeStmt with return code 0
(SQL_SUCCESS)
HSTMT 0x00b31870
UWORD 1 <SQL_DROP>
MSACCESS fff27265:fff26281 ENTER SQLDisconnect
HDBC 0x00b31668
MSACCESS fff27265:fff26281 EXIT SQLDisconnect with return code
0 (SQL_SUCCESS)
HDBC 0x00b31668
MSACCESS fff27265:fff26281 ENTER SQLFreeConnect
HDBC 0x00b31668
MSACCESS fff27265:fff26281 EXIT SQLFreeConnect with return code
0 (SQL_SUCCESS)
HDBC 0x00b31668
MSACCESS fff27265:fff26281 ENTER SQLFreeEnv
HENV 0x00b30204
MSACCESS fff27265:fff26281 EXIT SQLFreeEnv with return code 0
(SQL_SUCCESS)
HENV 0x00b30204
--------------------eof-----------------------------------
I verified again using pg_dumpall, that there is no table MSysConf.
And - even better: postmasters log says in that moment:
ERROR: msysconf: Table does not exist.
So I think I can be shure of that.
has anyone seen that before?
KK
What version of the PostgreSQL driver are you running? What version of Access? What version of the Driver Manager? The driver install will upgrade to the DM to 3.0 but it is optional. I probably should have asked this before we got this far. Karsten Kaus wrote: > > MSACCESS fff27265:fff26281 ENTER SQLExecDirect > HSTMT 0x00b31870 > UCHAR * 0x040ce9a0 [ -3] "SELECT Config, nValue FROM > MSysConf" > SDWORD -3 > Everything above this point looks ok. > > MSACCESS fff27265:fff26281 EXIT SQLExecDirect with return code > 0 (SQL_SUCCESS) > HSTMT 0x00b31870 > UCHAR * 0x040ce9a0 [ -3] "SELECT Config, nValue FROM > MSysConf" > SDWORD -3 > Here is where it gets strange. The above statement should have a return code of -1 if the table does not exist. And theerror message should say so. (I just this verified this on my system.) The next few statements tries to cope with the result set and it is not happy with what it is getting. > > MSACCESS fff27265:fff26281 ENTER SQLFetch > HSTMT 0x00b31870 > > MSACCESS fff27265:fff26281 EXIT SQLFetch with return code -1 > (SQL_ERROR) > HSTMT 0x00b31870 > > MSACCESS fff27265:fff26281 ENTER SQLError > HENV 0x00b30204 > HDBC 0x00b31668 > HSTMT 0x00b31870 > UCHAR * 0x0062c6ac (NYI) > SDWORD * 0x0062c6c8 > UCHAR * 0x0089a9cc > SWORD 8192 > SWORD * 0x0062c6de > > MSACCESS fff27265:fff26281 ENTER SQLErrorW > HENV 0x00b30204 > HDBC 0x00b31668 > HSTMT 0x00b31870 > WCHAR * 0x0062c25c (NYI) > SDWORD * 0x0062c6c8 > WCHAR * 0x0062c268 > SWORD 1024 > SWORD * 0x0062c6de > > MSACCESS fff27265:fff26281 EXIT SQLErrorW with return code 0 > (SQL_SUCCESS) > HENV 0x00b30204 > HDBC 0x00b31668 > HSTMT 0x00b31870 > WCHAR * 0x0062c25c (NYI) > SDWORD * 0x0062c6c8 (3) > WCHAR * 0x0062c268 [ 74] "Bindings were not allocated > properly." > SWORD 1024 > SWORD * 0x0062c6de (74) > ... > > I verified again using pg_dumpall, that there is no table MSysConf. > And - even better: postmasters log says in that moment: > ERROR: msysconf: Table does not exist. > So I think I can be shure of that. > > has anyone seen that before? Not I.
David Hartwig wrote: I still do not know, whats going on, but I get it running somehow: I must not need the controlpanel to set up the DSN. If I stay within Access when linking the tables and configure the DSN from the panel Access opens up, then everything works (as far as I checked). I even get write-access - wow. Access seems to make a copy of the driver-settings. I set up a DSN with read-only access (wich shows up in the contolpanel after linking the tables) and try to change to read/write in the controlpanel - it does not work. I have to link all the 70 tables new with a freshly created DSN, within Access. Hope I get this distributed to all the stations without too much handwork. For this first try, I'm only creating a faxlog for 6 of our users. But in 2 weeks it's going to be harder. ( By the way: Most of the records I will use are bigger than 2k. Can someone give me a keyword how to handle that?) > > What version of the PostgreSQL driver are you running? I thought it was the latest: PostgreSQL 6.40.0004 > > What version of Access? Access 97 - Access 2.0 does not recognize the PostgreSQL Driver. > > What version of the Driver Manager? The driver install will upgrade to the DM to 3.0 but it is optional. I should be already above that. > > I probably should have asked this before we got this far. > > Karsten Kaus wrote: > ... > > > > > MSACCESS fff27265:fff26281 EXIT SQLExecDirect with return code > > 0 (SQL_SUCCESS) > > HSTMT 0x00b31870 > > UCHAR * 0x040ce9a0 [ -3] "SELECT Config, nValue FROM > > MSysConf" > > SDWORD -3 > > > > Here is where it gets strange. The above statement should have a return code of -1 if the table does not exist. Andthe error message should say so. (I just this verified this on my > system.) The next few statements tries to cope with the result set and it is not happy with what it is getting. Thanks a lot so far, but still I'm not too happy with that. KK