Solved

ADO Update Multiple Access Databases / Error with Null Date Value

Posted on 2010-08-24
16
762 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
ID: 33510927
<<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
ID: 33511178
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
ID: 33511338
                   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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:FLOG51
ID: 33511677
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
ID: 33511721
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
ID: 33511843
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
ID: 33512040
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
ID: 33512264
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
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 33514702
               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
ID: 33517368
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
ID: 33517485
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
ID: 33517498
Solution not provided but must award points for effort in attempting to assist.

Thanks OM Gang.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33521581
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
ID: 33521585
I guess that should have been 'Good work'.
OM Gang
0
 

Author Comment

by:FLOG51
ID: 33527370
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
ID: 33531411
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

856 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