Null character in Acess 97 ValidationRule after amending with Access 2000

By the way, I can't simply upgrade the back-end to Access 2000 (even although this DOES actually make the problem go away!), as I have to support a series of legacy and third party applications which only work with Access 97.

I'm trying this sort of thing in the Access 2000 front-end (using DAO):

  Dim wrkWorkspace As Workspace
  Dim dbsDatabase As Database
  Dim tdfTableDef As TableDef
  Dim fldField As Field

  Set wrkWorkspace = DBEngine.Workspaces(0)
  Set dbsDatabase = wrkWorkspace.OpenDatabase("C:\Temp\My97Database.mdb")  ' Access 97 database!
  Set tdfTableDef = dbsDatabase.TableDefs("MyTable")
  Set fldField = tdfTableDef.Fields("MyField")
  fldField.ValidationRule = ">0"  ' For example

After executing this code, I get the message "The expression you entered contains invalid syntax" when opening MyTable using Access 97

I'm not really familiar with ADO, but has anyone out there tried modifying tables on-the-fly using ADO (instead of DAO) who could offer some advice?

I'm not holding my breath, but perhaps using a different software interface might help matters!

Here is a great MS link that explains Proting (converting) DAO to ADO.

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnardao/html/daotoado.asp

It actually shows the same code in both DAO and ADO!
:)


Good luck!

jeffc

JezWalters,

Also see here for a function to strip out the null character:
http://www.geocities.com/practicalvb/vb/strings/ztrim.html

jeffc

 

The function you suggest for stripping out null's won't work because my Access 2000 front-end doesn't 'see' the null terminator it is erroneously appending - it's only noticed by the Acccess 97 back-end.

I'm after a solutiuon that works in Access 2000.

It looks like Access 2000 CAN in fact detect the erroneous null character but in trying to remove it, it appends a null character to the resultant string!

  Dim wrkWorkspace As Workspace
  Dim dbsDatabase As Database
  Dim tdfTableDef As TableDef
  Dim fldField As Field

  Set wrkWorkspace = DBEngine.Workspaces(0)
  Set dbsDatabase = wrkWorkspace.OpenDatabase("C:\Temp\My97Database.mdb")  ' Access 97 database!
  Set tdfTableDef = dbsDatabase.TableDefs("MyTable")
  Set fldField = tdfTableDef.Fields("MyField")
  If Right(fldField.ValidationRule, 1) = vbNullChar Then  ' True
    fldField.ValidationRule = Left(fldField.ValidationRule, Len(fldField.ValidationRule) - 1)  ' Removes null character, then appends a new one!
  End If

Rather odd!
:(

I'm guessing that:
 If Right(fldField.ValidationRule, 1) = " " Then
...failed as well

Perhaps you can do something to look at the ASCII code, using the chr() function.

For example:

SpaceBar ascii values:
Dec=32
Hex=20
Oct=040

worth a try?

jeffc

An extra null character is appended each time Access 2000 sets a field validation rule in an Access 97 table using DAO, causing a syntax error in Access 97 (only).  Oddly, table validation rules don't seem to be affected the same way.

Your suggestion seems to focus on detecting this erroneous condition, which Access 2000 has no problem determining (see my previous posting).  The crux of the issue is how to correct the fault somehow, using Access 2000.

After a lengthy trawl through various documentation, I finally had a go at correcting the validation rules using ADO - and it worked!  Bizarrely, the null character is only detected by DAO (not ADO) and can only be corrected using ADO (not DAO)!

It turns out that reassigning the corrupted field validation rule to its current setting using ADO strips the spurious null.  Having experimented a little it's quite a lot quicker to reassign all column validation rules, whether they end in null or not (thus avoiding the DAO overhead):

    Dim catCatalog As ADOX.Catalog
    Dim colColumn As ADOX.Column
    Dim tabTable As ADOX.Table

    Set catCatalog = New Catalog
    catCatalog.ActiveConnection = "Provider=" & CurrentProject.Connection.Provider & ";" & _
                                  "Data Source=C:\Temp\My97Database.mdb"
    On Error Resume Next  ' Ignore unassignable system tables/fields
    For Each tabTable In catCatalog.Tables
      For Each colColumn In tabTable.Columns
        colColumn.Properties("Jet OLEDB:Column Validation Rule") = colColumn.Properties("Jet OLEDB:Column Validation Rule")  ' Reassign to strip trailing null character
      Next
    Next
    On Error GoTo 0

Great, glad you got it worked out.

<the null character is only detected by DAO (not ADO) and can only be corrected using ADO (not DAO)!
... another reason for us old timers to convert to ADO!
:)

Just to show the sort of weird bugs that are out there, when I tried checking which field validation rules were corrupted using DAO before correcting them with ADO, I got the sporadic error 'Unspecified error'!  So much for only tweaking the rules that needed fixing.

I guess that this was some sort of DAO/ADO concurrent access clash and is another reason why I chose to tweak all column validation rules, irrespectively.

As for ADO vs. DAO, my problem is more a result of having an Access 2000 front-end linking to tables in an Access 97 back-end.

I suspect that Microsoft didn't put too much thought into this combination, expecting everyone to upgrade across the board.