VBA code not retrieving any data

[Stephen11]

Hi, I am having trouble getting my VBA code to retrieve data from access. The code is as follows

Sub attempts() 
    Dim DBFullName As String 
    Dim TableName As String 
    Dim FieldName As String 
    Dim TargetRange As Range 
     
    Dim db As database 
    Dim rs As Recordset 
    Dim intColIndex As Integer 
     
    DBFullName = "C:\Users\Stephen\Desktop\Final database.accdb" 
    TableName = "SAMPLE" 
    FieldName = "ID" 
    MyCriteria = Sheets("test").Range("A2").Value 
    Set TargetRange = Range("A9") 
    Set TargetRange = TargetRange.Cells(1, 1) 
    Set db = OpenDatabase(DBFullName) 
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _ 
    " WHERE " & FieldName & _ 
    " = 'MyCriteria'", dbReadOnly) 
     
     
     
     ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1 
        TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name 
    Next 
     ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs 
    Set rs = Nothing 
    db.Close 
    Set db = Nothing 
     
End Sub

I am trying to tell the VBA that my criteria is a barcode in cell A2, sheet "test" of excel. However when I run the macro it only retrieves the database column headers and no data corresponding to the content of cell A2.
Anyone have any ideas where I am going wrong?

 

[s-h]

If ID is a text field try:

"SELECT * FROM " & TableName & " WHERE " & FieldName & " = '" & MyCriteria & "'"

If ID is a number field try:

"SELECT * FROM " & TableName & " WHERE " & FieldName & " = " & MyCriteria

Or use Microsoft Query with a cell parameter then no code required.