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?
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?