We help IT Professionals succeed at work.

MS Access: Form.Recordset - ADO, Refresh

VoodooFrog
VoodooFrog asked
on
1,248 Views
Last Modified: 2013-11-28
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?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

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

Author

Commented:

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

Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Also, why use a Command object? You can open your recordset like this:

rst.Open "EXEC sp_GetWageInfo @Parameter1='" & strParameter1 & "'", adoConn

Author

Commented:
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!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.