Thread: Connection Problem

Hello,

 

    I am having a problem connecting to postgresql using Access and ODBCDirect.  I am not sure that the problem is a odbc related problem, but I figured that I would ask and see if I could get a answer.  I am using Access 2000, PostreSQL 7.1.2 and pgsql-ODBC V 7.01.00.09.  When using any other method of connection (linked tables, pass-through query's) all seems to work great.  But when I try to use ODBCDirect I get a Type mismatch error from Access.  Here is the connection code.

 

Private Sub cmdLogon_Click()

    Dim FSEInfo As Workspace
    Dim cnn As Connection

    Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser, txtPassword, dbUseODBC)

 

    Set cnn = FSEInfo.OpenConnection("FSE_Info_cnn", dbDriverCompleteRequired, False, "ODBC;DSN=FSE Info;")
    
    cnn.Close

    Exit Sub

 

End Sub

 

The txtUser and txtPassword is supplyed by the user through a form.  Also here is the log output from the linux box running PostgreSQL.

 

2002-01-23 10:06:33 DEBUG:  connection: host=192.168.1.10 user=jtwatson database=fseinfo
2002-01-23 10:06:33 DEBUG:  query: set DateStyle to 'ISO'
2002-01-23 10:06:33 DEBUG:  ProcessUtility: set DateStyle to 'ISO'
2002-01-23 10:06:33 DEBUG:  query: set geqo to 'OFF'
2002-01-23 10:06:33 DEBUG:  ProcessUtility: set geqo to 'OFF'
2002-01-23 10:06:33 DEBUG:  query: set ksqo to 'ON'
2002-01-23 10:06:33 DEBUG:  ProcessUtility: set ksqo to 'ON'
2002-01-23 10:06:33 DEBUG:  query: select oid from pg_type where typname='lo'
2002-01-23 10:06:33 DEBUG:  query: select version()

 

It looks to me like the connection was excepted and successfull, but I get a Type mismatch error # 13.  Can anyone help me out here.  Any feedback would be appreciated.

 

 

Joseph

 

Well to be honest, I don't know.  I need to be able to start a transation,
make query's, and then based on the results, I need to decide if I can make
the changes to the database, then commit or rollback.  This was not working
with passthrough queiryes, but most likley I just don't know enouph, and
was doing it wrong.  So I am trying ODBCDirect.  Also I want my database to
be client independent as much as possible, so if I can bypass Jet, I would
like to.

Joseph

Gerrit Kilian wrote:

> Hi
>
> I was just wondering if ADO with the OLE DB provider for ODBC not a
> better choice is to connect to Postgresql? ODBCDirect is really just RDO
> ( Remote Data Objects ) using DAO object names. Do you experience better
> performance with ODBCDirect than ADO?
>
> Kind Regards
> Gerrit
>
>
>
>
>
>  -----Original Message-----
> *From:* pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org]*On Behalf Of* Joseph Watson
> *Sent:* Thursday, January 24, 2002 5:08 AM
> *To:* pgsql-ODBC@postgresql.org
> *Subject:* [ODBC] Connection Problem
>
>     Hello,
>
>
>
>         I am having a problem connecting to postgresql using Access and
>     ODBCDirect.  I am not sure that the problem is a odbc related
>     problem, but I figured that I would ask and see if I could get a
>     answer.  I am using Access 2000, PostreSQL 7.1.2 and pgsql-ODBC V
>     7.01.00.09.  When using any other method of connection (linked
>     tables, pass-through query's) all seems to work great.  But when I
>     try to use ODBCDirect I get a Type mismatch error from Access.  Here
>     is the connection code.
>
>
>
>     Private Sub cmdLogon_Click()
>
>         Dim FSEInfo As Workspace
>         Dim cnn As Connection
>
>         Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser,
>     txtPassword, dbUseODBC)
>
>
>
>         Set cnn = FSEInfo.OpenConnection("FSE_Info_cnn",
>     dbDriverCompleteRequired, False, "ODBC;DSN=FSE Info;")
>
>         cnn.Close
>
>         Exit Sub
>
>
>
>     End Sub
>
>
>
>     The txtUser and txtPassword is supplyed by the user through a form.
>     Also here is the log output from the linux box running PostgreSQL.
>
>
>
>     2002-01-23 10:06:33 DEBUG:  connection: host=192.168.1.10
>     user=jtwatson database=fseinfo
>     2002-01-23 10:06:33 DEBUG:  query: set DateStyle to 'ISO'
>     2002-01-23 10:06:33 DEBUG:  ProcessUtility: set DateStyle to 'ISO'
>     2002-01-23 10:06:33 DEBUG:  query: set geqo to 'OFF'
>     2002-01-23 10:06:33 DEBUG:  ProcessUtility: set geqo to 'OFF'
>     2002-01-23 10:06:33 DEBUG:  query: set ksqo to 'ON'
>     2002-01-23 10:06:33 DEBUG:  ProcessUtility: set ksqo to 'ON'
>     2002-01-23 10:06:33 DEBUG:  query: select oid from pg_type where
>     typname='lo'
>     2002-01-23 10:06:33 DEBUG:  query: select version()
>
>
>
>     It looks to me like the connection was excepted and successfull, but
>     I get a Type mismatch error # 13.  Can anyone help me out here.  Any
>     feedback would be appreciated.
>
>
>
>
>
>     Joseph
>
>
>

> Joseph Watson wrote:
>
> Hello,
>
>     I am having a problem connecting to postgresql using Access and
> ODBCDirect.  I am not sure that the problem is a odbc related problem,
> but I figured that I would ask and see if I could get a answer.  I am
> using Access 2000, PostreSQL 7.1.2 and pgsql-ODBC V 7.01.00.09.  When
> using any other method of connection (linked tables, pass-through
> query's) all seems to work great.  But when I try to use ODBCDirect I
> get a Type mismatch error from Access.  Here is the connection code.
>
> Private Sub cmdLogon_Click()
>     Dim FSEInfo As Workspace
>     Dim cnn As Connection
>
>     Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser, txtPassword,
> dbUseODBC)
>
>     Set cnn = FSEInfo.OpenConnection("FSE_Info_cnn",
> dbDriverCompleteRequired, False, "ODBC;DSN=FSE Info;")
>
>     cnn.Close
>
>     Exit Sub
>
> End Sub
>
> The txtUser and txtPassword is supplyed by the user through a form.
> Also here is the log output from the linux box running PostgreSQL.
>
> 2002-01-23 10:06:33 DEBUG:  connection: host=192.168.1.10
> user=jtwatson database=fseinfo
> 2002-01-23 10:06:33 DEBUG:  query: set DateStyle to 'ISO'
> 2002-01-23 10:06:33 DEBUG:  ProcessUtility: set DateStyle to 'ISO'
> 2002-01-23 10:06:33 DEBUG:  query: set geqo to 'OFF'
> 2002-01-23 10:06:33 DEBUG:  ProcessUtility: set geqo to 'OFF'
> 2002-01-23 10:06:33 DEBUG:  query: set ksqo to 'ON'
> 2002-01-23 10:06:33 DEBUG:  ProcessUtility: set ksqo to 'ON'
> 2002-01-23 10:06:33 DEBUG:  query: select oid from pg_type where
> typname='lo'
> 2002-01-23 10:06:33 DEBUG:  query: select version()
>
> It looks to me like the connection was excepted and successfull, but I
> get a Type mismatch error # 13.  Can anyone help me out here.  Any
> feedback would be appreciated.

Can you send me the Mylog debug output ?
You can turn on the Mylog option using ODBC Datasource
administraotr.

regards,
Hiroshi Inoue

Joseph Watson wrote:
>
> Here is my VB code:
>
> Private Sub Command5_Click()
>
>     Dim FSEInfo As Workspace
>     Dim conn As Connection

If I remove the above line(Connection), the following
seems to work though I don't understand the reason.

regards,
Hiroshi Inoue

>     Dim connstring As String
>
>     connstring = "ODBC;DSN=FSE Info;"
>
>     Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser, txtPassword,
> dbUseODBC)
>
>     Set conn = FSEInfo.OpenConnection("FSE_Info_cnn",
> dbDriverCompleteRequired, False, connstring)
>
>     conn.Close
>
>     FSEInfo.Close
>
> End Sub
>
> The error occurs on the "Set Conn" line, and reads:
>
>     Run-time error '13':
>
>     "type mismatch"

> -----Original Message-----
> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> Sent: 28 January 2002 00:36
> To: Joseph Watson
> Cc: pgsql-ODBC (E-mail)
> Subject: Re: [ODBC] Connection Problem
>
>
> Joseph Watson wrote:
> >
> > Here is my VB code:
> >
> > Private Sub Command5_Click()
> >
> >     Dim FSEInfo As Workspace
> >     Dim conn As Connection
>
> If I remove the above line(Connection), the following
> seems to work though I don't understand the reason.

If it were VB I'd say in a flash that ADO is referenced in the project
before RDO/DAO and conn was defaulting to ADODB.Connection. Is this possible
in Access?

Regards, Dave.

Dave Page wrote:
>
> > -----Original Message-----
> > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> >
> > Joseph Watson wrote:
> > >
> > > Here is my VB code:
> > >
> > > Private Sub Command5_Click()
> > >
> > >     Dim FSEInfo As Workspace
> > >     Dim conn As Connection
> >
> > If I remove the above line(Connection), the following
> > seems to work though I don't understand the reason.
>
> If it were VB I'd say in a flash that ADO is referenced in the project
> before RDO/DAO and conn was defaulting to ADODB.Connection. Is this possible
> in Access?

Oh I see.
Joseph, please change the line like
    Dim conn As DAO.Connection
and try.

regards,
Hiroshi Inoue

Thanks so much for all the help

I moved the DAO 3.6 up the reference list and this fixed the problem and it
works.

Thanks so much.

Joseph


----- Original Message -----
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Dave Page" <dpage@vale-housing.co.uk>
Cc: "'Joseph Watson'" <jtwatson@datakota.com>; "'pgsql-ODBC (E-mail)'"
<pgsql-ODBC@postgresql.org>
Sent: Monday, January 28, 2002 4:55 AM
Subject: Re: [ODBC] Connection Problem


> Dave Page wrote:
> >
> > > -----Original Message-----
> > > From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp]
> > >
> > > Joseph Watson wrote:
> > > >
> > > > Here is my VB code:
> > > >
> > > > Private Sub Command5_Click()
> > > >
> > > >     Dim FSEInfo As Workspace
> > > >     Dim conn As Connection
> > >
> > > If I remove the above line(Connection), the following
> > > seems to work though I don't understand the reason.
> >
> > If it were VB I'd say in a flash that ADO is referenced in the project
> > before RDO/DAO and conn was defaulting to ADODB.Connection. Is this
possible
> > in Access?
>
> Oh I see.
> Joseph, please change the line like
> Dim conn As DAO.Connection
> and try.
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

> > -----Original Message-----
> > From: Joseph Watson [mailto:jtwatson@datakota.com]
> >
> > Here is the Mylog debug output.  Hope it helps, thanks for the help.
>
> I see no suspicios error in the log.
> When(where) does your error occur ?
>
> regards,
> Hiroshi Inoue
>


I don't see anything funny in any of the logs myself.  In the PostgreSQL
log, it shows that the connection was successfull, and some default quiries
are executed and all looks good.  Likewise in the logges generated by the
ODBC driver, all look okay.  It seems to me that what ever is returned to
VB from the ODBC driver is not of the right data type.

Here is my VB code:

Private Sub Command5_Click()

    Dim FSEInfo As Workspace
    Dim conn As Connection
    Dim connstring As String

    connstring = "ODBC;DSN=FSE Info;"

    Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser, txtPassword,
dbUseODBC)

    Set conn = FSEInfo.OpenConnection("FSE_Info_cnn",
dbDriverCompleteRequired, False, connstring)

    conn.Close

    FSEInfo.Close

End Sub

The error occurs on the "Set Conn" line, and reads:


    Run-time error '13':

    "type mismatch"

and here is the log from PostgreSQL,

2002-01-26 06:25:39 DEBUG:  connection: host=192.168.1.10 user=jtwatson
database=fseinfo
2002-01-26 06:25:39 DEBUG:  query: set DateStyle to 'ISO'
2002-01-26 06:25:39 DEBUG:  ProcessUtility: set DateStyle to 'ISO'
2002-01-26 06:25:39 DEBUG:  query: set geqo to 'OFF'
2002-01-26 06:25:39 DEBUG:  ProcessUtility: set geqo to 'OFF'
2002-01-26 06:25:39 DEBUG:  query: set ksqo to 'ON'
2002-01-26 06:25:39 DEBUG:  ProcessUtility: set ksqo to 'ON'
2002-01-26 06:25:39 DEBUG:  query: select oid from pg_type where
typname='lo'
2002-01-26 06:25:39 DEBUG:  query: select version()

It lookes like the connection was successful and there was 5 successfull
queries made.  But I get a error dialog popup in access saying type mismatch
error.  It seems that the data returned to VB by the ODBC driver is being
rejected by the connection data type???

I attached a Access 2000 database that will reproduce this problem.  Just
edit the DSN to match one on your machine.

Thanks for the help thus far.


Joseph

 > > -----Original Message-----
 > > From: Joseph Watson [mailto:jtwatson@datakota.com]
 > >
 > > Here is the Mylog debug output.  Hope it helps, thanks for the help.
 >
 > I see no suspicios error in the log.
 > When(where) does your error occur ?
 >
 > regards,
 > Hiroshi Inoue
 >


     I don't see anything funny in any of the logs myself.  In the
PostgreSQL
 log, it shows that the connection was successfull, and some default quiries
 are executed and all looks good.  Likewise in the logges generated by the
 ODBC driver, all look okay.  It seems to me that what ever is returned to
 VB from the ODBC driver is not of the right data type.

 Here is my VB code:

 Private Sub Command5_Click()

     Dim FSEInfo As Workspace
     Dim conn As Connection
     Dim connstring As String

     connstring = "ODBC;DSN=FSE Info;"

     Set FSEInfo = CreateWorkspace("FSE_Info_ws", txtUser, txtPassword,
 dbUseODBC)

     Set conn = FSEInfo.OpenConnection("FSE_Info_cnn",
 dbDriverCompleteRequired, False, connstring)

     conn.Close

     FSEInfo.Close

 End Sub

 The error occurs on the "Set Conn" line, and reads:


     Run-time error '13':

     "type mismatch"

 and here is the log from PostgreSQL,

 2002-01-26 06:25:39 DEBUG:  connection: host=192.168.1.10 user=jtwatson
 database=fseinfo
 2002-01-26 06:25:39 DEBUG:  query: set DateStyle to 'ISO'
 2002-01-26 06:25:39 DEBUG:  ProcessUtility: set DateStyle to 'ISO'
 2002-01-26 06:25:39 DEBUG:  query: set geqo to 'OFF'
 2002-01-26 06:25:39 DEBUG:  ProcessUtility: set geqo to 'OFF'
 2002-01-26 06:25:39 DEBUG:  query: set ksqo to 'ON'
 2002-01-26 06:25:39 DEBUG:  ProcessUtility: set ksqo to 'ON'
 2002-01-26 06:25:39 DEBUG:  query: select oid from pg_type where
 typname='lo'
 2002-01-26 06:25:39 DEBUG:  query: select version()

 It lookes like the connection was successful and there was 5 successfull
 queries made.  But I get a error dialog popup in access saying type
mismatch
 error.  It seems that the data returned to VB by the ODBC driver is being
 rejected by the connection data type???

 I attached a Access 2000 database that will reproduce this problem.  Just
 edit the DSN to match one on your machine.

 Thanks for the help thus far.


 Joseph