CODEWITHSUNDEEP
formsms-accessvbadataprovider
erasmo carlos
erasmo carlos

Reputation: 682

Access VBA : Data provider could not be initialized

I get this error when coming from a "find" form to a "main form".

Steps are

  1. Open the main form
  2. Click on "Find" button on mani form, which opens another form named "Find"
  3. Click "Find" button on "Find" form
  4. Load record on main form

Code:

Private Sub cmdFind_Click()

    Dim SQLString As String
    Dim TitleString As String
    Dim con         As ADODB.Connection
    Dim cmd         As ADODB.Command

    If production_batch_find.Form.RecordsetClone.RecordCount > 0 Then        

        SQLString = "sku IN (SELECT sku FROM production_batch WHERE [user] = '" & getUsr & "')"    

    Else        

        If IsNull(txtSKU.Value) = False Then            

            If Len(txtSKU.Value) < 9 Then                

                If IsNumeric(txtSKU.Value) Then                    
                    txtSKU.Value = "MN" & String(7 - Len(txtSKU.Value), "0") & txtSKU.Value                    
                Else                    
                    MsgBox "The SKU you entered isn't a valid product number.", vbCritical, "Invalid SKU"                    
                    Exit Sub                
                End If            

            End If

            SQLString = "sku = '" & txtSKU.Value & "'"

        Else

            If IsNull(txtTitle.Value) = False Then                

                TitleString = txtTitle.Value                

                Do
                    If InStr(TitleString, "  ") > 0 Then
                        TitleString = Replace(TitleString, "  ", " ")
                    Else
                        Exit Do
                    End If
                Loop

                If Left(TitleString, 6) = "like '" Or Left(TitleString, 6) = "like " & Chr(34) Then                    

                TitleString = Mid(TitleString, 7)                    

                    If Right(TitleString, 1) = Chr(34) Or Right(TitleString, 1) = Chr(39) Then
                        TitleString = Left(TitleString, Len(TitleString) - 1)
                    End If

                    SQLString = "title LIKE '" & InsertChr39(TitleString) & "' OR ID IN (SELECT ID FROM Variants WHERE Variant LIKE '" & InsertChr39(TitleString) & "')"

                Else

                    SQLString = "title = '" & InsertChr39(TitleString) & "' OR ID IN (SELECT ID FROM TitleVariants WHERE VariantTitle = '" & InsertChr39(TitleString) & "')"

                End If

            Else

                If IsNull(txtSongID.Value) = False Then
                    SQLString = "(ID = " & txtSongID.Value & ")"
                End If

            End If

            If IsNull(cmbName.Value) = False Then
                SQLString = SQLString & IIf(SQLString > "", " AND ", "") & "(songid IN (SELECT ID FROM Names WHERE Nameno  = " & cmbName.Value & "))"
            Else
                If IsNull(cmbPrimary.Value) = False Then
                    SQLString = SQLString & IIf(SQLString > "", " AND ", "") & "Primary = " & cmbPrimary.Value
                End If
            End If

            If IsNull(cmbStatusID.Value) = False Then
                SQLString = SQLString & IIf(SQLString > "", " AND ", "") & "StatusID = " & cmbStatusID.Value
            End If

            If IsNull(cmbPublisher.Value) = False Then
                SQLString = SQLString & IIf(SQLString > "", " AND ", "") & "PublisherID = " & cmbPublisher.Value
            End If

        End If

    End If

    If SQLString = "" Then        
        MsgBox "You haven't entered any search criteria", vbInformation, "No Search"
        Exit Sub    
    End If

    SQLString = "SELECT * FROM Production WHERE " & SQLString

    Forms("Production").RecordSource = SQLString

    DoCmd.Close acForm, "ProductionFind"

End Sub

Once the record is loaded on the main form, a user can select an option from a combo-box "auto-search", this option will then execute a stored procedure that resides on a sql server, and use its output as its form RecordSource.

Code:

Private Sub cmbAutoSearch_Click()

    Dim ADOCon As ADODB.Connection
    Dim ADOQD As ADODB.Command
    Dim ADORS As ADODB.Recordset
    Dim SearchSQL As String

    SearchSQL = ""

    Set ADOCon = New ADODB.Connection
    With ADOCon
        .ConnectionString = GetConnectionString("MNCatalog")
        .Open
    End With

    If cmbAutoSearch.Value = 101 Then    

        Set ADOQD = New ADODB.Command
        With ADOQD
            .ActiveConnection = ADOCon
            .ActiveConnection.CursorLocation = adUseClient
            .CommandType = adCmdStoredProc
            .CommandText = "[dbo].[mn_Production_Derived_Products_Hierarchy]"
            .Parameters.Append .CreateParameter("@sku", adVarChar, adParamInput, 10, SKU.Value)
        End With

        Set ADORS = New ADODB.Recordset
        With ADORS
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            Set ADORS = ADOQD.Execute()
            Set Me.Recordset = ADORS.Clone
        End With    

    End If

    Set ADOQD = Nothing
    Set ADORS = Nothing    
    ADOCon.Close: Set ADOCon = Nothing

    Exit Sub

End Sub

The output from the stored procedure gets assigned in this line:

Set Me.Recordset = ADORS.Clone

The record(s) get loaded, everything so far seems to be working fine, if there are more than one record(s), the user can navigate back and forth on that result set.

The problem occurs when the user tries to use the "Find" form after having used the combo-box option/ functionality.

Steps:

  1. Click on "Find" button on mani form, which opens another form named "Find"
  2. Click "Find" button on "Find" form

Then Run-time error '31' shows up.

"Data provider could not be initialized."

When I run the debugger, I see that the error happens in this line in the "Find" form code:

Forms("Production").RecordSource = SQLString

I could see that the previous call to the stored procedure was still as the record source value.

So I tried this:

Forms("Production").RecordSource = ""
Forms("Production").RecordSource = SQLString

Even though that "clears" the value of {call stored procedure ?} (something like that), still returns the same error.

I am at a lost, I do not know how to correct this error. Any advice would be great. Perhaps is I am thinking too much, and this is fixed with something simple.

Thank you in advance.

Upvotes: 1

Views: 943

Answers (1)

Chris Meurer
Chris Meurer

Reputation: 468

Try importing your data from the ADO connection into a local temporary table, and then setting the recordsource property to that data. As noted in this older blog post

Something like this may be helpful:

dim t as tabledef, i as integer
for each t in currentdb.tabledefs
select case t.name
    case "myTempTable"
        currentdb.tabledefs.delete "mytemptable"
end select
next t

currentdb.execute "CREATE TABLE ..."

ADORS.MoveFirst
for i = 0 to ADORS.RecordCount -1
    currentdb.execute "INSERT INTO myTempTable " & _
        ADORS(i).Fields("FieldName1").Value & " as [FieldName1], " & _
        etc.
next i
...

Upvotes: 1

Related Questions