Solved

ADO Update Multiple Access Databases / Error with Null Date Value

Posted on 2010-08-24
16
759 Views
Last Modified: 2013-12-25
Hi All

Have a problem with updating a synchronised Access DB, when a Date is unchecked from a Date picker in a VB6 Windows Forms Application. Also this process works fine with all other Field Updates except where Dates are Empty.

When a NULL Value is passed in (Date changed from Some Date to Nothing) the rs.Update does nothing to the value in the second DB.

If I attempt to trap the value and update it to an Empty string or other I get the following error.
ADODB.Field
Description: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.


And if I declare a variable to catch the adoDB.field Type, I get
Description: Invalid use of Null

This is Old Code from a previous developer and to be honest I am struggling to understand the best way to fix it.

I have attached the code below and added comments to the relevant areas. The problem occurs specifically with field.type (7) i.e. 'adDate'.

By the way the field Definition in the Access Table is set to "Required = No". for this EndDate Field.

My gut feeling is I need to revert to some form of "Update" SQL Statement or to even delete the row from the External DB and to just reinsert the record if a change is made.

I have spent many hours trying to track down the best way to deal with this so any help would be appreciated.

Cheers FLOG51


Private Function updateExternalDB(ByVal auditId As String, ByVal externalDB As adoUtil, ByVal tablename As String, ByVal key As String, ByVal oldKey As String) As String

    On Error Resume Next

    Dim errorStr As String

    Dim searchKey As String



	Dim parentRS As Object

	' gDBUtility is declared as Global and .getParentRSA routine Successfully retrieves field Data from Parent Database Table

	' This is carried out after the Parent Table has Successfully been updated with Empty Values for relevant dates.

	Set parentRS = gDBUtility.getParentRSA("select " & gDBUtility.getMyTableExportFields & " from myTable where cardnumber = " & key)



	If Not parentRS Is Nothing Then

	  If parentRS.EOF = False Then

	    If errorStr = "" Then

	      Dim externalparentRS As Object					

	      Set externalparentRS = externalDB.getParentRSA("select * from " & tablename & " where cardnumber = " & key, True)

	        If externalparentRS Is Nothing Then

		      Set externalparentRS = externalDB.getParentRSA(tablename, True, , True)

		      externalparentRS.addNew

	        Else

		      If externalparentRS.EOF Then

			    externalparentRS.addNew

		      End If

	        End If

	

	          Dim f As adoDB.field

	          Dim fieldname As String

	          For Each f In parentRS.fields

		        fieldname = f.name				

		        If Not IsNull(parentRS.fields(fieldname)) Then

			      If parentRS.fields(fieldname) <> "" Then

			        ' This line below is where the NULL date value is passed and never inputs at externalparentRS.Update 

				     externalparentRS.fields(fieldname) = parentRS.fields(fieldname)

				    If err.Number <> 0 Then

					  'Debug.Print "Failed: " & err.description

				    End If

			      End If

		        End If

	          Next

	          externalparentRS.Update

	          If err.Number <> 0 Then

		        updateExternalDB = err.description

	          End If

	    Else

	      updateExternalDB = errorStr

	    End If

	  End If

	End If

	parentRS.Close

	Set parentRS = Nothing

End Function

Open in new window

0
Comment
Question by:FLOG51
  • 8
  • 8
16 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
<<And if I declare a variable to catch the adoDB.field Type, I get
Description: Invalid use of Null
>>

Even if you declare the variable as a Variant?
Dim varFieldVal As Variant
varFieldVal = parentRS.fields(fieldname)

Also, the outer conditional here does not have an Else part for when the condition is False, e.g. when the value is Null.  This will, by design, skip the update of any field in the parent recordset when the value is Null.
                    If Not IsNull(parentRS.fields(fieldname)) Then
                        If parentRS.fields(fieldname) <> "" Then
                          ' This line below is where the NULL date value is passed and never inputs at externalparentRS.Update
                             externalparentRS.fields(fieldname) = parentRS.fields(fieldname)
                            If err.Number <> 0 Then
                                'Debug.Print "Failed: " & err.description
                            End If
                        End If
                    End If

OM Gang
0
 

Author Comment

by:FLOG51
Comment Utility
Hi OM Gang

Sorry cut out a bit of useless logging code and threw my conditionals out, here it is again trimmed down.

That line in my question should have read "And if I declare a variable to catch the adoDB.field Value, I get
Description: Invalid use of Null". Instead of Type I meant Value,

Yes I have attempted to Declare a Variant but still get same Null error.

By the way the rs.Open sequence is "rs.Open sql, gConn, , adLockOptimistic, adCmdTable"

gConn being adoDB.Connection

FLOG51


Private Function updateExternalDB(ByVal auditId As String, ByVal externalDB As adoUtil, ByVal tablename As String, ByVal key As String, ByVal oldKey As String) As String

    On Error Resume Next

    Dim errorStr As String

    Dim searchKey As String



	Dim parentRS As Object

	' gDBUtility is declared as Global and .getParentRSA routine Successfully retrieves field Data from Parent Database Table

	' This is carried out after the Parent Table has Successfully been updated with Empty Values for relevant dates.

	Set parentRS = gDBUtility.getParentRSA("select " & gDBUtility.getMyTableExportFields & " from myTable where cardnumber = " & key)



	If Not parentRS Is Nothing Then

	  If parentRS.EOF = False Then

	      Dim externalparentRS As Object					

	      Set externalparentRS = externalDB.getParentRSA("select * from " & tablename & " where cardnumber = " & key, True)

	        If externalparentRS Is Nothing Then

		      Set externalparentRS = externalDB.getParentRSA(tablename, True, , True)

		      externalparentRS.addNew

	        Else

		      If externalparentRS.EOF Then

			    externalparentRS.addNew

		      End If

	        End If

	

	          Dim f As adoDB.field

	          Dim fieldname As String

	          For Each f In parentRS.fields

		        fieldname = f.name				

		        If Not IsNull(parentRS.fields(fieldname)) Then

			      If parentRS.fields(fieldname) <> "" Then

			        ' This line below is where the NULL date value is passed and never inputs at externalparentRS.Update 

				     externalparentRS.fields(fieldname) = parentRS.fields(fieldname)

				    If err.Number <> 0 Then

					  'Debug.Print "Failed: " & err.description

				    End If

			      End If

		        End If

	          Next

	          externalparentRS.Update

	     If err.Number <> 0 Then

		   updateExternalDB = err.description

	     End If

	  End If

	End If

	  parentRS.Close

	Set parentRS = Nothing

End Function

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
                   If Not IsNull(parentRS.fields(fieldname)) Then
                        If parentRS.fields(fieldname) <> "" Then
                          ' This line below is where the NULL date value is passed and never inputs at externalparentRS.Update
                             externalparentRS.fields(fieldname) = parentRS.fields(fieldname)
                            If err.Number <> 0 Then
                                'Debug.Print "Failed: " & err.description
                            End If
                        End If
                    End If

If you insert a break point at the conditional above and then step through the code what happens?
The first statement (above) should evaluate to False when parentRS.fields(fieldname) is Null
the code should then continue to the last End If statement (above)
the code should then continue to the Next staement (not shown above), jump back to the For Each statement and continue with the next field in the parentRS recordset.
If I am correct then the update statement never executes.

OM Gang


Also, for testing and to assist with debugging you may want to add an error handler into the procedure

Private Function updateExternalDB(ByVal auditId As String, ByVal externalDB As adoUtil, ByVal tablename As String, ByVal key As String, ByVal oldKey As String) As String

    'On Error Resume Next

On Error GoTo Err_updateExternalDB

    Dim errorStr As String

    Dim searchKey As String



	Dim parentRS As Object

	' gDBUtility is declared as Global and .getParentRSA routine Successfully retrieves field Data from Parent Database Table

	' This is carried out after the Parent Table has Successfully been updated with Empty Values for relevant dates.

	Set parentRS = gDBUtility.getParentRSA("select " & gDBUtility.getMyTableExportFields & " from myTable where cardnumber = " & key)



	If Not parentRS Is Nothing Then

	  If parentRS.EOF = False Then

	      Dim externalparentRS As Object					

	      Set externalparentRS = externalDB.getParentRSA("select * from " & tablename & " where cardnumber = " & key, True)

	        If externalparentRS Is Nothing Then

		      Set externalparentRS = externalDB.getParentRSA(tablename, True, , True)

		      externalparentRS.addNew

	        Else

		      If externalparentRS.EOF Then

			    externalparentRS.addNew

		      End If

	        End If

	

	          Dim f As adoDB.field

	          Dim fieldname As String

	          For Each f In parentRS.fields

		        fieldname = f.name				

		        If Not IsNull(parentRS.fields(fieldname)) Then

			      If parentRS.fields(fieldname) <> "" Then

			        ' This line below is where the NULL date value is passed and never inputs at externalparentRS.Update 

				     externalparentRS.fields(fieldname) = parentRS.fields(fieldname)

				    If err.Number <> 0 Then

					  'Debug.Print "Failed: " & err.description

				    End If

			      End If

		        End If

	          Next

	          externalparentRS.Update

	     If err.Number <> 0 Then

		   updateExternalDB = err.description

	     End If

	  End If

	End If

	  parentRS.Close



Exit_updateExternalDB:

	Set parentRS = Nothing

    Exit Function



Err_updateExternalDB:

    MsgBox Err.Number & ", " & Err.Description, , "Error in function updateExternalDB"

    Resume Exit_updateExternalDB

End Function

 

Open in new window

0
 

Author Comment

by:FLOG51
Comment Utility
Hi
Just a quickie while I look at your suggestion, as I stated in initial question I have pulled a lot of error trapping and logging code out to try and cut down the length of the Question, it is there just haven't included it to simplify.

FLOG51
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Oh, OK.  I saw the On Error Resume Next statement and figured it may be keeping you from seeing any errors.
OM Gang
0
 

Author Comment

by:FLOG51
Comment Utility
Hi OM Gang

The code definitely executes I have breakpointed and stepped through, also made changes in Form that successfully got to the external DB.

The line of code in the FOR NEXT LOOP

"externalRS.fields(fieldname) = parentRS.fields(fieldname)"

Doesn't get updated when the new field.value from the Parent is Null, but everything else does get Updated.
There are over 80 fields updated in this code however the problem arises if one of the Values in the Parent RS is deleted and so is now NULL or and Empty String.

Also I just realised I did a find a Replace and the variable naming for Parent and External got a little to close for ease of reading in the initial sends, I have renamed the RS and Functions to replicate their Origin. Code Below.
I have always had some error code that has been picking up the Descriptions I posted initially just have it commented out for the time being. 'Debug.Print "Failed: " & err.description

FLOG51

Private Function updateExternalDB(ByVal auditId As String, ByVal externalDB As adoUtil, ByVal tablename As String, ByVal key As String, ByVal oldKey As String) As String

    'On Error Resume Next

On Error GoTo Err_updateExternalDB

    Dim errorStr As String

    Dim searchKey As String



	Dim parentRS As Object

	' gDBUtility is declared as Global and .getParentRSA routine Successfully retrieves field Data from Parent Database Table

	' This is carried out after the Parent Table has Successfully been updated with Empty Values for relevant dates.

	Set parentRS = gDBUtility.getParentRSA("select " & gDBUtility.getMyTableExportFields & " from myTable where cardnumber = " & key)



	If Not parentRS Is Nothing Then

	  If parentRS.EOF = False Then

	      Dim externalparentRS As Object

	      Set externalRS = externalDB.getExternalRSA("select * from " & tablename & " where cardnumber = " & key, True)

	        If externalRS Is Nothing Then

		      Set externalRS = externalDB.getExternalRSA(tablename, True, , True)

		      externalRS.addNew

	        Else

		      If externalRS.EOF Then

			    externalRS.addNew

		      End If

	        End If



	          Dim f As adoDB.field

	          Dim fieldname As String

	          For Each f In parentRS.fields

		        fieldname = f.name				

		        If Not IsNull(parentRS.fields(fieldname)) Then

			      If parentRS.fields(fieldname) <> "" Then

			        ' This line below is where the NULL date value is passed and never inputs at externalRS.Update 

				     externalRS.fields(fieldname) = parentRS.fields(fieldname)

				    If err.Number <> 0 Then

					  'Debug.Print "Failed: " & err.description

				    End If

			      End If

		        End If

	          Next

	          externalRS.Update

	     If err.Number <> 0 Then

		   updateExternalDB = err.description

	     End If

	  End If

	End If

	  parentRS.Close

	Set parentRS = Nothing

    Exit Function

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
I'm confused as to how the statements within the conditional can execute when the value of parentRS.fields(fieldname) is null.
Lets try this and let me know what is output to the Immediate Window for the problematic fields when executed.

                    fieldname = f.name      
                                        Debug.Print parentRS.fields(fieldname)                  
                    If Not IsNull(parentRS.fields(fieldname)) Then
                        If parentRS.fields(fieldname) <> "" Then
                          ' This line below is where the NULL date value is passed and never inputs at externalRS.Update
                             externalRS.fields(fieldname) = parentRS.fields(fieldname)
                            If err.Number <> 0 Then
                                'Debug.Print "Failed: " & err.description
                            End If
                        End If
                    End If
                Next

OM Gang
0
 

Author Comment

by:FLOG51
Comment Utility
Hi OM Gang

Sorry: I see your problem the bit that says '    " This line below is where the NULL date value is passed and never inputs at externalRS.Update "

Formatting of the question has stuffed this up, it is just a comment.

It is all commented out in the code and is executed once after the Next Statement with "externalRS.Update"

FLOG51
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
Comment Utility
               For Each f In parentRS.fields                  'iterate through each field in the parentRS recordset
                    fieldname = f.name                  'assign the name of the current field to a string variable

'here's where I'm getting confused
            
                    If Not IsNull(parentRS.fields(fieldname)) Then               'only proceed if the value of the current field is NOT NULL
                        If parentRS.fields(fieldname) <> "" Then          'only proceed if the value of the current field is NOT AN EMPTY STRING

'how are we getting here if a Null value is being passed????  The conditionals above should prevent this

                          ' This line below is where the NULL date value is passed and never inputs at externalRS.Update
                             externalRS.fields(fieldname) = parentRS.fields(fieldname)           'if we get here then set the value of the externalRS recordset field to the same value as the parentRS field
                            If err.Number <> 0 Then
                                'Debug.Print "Failed: " & err.description
                            End If
                        End If
                    End If
                Next
                externalRS.Update                        'update the recordset
0
 

Author Comment

by:FLOG51
Comment Utility
Hi omgang, sorry I stopped replying it was 2:00am here.

'how are we getting here if a Null value is being passed????  The conditionals above should prevent this

You are right, they do prevent this, and this is the problem I am trying to solve. If I attempt to get past this i.e. pass in a null value I get all the error's that I listed in the first Q. I posted.

I suppose the simple Q to this complex question is:
How do I update an Access Date Field with an Empty value?

And as yet every attempt I have made has proved fruitless: e.g. as a test I have added an Else to the Conditional

If Not IsNull(parentRS.fields(fieldname)) Then    
   etc.
Else
   externalRS.fields(fieldname) = "''"
End if

However this fails with ""Description: Multiple-step operation generated errors. Check each status value."

So I understand that the NULL value's are not entering the Update process, problem is I am not sure how to update these fields to Nothing when they are Empty.

FLOG51


0
 

Author Comment

by:FLOG51
Comment Utility
Hi OM Gang

Got It!

I have changed the line that I tested in the conditional Else from:
externalRS.fields(fieldname) = "''"
to
externalRS.fields(fieldname) = NULL

And it purr's now, no errors all Null values are updated in the access table.

Cannot believe how many threads I found on this without an answer on Google, no-one seems to have dealt with this with a simple answer that I have found.

Thanks for your assistance, I will award the points for effort.

FLOG51
0
 

Author Closing Comment

by:FLOG51
Comment Utility
Solution not provided but must award points for effort in attempting to assist.

Thanks OM Gang.
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
FLOG51, the addition of the Else part to the conditional is what solved the problem and is what I was getting at all along.  Before adding the Else part no fields that contained Null values were being processed and it appears that was by design by the original developer.  Your change allows the procedure to handle Null values passed and process them.  Goog work.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
I guess that should have been 'Good work'.
OM Gang
0
 

Author Comment

by:FLOG51
Comment Utility
Hi OM Gang

I suppose sometimes the art of getting what you need online is asking the right question. As I am a self taught developer I have acquired quite high standards of understanding in the two years I have been running my little software company mostly by reading and interpreting the code I have inherited and online studies.

Unfortunately, I have as yet perfected the art of asking the right question and indeed using correct terminologies.

I thought from my initial email I was asking exactly what I came up with i.e. how to pass a NULL value into a recordset.

From the earliest I understood what the code was not doing i.e. dealing with the NULL values in the Conditional statement, and had attempted to pass in the empty values a few different ways. What threw me was the error message with my attempts 'Invalid use of NULL' that misled me into thinking I couldn't use NULL, which was why I was attempting to pass an empty string into a Date field, not real smart I know.

I think in future I might try to keep the initial question short and to the point. However talking this sort of thing out with someone always helps.  

Thanks Again
FLOG51
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
You need to ask the question that's on your mind at the time.  Often quick questions yield quick answers to solve a problem.  Other times it's the discussion that provides the most benefit.  I also am self-taught.  I began with a project that was handed to me years ago that helped me stumble on EE (because I had no idea what I was doing).  I still use EE as my primary resource often finding what I need by simply browsing through specific topic areas.
Good luck with the rest of your project.
OM Gang
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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

11 Experts available now in Live!

Get 1:1 Help Now