Link to home
Start Free TrialLog in
Avatar of VoodooFrog
VoodooFrogFlag for United States of America

asked on

MS Access: Form.Recordset - ADO, Refresh

I have a form bound to an ado recordset that results from an sql stored procedure. If the form sits for a period of time, I will get a message saying, "Data provider could not be initialized." What can I do to prevent this message from popping up?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

"Data Provider" is the library being used to connect ... so the question is, how are you building your recordset?
Avatar of VoodooFrog

ASKER


Private Sub Form_Load()
    Dim adoConn As ADODB.connection
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Set adoConn = New ADODB.connection
    OpenADOConn adoConn 'function that uses connection string to open a trusted connection to the SQL server
    
    Dim adoCmd As ADODB.Command
    Set adoCmd = New ADODB.Command
    
    adoCmd.ActiveConnection = adoConn
    adoCmd.CommandText = "sp_GetWageInfo"
    adoCmd.CommandType = adCmdStoredProc
    adoCmd.Parameters("@Parameter1") = strParameter1
    rst.CursorLocation = adUseClient
    rst.Open adoCmd, , adOpenStatic, adLockOptimistic
    
    Set Me.Recordset = rst
    
    Set rst = Nothing
    Set adoConn = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, why use a Command object? You can open your recordset like this:

rst.Open "EXEC sp_GetWageInfo @Parameter1='" & strParameter1 & "'", adoConn
I ended up adding an On Error event to the form and catching the error. I then do as you suggest and rebuild the recordset.

Thanks!