Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

vb6 and Access

Posted on 2014-07-18
7
Medium Priority
?
247 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 46

Expert Comment

by:aikimark
ID: 40206186
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 58
ID: 40206199
<<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
ID: 40206375
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 40206400
<<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
ID: 40206487
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40208739
<<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
ID: 40209695
Thanks for the comments.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

885 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