input data to access DB using excel vba

Status
Not open for further replies.

baha17

Board Regular
Joined
May 12, 2010
Messages
181
Dear Excel Gurus,
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:
Code:
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,113,775
Messages
5,544,148
Members
410,595
Latest member
Tatum2020
Top