Pull data from Access to Excel "Runtime Error-2147217904(80040e10)"

[gmazza76]

Good afternoon,

I am trying to pull a record from an Access table into excel to use but I get an error "No value given for one or more required parameters" and I am unsure how to fix the error.
The "mysearch1" when the code runs and picks up the correct reference to find but unsure where to go from here. The only thing I can think of is that the "UNIQUEREF" isnt in the correct format.

Sub FindRef()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mysqlst, mysearch1 As String

mysearch1 = ThisWorkbook.Sheets("Quality Assurance Marking").Range("Q10").Value

    mysqlst = "SELECT BusinessLineTest.RACF,  " & _
   "BusinessLineTest.CallId, " & _
   "BusinessLineTest.Q1 FROM BusinessLineTest WHERE (BusinessLineTest.UniqueRef= " & mysearch1 & ") ;"

Set cn = New ADODB.Connection
With cn
.ConnectionString = con1
.Open
End With

Set rs = New ADODB.Recordset
rs.Open mysqlst, cn, adOpenDynamic, adLockPessimistic
   ThisWorkbook.Sheets("Current Marked").Range("B2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

thanks in advance
Gavin

 

[RoryA]

Assuming it's text, it should be in single quotes:

WHERE (BusinessLineTest.UniqueRef= '" & mysearch1 & "');"

 

[gmazza76]

Assuming it's text, it should be in single quotes:

WHERE (BusinessLineTest.UniqueRef= '" & mysearch1 & "');"

thanks @RoryA

I have re written the code with help of google and a colleague and it seems to work, but is there a way of instead of copying the "[Criteria]" to another sheet I could put each criteria in a specific cell?
ie [Criteria1] = Worksheets("Sheet1").Range("A5").Value or would this have to be run once the data has been pulled in?

Sub FindRef()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql, referenceNumber, ws As String

    Set ws = ThisWorkbook.Sheets("TabtoPopulate")

    referenceNumber = Worksheets("Sheet1").Range("Q10").Value

    If referenceNumber = "" Then
        MsgBox "Please enter a reference number.", vbExclamation
        Exit Sub
    End If

    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\myname.accdb;"

    sql = "SELECT [Criteria1], [Criteria2], [Criteria3],[Criteria4],[Criteria5], [Criteria6], [Criteria7], [Criteria8], [Criteria9], " & _
    "[Criteria10] FROM MTable1 WHERE UniqueRef = '" & referenceNumber & "'"

    On Error Resume Next
    Set rs = conn.Execute(sql)
    If Err.Number <> 0 Then
        MsgBox "Error querying database: " & Err.Description, vbCritical
        GoTo CleanUp
    End If
    On Error GoTo 0

    ws.Range("A3:Z1000").Clear ' Adjust range as needed


    If rs.EOF Then
        MsgBox "No data found for the given reference number.", vbInformation
    Else
        ' Populate headers
        Dim col As Integer
        For col = 0 To rs.Fields.Count - 1
            ws.Cells(3, col + 1).Value = rs.Fields(col).Name
        Next col

        ws.Range("A4").CopyFromRecordset rs

        MsgBox "Data retrieved successfully!", vbInformation
    End If

CleanUp:
    If Not rs Is Nothing Then rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

End Sub

 

[RoryA]

Yes, you'd basically do it the same way you are populating the field names now, but use .Value instead of .Name when writing to the cell. You can refer to the fields by name too:

Worksheets("Sheet1").Range("A5").Value = rs("Criteria1").Value

 

[gmazza76]

Yes, you'd basically do it the same way you are populating the field names now, but use .Value instead of .Name when writing to the cell. You can refer to the fields by name too:

Worksheets("Sheet1").Range("A5").Value = rs("Criteria1").Value

Sorry @RoryA would this be at the end or would it be in the "SQL" section of the coding, reasonably new at pulling the data in from Access to excel

Apologies just realised what you meant I will give it a go