Link to home
Start Free TrialLog in
Avatar of aeolianje
aeolianjeFlag for United States of America

asked on

Problem updating Access table using VB code

I have a database that imports Excel files, updates data (using queries) and parses the data based upon filter criteria.

Different internal tables are created and a history table is maintained for audit-tracking purposes.

The main functions are working - however, the history table has blank lines being inserted and one of the fields being blanked out when the forms are closed.  I can not figure out where the problem is.  What am I overlooking?

I have attached the process overview and related code.

Thanks for the help!!!  
jeProblem-Updating-History-Table.pdf
Avatar of SStory
SStory
Flag of United States of America image

I haven't read all of the code in fine detail, but just glancing I see this line:
stsql = "INSERT INTO tbl
_Import_History " & _
"( [ApplicationName], [Filename], [ImportFileName], [ImportHistoryName], [File_Rec_Count], [Import_Type]) " & _
"Values( " & """" & stAppName & """" & "," & """" & stfile
name & """" & "," & _
"""" & stpasstablename & """" & "," & """" & stinputtablenamedt & """" & "," & import_rec_count & "," & """" & stimporttype &
"""" & ");"

Open in new window


'I see no checking to see if stAppName is "".  Before telling it to RunSQL and insert a line I would do something like
if trim(stAppName)<>"" then
   'runsql here
end if

Open in new window


At each location where there is an INSERT command, check the variables to be sure they aren't blank. Perhaps there is a final line in your spreadsheet that gets imported as blank.  If you don't won't NULL data, don't allow it.
Avatar of aeolianje

ASKER

stAppname is always populated...
I see no check for that. Have you somehow done a breakpoint to verify this, or are you assuming? I have often assumed, set a breakpoint and been surprised.  If you export the offending XLS files to CSV do any of them end with only commas? If so there is a blank line in the XLS file.
My opinion is that you will only get a blank record through a INSERT in that code and that will only happen if the variables supplying the values are blank some of the time.  If you want to test that theory with a copy of the db, change the code in the copy to INSERT they same record over and over as actual values without variables and see if any blank lines are in the db. I'll bet there won't be.  Only through testing and debugging in this manner will you find the solution.  Step one is see where inserts take place. That will be RunSQL, then see the SQL commands issued.

Or before calling RunSQL, output the SQLString variable to debug.print or to a text file then review it. Look for where the values are blank.
Thanks for the suggestions..... A valid (populated) record does get inserted.  For some reason, it inserts another blank line (but only when the form is closed).  I've put in several checkpoints to validate variables and it only seems to execute the INSERT once -- so I'm not sure when the other 'blank' line is being inserted.  The other problem is why the ImportHistoryName field is being blanked out.  But all of this only happens when the form is closed - which is peculiar because the close function doesn't have anything else in it.....  I wonder if somehow it's going to the 'next record' in the table before it closes out and then saves the blank record.

I'll keep checking variable values and see if I'm missing something else.

je
ASKER CERTIFIED SOLUTION
Avatar of SStory
SStory
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have been able to use a workaround.

I don't really care about the blank lines -- I have a query to clean them up.

As far as the ImportHistoryName field being blanked-out on the first record -- I insert a Test record as the first record so it doesn't matter if that field gets wiped out.

Both of these workarounds seem to be ok.

I'll still hunt around for the cause -- but need to move on to other things.

Thanks again.
je
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: SStory (http:#a40114347)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer