Updating A Recordset (1 Viewer)

Atif Hasan

New member
Local time
Today, 12:16
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2013
Messages
16,553
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
 

Atif Hasan

New member
Local time
Today, 12:16
Joined
Jun 14, 2022
Messages
5
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:16
Joined
May 7, 2009
Messages
19,170
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
 

Atif Hasan

New member
Local time
Today, 12:16
Joined
Jun 14, 2022
Messages
5
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
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:16
Joined
May 7, 2009
Messages
19,170
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
 

Atif Hasan

New member
Local time
Today, 12:16
Joined
Jun 14, 2022
Messages
5
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
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:16
Joined
May 7, 2009
Messages
19,170
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:

Atif Hasan

New member
Local time
Today, 12:16
Joined
Jun 14, 2022
Messages
5
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")
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
 

Users who are viewing this thread

Top Bottom