Pushing data to Access table

Sheridi

Board Regular
Joined
Nov 20, 2015
Messages
76
I posted earlier this week asking for help in retrieving data from an Access table. Now I want to update an Access table and I am trying to use this code:


Sub ConnectTODB()

Dim hBBWGCConn As ADODB.Connection
Dim hBBWGCCmd As ADODB.Command
Dim r As Range
Dim Player As Long
Dim uHcap As Double
Dim strSQL As String

Set hBBWGCConn = New ADODB.Connection
Set hBBWGCCmd = New ADODB.Command

hBBWGCConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BBWGC\Program\BBWGC2020.accdb;Persist Security Info=False;"
hBBWGCConn.Open
hBBWGCCmd.ActiveConnection = hBBWGCConn

hBBWGCConn.BeginTrans

For Each r In Range("A3", Range("A3").End(xlDown))
Player = (r.Offset(0, 0))
uHcap = (r.Offset(0, 2))

strSQL = _
"UPDATE tblPlayers" & _
" SET HcapIndex = uHcap" & _
" WHERE pkPlayerID = Player;"

hBBWGCCmd.CommandText = strSQL
hBBWGCCmd.Execute

Next r

hBBWGCConn.CommitTrans
hBBWGCConn.Close

Set hBBWGCConn = Nothing

End Sub
Which results in this error "No value given for one or more required parameters". Player and uHcap are both returning the proper data type.
Any ideas?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
You need to take your variables outside of the string

Change this
VBA Code:
strSQL = _
"UPDATE tblPlayers" & _
" SET HcapIndex = uHcap" & _
" WHERE pkPlayerID = Player;"

to this
VBA Code:
strSQL = _
      "UPDATE tblPlayers" & _
      " SET HcapIndex = " & uHcap & _
      " WHERE pkPlayerID = " & Player

Full code
VBA Code:
Sub ConnectTODB()

    Dim hBBWGCConn As ADODB.Connection
    Dim hBBWGCCmd As ADODB.Command
    Dim r As Range
    Dim Player As Long
    Dim uHcap As Double
    Dim strSQL As String
   
    Set hBBWGCConn = New ADODB.Connection
    Set hBBWGCCmd = New ADODB.Command
   
    With hBBWGCConn
        .ConnectionString = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\BBWGC\Program\BBWGC2020.accdb;" & _
            "Persist Security Info=False;"
        .Open
    End With
   
    hBBWGCCmd.ActiveConnection = hBBWGCConn
   
    hBBWGCConn.BeginTrans
   
    For Each r In Range("A3", Range("A3").End(xlDown))
        Player = (r.Offset(0, 0))
        uHcap = (r.Offset(0, 2))
       
        strSQL = _
            "UPDATE tblPlayers" & _
            " SET HcapIndex = " & uHcap & _
            " WHERE pkPlayerID = " & Player
       
        hBBWGCCmd.CommandText = strSQL
        hBBWGCCmd.Execute
    Next r
   
    hBBWGCConn.CommitTrans
    hBBWGCConn.Close
   
    Set hBBWGCConn = Nothing

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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