Error - No value given for one or more required parameters

**jimmycantler** · 04-13-2009, 12:56 AM

Hey everyone, I'm trying to use a recordset with an access database to pull some employee information... here is my code

Code:

Private Sub ADORetrieveButton_Click()

    Dim strConnect As String
    Dim strSQL As String
    Dim i As Integer
    Dim j As Integer

  
    Dim myRecordSet As New ADODB.Recordset
    

 
    If Not (FileExists(DatabaseLocationTextBox.Value)) Then
        MsgBox "Error. The database file could not be found.", vbExclamation, "Error!"
        Exit Sub
    End If
    

    Range("A22:Z60000").Clear


    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & DatabaseLocationTextBox.Value & ";"
    
    strSQL = SQLQueryTextBox.Value
    

    myRecordSet.Open strSQL, strConnect, adOpenForwardOnly 'Error occurs here
    
    i = 4
    For Each fld In myRecordSet.Fields
        myRecordSet.MoveFirst
        j = 22
        Sheets("Sheet1").Cells(j, i) = fld.Name
        j = j + 1
        Do Until myRecordSet.EOF
            Sheets("Sheet1").Cells(j, i).Value = fld.Value
            j = j + 1
            myRecordSet.MoveNext
        Loop
        i = i + 1
    Next
    
    myRecordSet.Close
    Set myRecordSet = Nothing


End Sub

When I run the program, the values of the variables are as follows:

myRecordSet = <Object or With block variable not set>
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\class.mdb;"
strSQL = "SELECT * FROM Employees WHERE FirstName = 'Ellen';"

Seems like there is something fishy with myRecordSet, I have loaded all the appropriate object references, but maybe there is something wrong with my declaration. Can anyone help??

Thanks,

Jimmy

**ExlGuru** · 04-13-2009, 02:08 AM

I think Your problem is easily resolved by using the ADODB command object
instead of a recordset object for performing Updates, Deletes, and
Inserts. These are all action items. For Action items you want to
think in terms of the command object. For reads you can use the
recordset objects. With ADO.Net you can use a DataReader object for
reading; much nicer and easier to work with.

Here is a sample using the command object:

Code:

Dim cmd As New ADODB.Command
cmd.ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID =;Data
Source=C:\somedir\yourmdb.mdb"

cmd.CommandType = adCmdText
cmd.CommandText = "Update tbl1 Set fld1 = 'test'"
cmd.Execute

cmd.CommandText = "Insert Into tbl1 (fld1, fld2, fld3) " _
& "Values('test1', 'test2', 'test3')
cmd.Execute

cmd.ActiveConnection.Close

Here I am assuming tbl1 contains all text fields. Thus, delimit with
single quotes "'". For numeric - no delimeters, for dates, use "#" the
pound sign.

**jimmycantler** · 04-13-2009, 04:27 PM

ExlGuru, I greatly appreciate your assistance, but unfortunately I am required to use the recordset object in this case. That given, is there anything that seems to be visibly wrong with what I coded that would create that error? It seems to me that the myRecordSet variable shouldn't be equal to <Object or With block variable not set> but I'm not sure what is going wrong.

Do you, or anyone else, have any ideas?

Thanks again.

**ExlGuru** · 04-14-2009, 01:19 AM

Try this

Code:

myRecordSet.Open(queryString, myconnection,
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

In place of

myRecordSet.Open strSQL, strConnect, adOpenForwardOnly 'Error occurs here

Thread: Error - No value given for one or more required parameters

Thread Tools

Rate This Thread

Display

Error - No value given for one or more required parameters

Re: Error - No value given for one or more required parameters

Re: Error - No value given for one or more required parameters

Re: Error - No value given for one or more required parameters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions