UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Batch Updating Records Committrans, Access 2016    
 
   
rubbersidedown
post Jan 23 2019, 12:01 PM
Post#1



Posts: 14
Joined: 29-February 16



I have an access database from where I'm trying to update records in a table linked from an SQL database. To maintain integrity I need to make sure all records can/will be updated or else all the updates should fail/rollback. I've come across the BeginTrans, CommitTrans, and Rollback methods but I cannot get them to work as expected. From reading I was under the impression that I needed to declare a workspace and use that. But then I would get the following error when trying to run: "Compile Error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic". I then came across an article that said I could call those methods instead by prefixing them with "DOA.DBEngine.". The code now works but it runs very slowly, specifically when it is processing the "FindFirst" method. It also has the nasty side affect of causing an ODBC connection failure for all other users on the network who are accessing the same SQL database that contains the table I'm working on. You can see in the code below where I commented out my initial attempt of declaring a workspace. Am I doing something wrong or is there a better way that I should be going about this process?

CODE
    'Dim dirDb As String: dirDb = Application.CurrentProject.Path
    'dirDb = dirDb & "\" & Application.CurrentProject.Name
    
    'Dim wrk As DAO.Workspace
    Dim db As DAO.Database
    'Set db = wrk.OpenDatabase(dirDb)
    Set db = CurrentDb
    
    Dim rs1 As Recordset
    Set rs1 = db.OpenRecordset("qNewDefaultSupplier", dbOpenForwardOnly, dbReadOnly)
    'rs1.MoveFirst
    
    Dim rs2 As Recordset
    Set rs2 = db.OpenRecordset("InventorySuppliersQuery", dbOpenDynaset, dbSeeChanges)
    
    'db.BeginTrans
    DAO.DBEngine.BeginTrans
    
    Do While (Not rs1.BOF) And (Not rs1.EOF)
        Dim sCriteria As String
        sCriteria = "LocalSKU='" & rs1!LocalSKU & "' AND PrimarySupplier=True"
        rs2.FindFirst sCriteria
        If Not rs2.NoMatch Then
            rs2.Edit
                rs2!PrimarySupplier = False
            rs2.Update
        Else
        End If
        sCriteria = "LocalSKU='" & rs1!LocalSKU & "' AND SupplierID=" & rs1!FirstOfSupplierID
        rs2.FindFirst sCriteria
        If Not rs2.NoMatch Then
            rs2.Edit
                rs2!PrimarySupplier = True
            rs2.Update
        Else
        End If
        rs1.MoveNext
    Loop
    
    'db.CommitTrans
    DAO.DBEngine.CommitTrans
Go to the top of the page
 
theDBguy
post Jan 23 2019, 12:09 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,559
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

I didn't read (or follow) your entire post but typically, I think you would use transactions on bulk (or set) updates. For example:

CODE
[pseudocode]
BEGINTRANS
EXECUTE "UPDATE A BUNCH OF RECORDS", FAILONERROR
IF EXEC.FAIL THEN
    ROLLBACK
ELSE
    COMMITTRANS
END IF
[/pseudocode]

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Jan 23 2019, 04:49 PM
Post#3


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Hi, you don't ever seem to set your Workspace variable.

IIRC, it's should be something like:
CODE
' ...
  Set wrk = DBEngine(0)
  wrk.BeginTrans
' ...


However, you might be better off creating pass-throughs and running the transactions on the server.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jan 23 2019, 08:49 PM
Post#4



Posts: 3,132
Joined: 27-February 09



Could you please explain what's happening in the update? I'm not clear on what the recordset stuff is for. What rule are you trying to enforce with the transaction?
Go to the top of the page
 
rubbersidedown
post Jan 24 2019, 10:14 AM
Post#5



Posts: 14
Joined: 29-February 16



The table lists different distributors' part numbers in relation to our Local SKU. There is a field that determines which distributor is our primary for our Local SKU. eg:

LocalSKU | DistID | DistSKU | Primary
22-222 | 1 | 131 | -1
22-222 | 3 | 10-1 | 0
22-222 | 5 | WP2 | 0

What I'm doing is changing the [Primary] field, but we cannot have multiple primary suppliers for a single [LocalSKU]. So when I set the [Primary] field value in another record to True, I also have to make sure the rest of the records with the corresponding [LocalSKU] value have a [Primary] value of False. Hopefully that makes sense. My overall goal is to change these records without interrupting anyone else's workflow who is also accessing the same table or database. But it appears that I'm locking everyone else out from even querying the table while I'm trying to edit the records. In the past I haven't had to do any record edits like this while other people are using the database. So I'm kind of out of my depth here.
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 05:01 AM
Post#6


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Just an experiment, try:
CODE
  Dim strSQL As String

  strSQL = "UPDATE InventorySuppliersQuery " & _
           "  SET PrimarySupplier = Not PrimarySupplier " & _
           "WHERE PrimarySupplier = True " & _
           "   OR SupplierID IN (" & _
           "        SELECT TOP 1" & _
           "          FirstOfSupplierID " & _
           "        FROM qNewDefaultSupplier
           "      );"

  CurrentDb.Execute strSQL, dbFaliOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 05:05 AM
Post#7


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Actually, hold fire - you need to get LocalSKU into the query somehow - let me have a think ...

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 05:07 AM
Post#8


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Where are you calling the code from? Is it from a form event?

Will you have access to the value of LocalSKU?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 07:26 AM
Post#9


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Make a backup of your table first!!!

Then try:
CODE
Dim strSQL As String

  strSQL = "UPDATE InventorySuppliersQuery is " & _
           "INNER JOIN qNewDefaultSupplier ns " & _
                   "ON is.LocalSKU = ns.LocalSKU " & _
                  "AND (" & _
                    "is.PrimarySupplier = ns.FirstOfSupplierID " & _
                    "OR " & _
                    "is.PrimarySupplier = True " & _
                  ") " & _
             "SET is.PrimarySupplier = Not is.PrimarySupplier;"

  CurrentDb.Execute strSQL, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
rubbersidedown
post Jan 25 2019, 12:36 PM
Post#10



Posts: 14
Joined: 29-February 16



QUOTE
Make a backup of your table first!!!

Then try:
CODE
Dim strSQL As String

strSQL = "UPDATE InventorySuppliersQuery is " & _
"INNER JOIN qNewDefaultSupplier ns " & _
"ON is.LocalSKU = ns.LocalSKU " & _
"AND (" & _
"is.PrimarySupplier = ns.FirstOfSupplierID " & _
"OR " & _
"is.PrimarySupplier = True " & _
") " & _
"SET is.PrimarySupplier = Not is.PrimarySupplier;"

CurrentDb.Execute strSQL, dbFailOnError

hth,

d


That's so simple! I wouldn't have thought to just update using the inverse value of the [PrimarySupplier] field. I'll try it and get back to you, but just from reading it I can't see why it wouldn't work. Thank you!
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 12:44 PM
Post#11


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Let us know how you get on.

Sometimes, what we think *should* work very often doesn't!!!

--------------------


Regards,

David Marten
Go to the top of the page
 
rubbersidedown
post Jan 25 2019, 01:16 PM
Post#12



Posts: 14
Joined: 29-February 16



It worked like a charm. The only problem I ran into is that the Update query wouldn't work initially because of a select query I used within the update query itself. I instead created a temp table and used that instead. Works perfect.
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 01:52 PM
Post#13


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Great that you found a solution! thumbup.gif

If you get a chance, please share what worked in the end, and also the query which didn't work too.

Thanks,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
rubbersidedown
post Jan 25 2019, 03:43 PM
Post#14



Posts: 14
Joined: 29-February 16



The qNewDefaultSupplier query pulled data from a table and a totals query. Access has an issue when updating tables if you're using a totals query anywhere along the way. So I just used the data from qNewDefaultSupplier and put it into a temp table and then used it in the solution you suggested and everything worked as expected.
This post has been edited by rubbersidedown: Jan 25 2019, 03:45 PM
Go to the top of the page
 
cheekybuddha
post Jan 25 2019, 06:31 PM
Post#15


UtterAccess VIP
Posts: 11,299
Joined: 6-December 03
From: Telegraph Hill


Brilliant! Thank you.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 04:16 PM