Updating record using code - DAO ADO ?????? 2

[Moss100]

Hello,

I would like to update a record in a table using code.

From my research it seems I would need to use a DAO or ADO connection.

If so which is the best? - The table is currently in Access (2013), but in the future it may end up in a sql server backend.

Could someone help me firstly decide which method is the best (or is there a better method)?

Basically I have a table called tbl_cars (with unique reference car_id)

Within the table I have a field named SOLD.

When I click a button on a form I would like the SOLD field to be updated to YES.

I understand that I could bind the table to the form - but in this instance this would not be desirable.

Is it also possible for the code to check whether the record exists and whether the record is currently being edited (to ensure that the code executes correctly - or tells the user that it couldn't update the record as it is currently being edited / doesn't exist?)

Many thanks for any help you can provide with this.

Best regards

Mark

 

[Andrzejek]

First you need to decide what your approach / overall logic will be.
“I would like the SOLD field to be updated to YES [in tbl_cars]” and also you said:
“check whether the record exists []or tells the user that [record] doesn't exist”

If you just want to UPDATE, the record has to be in tbl_cars. Unless you can also create a new record (INSERT a records into tbl_cars)? Is that the case?

I have a field (let’s say in tbl_cars) called LOCKED_BY_USER where I keep the login name of the user who ‘owns’ that record / who can UPDATE that record / who selected that record first. Everybody else who accesses that record gets the message “Record locked by ABCD” (ABCD comes from LOCKED_BY_USER field on that record). That’s my way of ‘locking’ the record to the first user who 'hits' that record. I set it to NULL when user moves to another record or leaves the application.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Hi Andy - thanks for your comments.

I am just wanting to update an existing record (not add a record).

I just thought that it would be good to put some error checking in just in case for some (unknown reason) the record had been deleted.

I think I will have to ignore the record locking at present (I was hoping that access would know whether a record was already being edited - sound like not)

Can you help me further?

Many thanks Mark -

So far I have come up with:

Dim db As database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.Openrecordset("tbl_cars")

(I resume this opens the recordset) - i'm now stuck in working out how to go to the right record (using car_id) and then edit this record.

 

[Andrzejek]

I see you have DAO. I think this is an old approach. I would suggest ADO (ADODB) so you can also connect to SQL Server or Oracle, or whatever.

Start a brand new Access not related to whatever you have now.
Place a command button cmdCommand on your Form
Add a Reference to: Microsoft ActiveX Data Object Library X.X

Option Explicit
Dim Cn As ADODB.Connection

Private Sub cmdCommand_Click()
Dim strSQL As String

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[blue]C:\SomeFolder\YourDBWithData.mdb[/blue];User Id=admin;Password=;"
Cn.CursorLocation = adUseClient
Cn.Open
[green]
‘You should have a connection to your Access DB established here
[/green]
strSQL = "Update tbl_cars Set SOLD = 'YES' Where car_id = 123"

Cn.Execute strSQL

Cn.Close
Set Cn = Nothing

End Sub

This is just to show you how to connect to your DB and how to update a record.
Let me know if that works for you.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Thanks Andy - i'm not sure why but I couldn't get that to work. If i stick with DAO is it likley to give probelms?

I have tested the code below (some different field names) - but it works.

What do you think of this code - I would welcome you thoughts. Are there any improvements that you could suggest?

Many thanks Mark

'On Error GoTo ErrorHandler

'This sub will chnage value of Ten_Archived to True

Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT * FROM tbl_tenants WHERE Ten_ID=2"
'We are using a select statement that will return only
'one record (Tenant ID 2)

Set rs = CurrentDb.OpenRecordset(sql)
'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

End If

If .Updatable 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
'Must start an update with the edit statement

.Fields("ten_archived") = True

'The field to update and the new value

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

End If

.Close
'Closes the recordset...

End With

Exitsub:
Set rs = Nothing
'...and set it to nothing
Exit Sub

ErrorHandler:
Resume Exitsub

 

[Andrzejek]

1. Please use tags to show your code as code (see my post). It is easier to read.
2. If your code works and you know what you are doing, that's great. But...
3. "The table is currently in Access (2013), but in the future it may end up in a sql server backend." - that's why I showed you how to connect to the DB that is outside of your front-end Access.

"If i stick with DAO is it likley to give probelms?" I don't really know, I am not an Access expert. I am more a VB guy (VB6, VB.NET, VBA)

"I couldn't get that to work" - any errors?

I hope somebody else will jump in and explore 'run thru' quesries...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

I would create a connection and execute an SQL statement much like Andy's solution. If you intend to possibly change, I would encapsulate the code in a public function that would be easy to convert in the future:

Public Function ExecSQL(strSQL as String) As integer
[COLOR=#4E9A06]' code here to set the connection using either DAO or ADO
' The function can evaluate the success and return a value of maybe 1 for success and 0 for oops[/color]


End Function

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Moss100

If i stick with DAO is it likley to give probelms?

I have used DAO and MS SQL server for many, many years with no problems, M$ keep changing their mind which one they recommend one minutes it's ADO then next it's DAO.

I use DAO with linked SQL tables and pass-through queries to execute transactional stored procedures.

The one limitation I have found with DAO and pass-through queries is you cannot utilise return values and parameters properly from your stored procedure, but understanding the limitation and the work-around needed means I haven't had the need to move to ADO, nor do I intent to.

Though it may be possible to achieve this with DAO if you use a direct connection string and pass-though argument instead of using a pass-through query directly.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music