Solved

vb6 and Access

Posted on 2014-07-18
7
234 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
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 57
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 57
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel - Save a copy of work book 13 88
MS Date Picker 64 bit 32 bit issue 12 60
Added a column screws up code 5 63
CONDITIONAL MAX STATEMENT IN T-SQL 3 72
This article will show, step by step, how to integrate R code into a R Sweave document
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

840 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