Updating A Recordset

A

Atif Hasan

New member
Local time
Today, 08:20
Joined
Jun 14, 2022
Messages
5
Using someone's code found on internet and modified it I want to use cell value from Excel sheet. It is picking the value from the cell but not searching the record in my access database to update the fields of the same record. Can u please help me out. Thanks. Records are adding without any problem as i am using other code for that purpose.

Public Const Conn As String = “Data Source= C:\Users\Atif\Desktop\New Database\Sample.accdb;”

Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String

id = Range(“H1”)

cn.Open “Provider=Microsoft.ACE.OLEDB.12.0; ” & Conn

sql = “SELECT * FROM Table1 WHERE Invoice = ” & id

Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
‘Open RecordSet

With rs

If Not .BOF And Not .EOF Then
‘Ensure that the recordset contains records
‘If no records the code inside the if…end if
‘statement won’t run

.MoveLast
.MoveFirst
‘Not necessary but good practice

‘If .Supports(adUpdate) Then
‘It is possible that the record you want to update
‘is locked by another user. If we don’t check before
‘updating, we will generate an error

![Client] = Range(“B5”).Value

.Update
‘And finally we will need to confirm the update

‘End If
End If

.Close
‘Make sure you close the recordset…
End With

ExitSub:
Set rs = Nothing
‘…and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub

End Sub
 
Sort by date Sort by votes
please repost your code use code tags to preserve formatting. Very difficult to read as presented.

To use code tags, paste your code, highlight it then click the </> button

Also please clarify why you are using an ADO recordset rather than DAO recordset

Also where are you running this code from? looks like it is Excel but just to be clear
 
Upvote 0 Downvote
Code: 
Public Const Conn As String = “Data Source= C:\Users\Atif\Desktop\New Database\Sample.accdb;”


Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String


id = Range(“H1”)


cn.Open “Provider=Microsoft.ACE.OLEDB.12.0; ” & Conn


sql = “SELECT * FROM Table1 WHERE Invoice = ” & id


Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
‘Open RecordSet


With rs


If Not .BOF And Not .EOF Then
‘Ensure that the recordset contains records
‘If no records the code inside the if…end if
‘statement won’t run


.MoveLast
.MoveFirst
‘Not necessary but good practice


‘If .Supports(adUpdate) Then
‘It is possible that the record you want to update
‘is locked by another user. If we don’t check before
‘updating, we will generate an error


![Client] = Range(“B5”).Value


.Update
‘And finally we will need to confirm the update


‘End If
End If


.Close
‘Make sure you close the recordset…
End With


ExitSub:
Set rs = Nothing
‘…and set it to nothing
Exit Sub
ErrorHandler:
Resume ExitSub


End Sub
 
Upvote 0 Downvote
you forget to .Edit the record:

...
...
If .Supports(adUpdate) Then
‘It is possible that the record you want to update
‘is locked by another user. If we don’t check before
‘updating, we will generate an error

.Edit
![Client] = Range(“B5”).Value
.Update
 
Upvote 0 Downvote
arnelgp said:
you forget to .Edit the record:

...
...
If .Supports(adUpdate) Then
‘It is possible that the record you want to update
‘is locked by another user. If we don’t check before
‘updating, we will generate an error

.Edit
![Client] = Range(“B5”).Value
.Update
Click to expand...
when i put

Code: 
If .Supports(adUpdate) Then

.Edit,

the error is coming saying Compile error.

One more thing

Code: 
sql = “SELECT * FROM Table1 WHERE Invoice = ” & id

Instead of using "& id , if i am using '12345' (the data which is already in my database) it is updating the fields related to this record.

thanks
 
Upvote 0 Downvote
so there is no .Edit in ADODB,
you can try:
Code: 
Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & Conn

sql = "SELECT * FROM Table1 WHERE Invoice = " & id

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sql
End With
'rs.Open sql, cn, adOpenDynamic, adLockOptimistic

'Open Recordset

With rs

If Not .BOF And Not .EOF Then
'Ensure that the recordset contains records
'If no records the code inside the if…end if
'statement won’t run

.MoveLast
.MoveFirst
'Not necessary but good practice

'If .Supports(adUpdate) Then
'It is possible that the record you want to update
'is locked by another user. If we don’t check before
'updating, we will generate an error

![Client] = Range("B5").Value


.Update
'And finally we will need to confirm the update

'End If
End If

.Close
'Make sure you close the recordset…
End With

ExitSub:
Set rs = Nothing
'…and set it to nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
Resume ExitSub

End Sub
 
Upvote 0 Downvote
arnelgp said:
so there is no .Edit in ADODB,
you can try:
Code: 
Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & Conn

sql = "SELECT * FROM Table1 WHERE Invoice = " & id

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sql
End With
'rs.Open sql, cn, adOpenDynamic, adLockOptimistic

'Open Recordset

With rs

If Not .BOF And Not .EOF Then
'Ensure that the recordset contains records
'If no records the code inside the if…end if
'statement won’t run

.MoveLast
.MoveFirst
'Not necessary but good practice

'If .Supports(adUpdate) Then
'It is possible that the record you want to update
'is locked by another user. If we don’t check before
'updating, we will generate an error

![Client] = Range("B5").Value


.Update
'And finally we will need to confirm the update

'End If
End If

.Close
'Make sure you close the recordset…
End With

ExitSub:
Set rs = Nothing
'…and set it to nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
Resume ExitSub

End Sub
Click to expand...
Not working
and from where ID will get the value? it is supposed to come from Excel Sheet Range.

sql = "SELECT * FROM Table1 WHERE Invoice = " & id
 
Upvote 0 Downvote
is Invoice a String in your Table?
if it is change to:
Code: 
Public Const Conn As String = "Data Source= C:\Users\Atif\Desktop\New Database\Sample.accdb;"

Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String

id = Range("H1")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & Conn

sql = "SELECT * FROM Table1 WHERE Invoice = '" & id & "'"

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sql
End With
'rs.Open sql, cn, adOpenDynamic, adLockOptimistic

'Open Recordset

With rs

If Not .BOF And Not .EOF Then
'Ensure that the recordset contains records
'If no records the code inside the if…end if
'statement won’t run

.MoveLast
.MoveFirst
'Not necessary but good practice

'If .Supports(adUpdate) Then
'It is possible that the record you want to update
'is locked by another user. If we don’t check before
'updating, we will generate an error

![Client] = Range("B5").Value


.Update
'And finally we will need to confirm the update

'End If
End If

.Close
'Make sure you close the recordset…
End With

ExitSub:
Set rs = Nothing
'…and set it to nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
Resume ExitSub

End Sub

also you realize, without Explicitly qualifying your Range, it will point to the ActiveSheet.
to be sure, use the Sheet number of sheet name, eg:

id = Worksheets("Sheet1").Range("H1")
 
Last edited:
Upvote 0 Downvote
arnelgp said:
is Invoice a String in your Table?
if it is change to:
Code: 
Public Const Conn As String = "Data Source= C:\Users\Atif\Desktop\New Database\Sample.accdb;"

Sub ADODBUpdating()
On Error GoTo ErrorHandler
Dim sql As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection
Dim id As String

id = Range("H1")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & Conn

sql = "SELECT * FROM Table1 WHERE Invoice = '" & id & "'"

Set rs = New ADODB.Recordset
With rs
    .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open sql
End With
'rs.Open sql, cn, adOpenDynamic, adLockOptimistic

'Open Recordset

With rs

If Not .BOF And Not .EOF Then
'Ensure that the recordset contains records
'If no records the code inside the if…end if
'statement won’t run

.MoveLast
.MoveFirst
'Not necessary but good practice

'If .Supports(adUpdate) Then
'It is possible that the record you want to update
'is locked by another user. If we don’t check before
'updating, we will generate an error

![Client] = Range("B5").Value


.Update
'And finally we will need to confirm the update

'End If
End If

.Close
'Make sure you close the recordset…
End With

ExitSub:
Set rs = Nothing
'…and set it to nothing
Exit Sub
ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
Resume ExitSub

End Sub

also you realize, without Explicitly qualifying your Range, it will point to the ActiveSheet.
to be sure, use the Sheet number of sheet name, eg:

id = Worksheets("Sheet1").Range("H1")
Click to expand...
Thanks u very much Sir , it is working now, if there is anything or anywhere i will stuck, i will ask
i a am new in VBA Coding , that's need help
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

D
Solved Referencing a field within a RecordSet...
2
Replies
23
Views
1,007
cheekybuddha
cheekybuddha
B
  • Solved
Solved Help with recordset
Replies
6
Views
376
Pat Hartman
P
klsblues
VBA Conditional Formatting error because I have more than 3 formatting requirements.
2 3
Replies
53
Views
1,460
AHeyne
A
A
Restrictions Update Message
Replies
17
Views
512
Gasman
Gasman
avincent61
  • Question Question
DateAdd with DMax +1, not updating table data
Replies
14
Views
223
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom