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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
This looks wrong.
Code:
rst(Cells(1, 7).Value) = 1

Recordsets don't use Cells()
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top