Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Transactions in MS Access VBA Issue

Posted on 2014-09-14
8
Medium Priority
?
639 Views
Last Modified: 2014-09-18
Hi Experts,

I have an issue concerning using transactions in VBA.
I am trying to surround 2 queries that modify an existing table (Table1) in a transaction.

Table1 has data already in it and Field1 is set to Indexed and No Duplicates.

My code looks something like this:

BeginTrans

CurrentDB.Execute "DELETE * FROM Table1", dbFailOnError
CurrentDb.Execute "INSERT INTO Table1 (Field1, Field2) SELECT NewField1, NewField2 From Table2", dbFailOnError

CommitTrans

I am trying to DELETE all the records in Table1 and repopulate the table using Table2.
However I keep getting the following error:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

What I don't understand is if the data is suppose to be completely deleted, why would there be duplicates? Btw there are no duplicate values in Table2 so that's not the issue.

Is it because I am using transactions and because of that it doesn't see that the records will be deleted before they'll be inserted?

I've been working with workspaces for a bit now but I keep getting weird logic errors like that when I try to execute too many queries within a transaction.

I would really like to find a solution to the problem as the success of the execution of the queries in this procedure is really important as a whole.  

Please help! Regards,
TSI Solutions
0
Comment
Question by:TSIsolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 2000 total points
ID: 40322425
You need to create an ADODB connection to run these. Run the transaction within this connection. If you use the default DAO connection, Access uses a new connection for each part, and one query won't see the changes of the other until the commit.


Kelvin
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40322740
You need a common workspace:

Dim wks As DAO.WorkSpace
Dim dbs As DAO.Database

Set wks = DBEngine(0)
Set dbs = wks(0)

wks.BeginTrans
    dbs.Execute "DELETE * FROM Table1", dbFailOnError
    dbs.Execute "INSERT INTO Table1 (Field1, Field2) SELECT NewField1, NewField2 From Table2", dbFailOnError
wks.CommitTrans

/gustav
0
 
LVL 75
ID: 40323524
And you might want to error trapping which include a wks.RollBack ...

mx
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Closing Comment

by:TSIsolutions
ID: 40331087
Hi Kelvin,

You were right about working in ADO and not DAO. I creates initally a workspace with DAO and it kept giving me errors.
With ADO it worked like a charm and had no issues.

Thanks again for the help... it was driving me crazy.
0
 
LVL 75
ID: 40331096
@ TSIsolutions

FWIW ... I have worked with Transactions for years, running multiple actions queries within a Transaction.
With all due respect to Kevin Sparks, there is absolutely no need to create an ADODB connection to do this.  Gustav has shown the correct and simple way to do this.

mx
0
 
LVL 1

Author Comment

by:TSIsolutions
ID: 40331161
Hi Joe,

Maybe it's something that I have to look at again however, I tested the specific case where records in a table are deleted and then records were inserted into the same table.

Working with transactions in a workspace using DAO, the INSERT would not work and I would get the error "would create duplicates values in the destination table". That should not have happened given that the table was completely empty.

Doing the same thing with ADO Workspace in transactions did not give me any issues. Hope that clarifies it.

Lawrence
0
 
LVL 75
ID: 40331201
Well, I've been doing exactly what you are referring to ... along with a lot more complexity (in the same Transaction) .... and it works just fine.

I have a 'Report Import Manager' that a couple of people in our group use daily to auto load 6-7 Back End dbs on our server (over our 1Gb fiber optic WAN I might add) with reports we get from IT and/or out Metrics group. Some of the more complex auto load process have 15-20 action queries that all run in sequence under a single Transaction, including the sequence of steps you specified ... and a lot more.  There is no ADO involved. It's all DAO (long live DAO).

So ... yes, in the future you might want to revisit this .... just sayin' ...
In the meantime ... whatever works :-)
mx
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40331253
I used to use DAO, (my preferred coding style), until Scott advised me not to in this case

http://www.experts-exchange.com/Database/MS_Access/Q_24642612.html

As he states, not everyone is in this camp.

As with many things Access, there are many ways to solve the issue - with transactions I've found this to be the answer.

Kelvin
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question