Input data to Access DB from excel vba

baha17

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,718
Office Version
  1. 2019
Platform
  1. Windows
This looks wrong.
Code:
rst(Cells(1, 7).Value) = 1

Recordsets don't use Cells()
 

baha17

Board Regular
Joined
May 12, 2010
Messages
181
sorry I did not mention, there was a part in the code
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,
Code:
msgbox rst("ScanCount")
I am always getting blank value. any thoughts?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,718
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,523
Messages
5,548,547
Members
410,848
Latest member
anuradhagrewal
Top