Solved

vb6 and Access

Posted on 2014-07-18
7
225 Views
Last Modified: 2014-07-21
My project is written in VB6 and uses Access 2000 databases. This code has been in service for several years without incident.

I have some code where database updates are saved and then the code goes back to the form which called it.

I am populating one or more fields in a table.  I go through and do an update, and then go to the bookmark to move the record pointer back.

When I update one particular field that I am watching through the debugger, if this is a new record, first time through, and the only one in the table, I can watch the update take place.  Field course1 is updated with the value from a text box.  Then I do the update.  Since this is the only record, the debugger still points at the first record and shows the record is populated properly.  However. there will be later instances where additional records will be added to the table.  So, I set the bookmark before the update, do the update, set back to the bookmark, and the updated field is back to null.  Later code causes the app to quit since that field is not properly populated.

So, I come back in using the same db and table for another attempt.  There is one record in the table, although incomplete.  I go through the populating and updating the second time through and it remembers the updated field value.

I cannot imagine what is going on here.  Any ideas?  Or where should I look?
0
Comment
Question by:Skip_Laughlin
  • 3
  • 3
7 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
first time through, and the only one in the table
I can envision a potential problem with your code from your description.  How would you set a bookmark if there are no rows in the table?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I cannot imagine what is going on here.  Any ideas?  Or where should I look? >>

Really hard to say from your description.

I think I would:

1. Add a number of stops/msgboxes in various events to ensure that the sequence of events that is occurring  is what I expect them to be.    The thing that makes me wonder here is that it works "second time through", implying that your going through multiple loops of saving/modifying the record.

2. Look for use of requery.   Bookmarks don't survive a requery on a recordset.

3. In regards to:

"This code has been in service for several years without incident."

  Then I always ask, what has changed?

Jim.
0
 

Author Comment

by:Skip_Laughlin
Comment Utility
Yes, something has changed.  I went back to backup copies from early 2013 of the two involved forms and copied them into the current project folder.  And they exhibit the same screwed up behavior.

I know these worked as desired previously because I had many users using the compiled product. last year.

Did some DLL get updated as part of some other program install?  Was DAO 3.6 replaced by some other version?

Let me restate the behavior.  On the table in question, I create a single record in the subject table in form 1 and populate the key field.

Control then passes to form 2.  Some editing and updating of various fields in the record takes place.  A bookmark is used to maintain the record pointer position.

Control then passes back for form 1.  During some additional processing, the code determines that the fields updated in form 2 are no longer populated and the program ends.  The single record remains with the key field populated.

Here's the weird part.

When form 1 is loaded again, it does not create the single base record because it was created the first time through.  This time when control passes to form 2 an the subject fields are edited, populated, and updated, they stick.  When control passes back to form 1, the fields remain populated as they should.

Why won't that record take field edits and updates on form 2 when it is first created, but will the 2nd time through?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Why won't that record take field edits and updates on form 2 when it is first created, but will the 2nd time through? >>

 No idea...is it committed when leaving form 1 for 2?

<<Did some DLL get updated as part of some other program install?  Was DAO 3.6 replaced by some other version?>>

  DAO has not been updated in many, many, years.  Must easily be ten or so since Jet 4.0, SP8 and any update there was security.  DAO 3.6 was more or less finial as of '98.

  My guess is that it is a logic bug in the code that's always been there.   Has the software been moved to a new machine?   Very often, things which worked in the past may not work when moved to a slower/faster machine.

Jim.
0
 

Author Comment

by:Skip_Laughlin
Comment Utility
Well, the solution is weird, I think.

I execute a database rebuild when opening any mdb to assure a complete and correct access structure in the mdb and the tables.  Helps to forestall problems if a database has been damaged over time.

I was looking at field properties and noticed that after the rebuild above, the allow -0- length property for all text fields was set to false.  That could explain why the update did not take in form 2 the first time, because most of the text fields would not allow the -0- length.  ??

I wrote a routine that checks and update if necessary the allow -0- length in all text fields.  Now it works.  

Does that make any sense?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<Does that make any sense? >>

  Yes and no.

  Certainly as far as the result, but it also means you have no error handling on your inserts.   Trying to save a record with a zero length should have triggered a run time error.

<<I execute a database rebuild when opening any mdb to assure a complete and correct access structure in the mdb and the tables.  Helps to forestall problems if a database has been damaged over time.>>

  No in that if this is the same code that has been running for years, then I don't understand why it changed.   Nothing in Access has changed in a very long time as far as default table properties.  I'd have to double check, but the last time any real work was done in that area was for 2007 when ACE replaced JET.

Jim.
0
 

Author Closing Comment

by:Skip_Laughlin
Comment Utility
Thanks for the comments.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now