problems with recordset accessing access

[luisator]

Hi everybody! I am having a strange problem, Im working with a remote access database, i have one application to introduce info to de database and other for making a query. The firt one works perfect...but in the second one the application jumps to the error code when tries to open the recordset. the function I am using is the following:

Private Function Leer_DatoSql(cadena_ejecucion As String) As Variant
    'Ejecuta una cadena tipo Transact-SQL según la cadena de conexión
    'CONEXION BASICA CON LA BASE DE DATOS acces
    'FUNCION QUE SE LLAMA CADA VEZ QUE SE BUSCA UN DATO
    'LOS REGISTROS QUE SE TOMAN DE LA BASE DE DATOS SE RETORNAN EN UN OBJETO
    'RECORDSET

    On Error GoTo error_leer_datosql


    'CREACION DEL OBJETO RECORSET
    Dim REC As New ADODB.Recordset
    REC.CursorLocation = adUseClient
    REC.LockType = adLockReadOnly
    REC.CursorType = adOpenStatic
    'CADENA LA CONEXION DE LA BASE DE DATOS PARA TIPO ACCESS
    REC.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=Z:\softphone;" & _
        "Uid=usuario;" & _
        "Pwd=80234040;"
    REC.Source = cadena_ejecucion
    REC.Open
    REC.ActiveConnection = Nothing

    Set Leer_DatoSql = REC

    Exit Function
error_leer_datosql:
   Set Leer_DatoSql = Nothing
End Function

Please help me!!!

[mnemtsas]

Posting the error message would be helpful.

[Alvein]

Hi,

Maybe Luis does not know how to do it. -Maybe-. Anyways, a simple MsgBox REC.ActiveConnection.Errors(0).Description at the beginning of the error handler will solve the mistery.

BTW, I've tested the routine here, and it has no problems. But I have to say it's strange the way you "open" a new connection for every recordset to be returned. Because of you are not closing the connection (that would invalidate the Recordset) some resources cannot be freed the right way. One call to Leer_DatoSql() is not a big deal, but surely you plan to call this function many times.

I would suggest you to create a global connection (type is ADODB.Connection), open it right after program's beginning, and close it just before program's end. That way all your recordsets will share the same connection.

Suerte.

[Comatose]

Alvein has a real good point about opening and closing connections too frequently. It's been my experience with Access and VB, that doing a large number of opens/query's eats up value processor time, when only one connection is needed.

mnemtsas has a great tutorial on using Access with VB, and it's posted as a sticky thread at the top of this forum.