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.
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.
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)
If externalparentRS.EOF Then
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
If err.Number <> 0 Then
updateExternalDB = err.description
updateExternalDB = errorStr
Set parentRS = Nothing