[AccessD] Access97 and ODBC Problem

Gustav,

What you suggested didn't work either. Your suggestion did lead me down another path. Here is what works...

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

Dim wrk As Workspace
Dim con As Connection
Dim rst As Recordset

Set wrk = CreateWorkspace("NewWorkspace", "admin", "", dbUseODBC)
Set con = wrk.OpenConnection("Con", , True, "ODBC;DSN=dataconnection;UID=user;PWD=password;DBQ=uxs02;DBA=R;APA=T;PFC=1;TLO=0;")
           
GetEmployee = False

Set rst = con.OpenRecordset("SELECT surname, given_name FROM employees WHERE employeeno = '" & strEmployeeNo & "';")

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

rst.Close
Set rst = Nothing

con.Close
Set con = Nothing

wrkODBC.Close
Set wrkODBC = Nothing

Exit Function

HandleError:

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

End Function

Thanks for your help.

Scott Marcus


-----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