Input data to Access DB from excel vba

[baha17]

Dear All,
First of all I am not very sure that my question is excel related or access related, therefore I apologized posting in both group.
I have code which is supposed to input data to access db however, it seems like If.. Then statement does not work. I tried to place if condition in my sql statement, still it does not work. Any help will be greatly appreciated.
thank you guys in advance.Here is my code in excel vba:

Sub Add_ScanCount2()
Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim MyConn
    Dim lngRow As Long
    Dim lngID As Long
    Dim J As Long
    Dim sSQL As String
    Dim GFLUserID As Variant
    'On Error Resume Next
    GFLUserID = 5378 'Range("TMID").Value
    
     Sheets("GFLUsers").Activate
    
    sSQL = "SELECT * FROM tblGFLUsers WHERE TMID = " & GFLUserID
    
    Set cnn = New ADODB.Connection
    MyConn = "J:\Gaming Common\GFL Performance" & "\" & "DataFiles\" & TARGET_DB1

    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:=sSQL, ActiveConnection:=cnn, _
             CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    If rst("LastCycleData") <> rst("ScanCycle") Then
    rst("ScanCount") = rst("ScanCount") + 1
       
    rst(Cells(1, 7).Value) = 1
    End If
    
    rst.Update
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
Sheets("GFLReport").Activate
End Sub

 

[xenou]

This looks wrong.

rst(Cells(1, 7).Value) = 1

Recordsets don't use Cells()

 

[baha17]

sorry I did not mention, there was a part in the code

Sheets("GFLUsers").Activate

In that sheet the first row has the name of table's fields. so it is supposed to referred to that. however, when I place a msgbox to test what rst value I might getting as,

msgbox rst("ScanCount")

I am always getting blank value. any thoughts?

 

[xenou]

In that sheet the first row has the name of table's fields. so it is supposed to referred to that. however, when I place a msgbox to test what rst value I might getting as,
Code:
msgbox rst("ScanCount")

I'm not sure what you mean. When/where do you place this msgbox?