How to Read Data from Access

[MikeDBMan]

I am using Excel and Access 2010.
I am using the below code to get a record from an access table. How do I read what I got? It should select one record. How do I know when I didn't get any data? Thanks for the help

Let ProvTxt = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThePath & Application.PathSeparator & TheFile    '& ";Extended Properties=Excel 12.0 Xml;HDR=YES;"
Set cnn = New ADODB.Connection
 
With cnn
    .Open ProvTxt
    For Y = 1 To Ctr
        Let SelTxt = "select * from " & WhichOne & " where 'Project ID'=" & "'" & OneRec(30, Y) & "'" & " and 'YYYYPP' = " & "'" & YYYYPP & "'"
        .Execute SelTxt
    Next
End With

 

[Derek Brown]

Here is an extract from one of my processes, that may help:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim objFld As ADODB.Field
Dim strDB_Path As String
Dim strSQL As String
'
strDB_Path = "C:\My Database.accdb"
strSQL = "select * from " & WhichOne & " where 'Project ID'=" & "'" & OneRec(30, Y) & "'" & " and 'YYYYPP' = " & "'" & YYYYPP & "';"
' Note the ";" at end of the above string
'
Set objConn = New ADODB.Connection
objConn.Provider = "Microsoft.ACE.OLEDB.12.0"
objConn.Open strDB_Path
If objConn.State = adStateOpen Then
    Set objRS = New ADODB.Recordset
    objRS.Open strSQL, objConn, adOpenForwardOnly
    On Error Resume Next
    objRS.MoveFirst
    If Err.Number = 0 Then
        On Error GoTo 0
        For Each objFld In objRS.Fields
            Msgbox objFld.Name & ": " & objFld.Value
        Next objFld
    End If
    '
    objRS.Close
    Set objRS = Nothing
End If
'
Set objFld = Nothing
objConn.Close
Set objConn = Nothing
'
End Sub

However, note that I have edited the code, removing the bits that are not relevant for you, and have not compiled the resulting code.
You will need to set a 'Reference' to Microsoft Active X Data Objects.