how to commit updates?

[ckib]

Hi, I have some code (see step 1 below) that updates a set of contact records (tblContact) and adds a value for the field projectID (FK). The next step is an append query to insert records into an intersection table called tblSegment for each record that was just updated in tblContact. The problem is that the 2nd step fails ... for some reason I have to run the code a 2nd time for the append query to "see" the records that were updated by step 1. Is there a way to "commit" the updates so that step 2 will succeed? Thanks for any help with this problem.

Here is the code:

strProjectID = Me!projectID
strQryName = Me!proj_query

strText = "Step 1: Updating Contact records"
MsgBox (strText)

' promote contacts to research participants
' update tblContact with project id and active status = y using stored select query named in tblProject

Dim myUpdQuery As QueryDef

' Build SQL statement

strSQL = "UPDATE " & strQryName & " SET " & "cont_projectID = '" & strProjectID & "', " & "cont_active_status = 'Y';"

Set myUpdQuery = myDB.CreateQueryDef("", strSQL) ' create temp update query

' Run temporary QueryDef

myUpdQuery.Execute
myUpdQuery.Close

' ***************** Begin Step 2 *******************

strText = "Step 2: Appending Segment records"
MsgBox (strText)

' populate tblSegment records (intersection table) for all contacts participating in this project
' this table facilitates the many to many relationship between projects and contacts

Dim myAppendQuery As QueryDef

' build SQL statement for append query

strSQL = "INSERT INTO tblSegment ( seg_projectID, seg_contactID ) " & _
"SELECT tblContact.cont_projectID, tblContact.contactID " & _
"FROM tblContact WHERE (((tblContact.cont_projectID)='" & strProjectID & "'));"

Set myAppendQuery = myDB.CreateQueryDef("", strSQL) ' create temp append query

' run temporary QueryDef

myAppendQuery.Execute
myAppendQuery.Close

 

[Norie]

I don't know if it will help but why not use RunSQL rather than QueryDef to run the queries.

strProjectID = Me!projectID 
strQryName = Me!proj_query 

strText = "Step 1: Updating Contact records" 
MsgBox (strText) 

' promote contacts to research participants 
' update tblContact with project id and active status = y using stored select query named in tblProject 

' Build SQL statement 

strSQL = "UPDATE " & strQryName & " SET " & "cont_projectID = '" & strProjectID & "', " & "cont_active_status = 'Y';" 

DoCmd.RunSQL strSQL

' ***************** Begin Step 2 ******************* 

strText = "Step 2: Appending Segment records" 
MsgBox (strText) 

' populate tblSegment records (intersection table) for all contacts participating in this project 
' this table facilitates the many to many relationship between projects and contacts 

' build SQL statement for append query 

strSQL = "INSERT INTO tblSegment ( seg_projectID, seg_contactID ) " & _ 
"SELECT tblContact.cont_projectID, tblContact.contactID " & _ 
"FROM tblContact WHERE (((tblContact.cont_projectID)='" & strProjectID & "'));" 

DoCmd.RunSQL strSQL

 

[ckib]

runSQL

I tried that, but it didn't help.

I actually have to close the form, then open the form and rerun the code for step 2 to run. NOT IDEAL!

Thanks, C.

 

[mdmilner]

I can't explain the problem either - logically you should not be having this issue. There shouldn't be a need to commit changes - once run, it's run when you're not using workspaces to manage transactions.

Here's a question though. I noticed the second step was to manage a many-to-many relationship. Is there a chance it's a problem with your relationships? Might be worth a 60 second test.

 

[ckib]

relationships

I don't see how it could be a problem with the relationships. If I re-run the code after closing the form and then opening it, the tblSegment records are appended, and all the other forms/reports that are based on the many to many relationship work. I also displayed the SQL after I built the statement and it looks good. Very frustrating!

 

[mdmilner]

I was wondering if it might be a referential integrity problem. Not sure how this could be, but I'm not looking at your full mdb either.

Do you have your warnings off or on?
Could you be generating an error that's being ignored?
Did you only snip out the relevant portions? Any chance there's other code that interacts with the mdb that might prevent a SQL statement from executing?

Have you "stepped thru" your code or did you just execute it via Event?
Here's a thought that some are unaware of - even with code triggered by events, you can manually set a breakpoint within the event code module (say right at the beginning) - do whatever triggers the event and then the code stops...allowing you to step through each and every line of code by tapping the F8 key line by line.

If you also open up a Locals window (local variables) you can review the current state of each variable. You can also use the Immediate window to spot-check values/status/references as needed.

My suggestion would be - to step thru your code and look at your table before/after your update attempt. And then if it fails, manually drag the active line (yellow highlight) with your mouse to before the execution statement and see if simply executing it twice works.

I'd really like to hear if there's other "stuff" in this particular module being executed at the same time.

Mike

 

[mdmilner]

Only other quirky comment I'd have is - I noticed in the update query you're using a variable for your tablename. Why not use the same variable for the append query table name?

Yes this is probably not related to the problem unless there's a chance that despite the documentation you're not executing SQL on the same tables.

 

[ckib]

Thanks. I will try tracing through the code.

 

[ckib]

Here is an update on this problem for anyone curious as to how it was resolved. As it turned out, the index for tblContact was corrupted. I used the compact and repair tool, but that didn't resolve all the problems so I had to restore from a backup and re-import a bunch of data, and re-ran the "launch" code and have not had any other problems with this code.