[AccessD] Access97 and ODBC Problem

Gustav,

This is strange. When I run the code you gave me, I get the following error "3001:Invalid argument". Yet when I open the query from the IDE, it runs just fine. I guess I should try some other types instead of dbOpenForwardOnly.

Scott

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Monday, August 30, 2004 10:03 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access97 and ODBC Problem


Hi Scott

Did you try separating the database and querydef objects and/or using
a permanent query?

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

GetEmployee = False

Set dbs = CurrentDb
Set qdf = dbs.QueryDef("qdyTemp")
qdf.Connect = "ODBC;DSN=dataconnection;UID=user;PWD=password;DBQ=uxs02;DBA=R;APA=T;PFC=1;TLO=0;"
qdf.SQL = "SELECT surname, given_name FROM employees WHERE employeeno = '" & strEmployeeNo & "';"
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbSQLPassThrough)

If rst.EOF <> True Then
    GetEmployee = True
    strName = Trim(rst!given_name) & " " & Trim(rst!surname)
End If

rst.Close
Set rst = Nothing

qdf.Close
Set qdf = Nothing
Set dbs = Nothing

/gustav

> Why would the following function work the first time it is called
> but hang on subsequent calls? It locks up on the OpenRecordset
> statement. I've tried variations on the OpenRecordset statement  
> [such as Set rst = qdf.OpenRecordset()]. This is connecting to an
> Oracle database. Watch for line wrap. 

> Public Function GetEmployee(strEmployeeNo As String, ByRef strName As String) As Boolean
> On Error GoTo HandleError

> Dim qdf As QueryDef
> Dim rst As Recordset

> GetEmployee = False

> Set qdf = CurrentDb.CreateQueryDef("")
> qdf.Connect = "ODBC;DSN=dataconnection;UID=user;PWD=password;DBQ=uxs02;DBA=R;APA=T;PFC=1;TLO=0;"
> qdf.SQL = "SELECT surname, given_name FROM employees WHERE employeeno = '" & strEmployeeNo & "';"

> Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbSQLPassThrough)

> If rst.EOF <> True Then
>     GetEmployee = True
>     strName = Trim(rst!given_name) & " " & Trim(rst!surname)
> End If

> rst.Close
> Set rst = Nothing

> qdf.Close
> Set qdf = Nothing

> Exit Function

> HandleError:

> Call Log(Err.Number & ": " & Err.Description, "modEmployeeManagement:GetEmployee")

> End Function



> Thanks in advance,
> Scott Marcus
> TSS Technologies

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com