Solved

Null character in Acess 97 ValidationRule after amending with Access 2000

Posted on 2006-11-28
15
407 Views
Last Modified: 2008-02-01
I have an application with the usual front-end/back-end split. To slightly complicate matters, the front-end is Access 2000, whereas the back-end is Access 97.

I've recently added an update procedure to the front-end to programmatically 'tweak' the validation in various back-end tables (using DAO).  Unfortunately, whenever the Access 2000 front-end manipulates the ValidationRule property of a field in the Access 97 back-end, it appends a vbNullChar character to the end of the string.

Bizarrely, this null character is only noticed by Access 97 - where it causes a syntax error whenever the validation rule is evaluated.

Does anyone know how I can remove the unwanted character, USING ACCESS 2000?
0
Comment
Question by:JezWalters
  • 9
  • 4
15 Comments
 
LVL 17

Author Comment

by:JezWalters
ID: 18031199
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.
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18035551
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
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18045558
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18089995
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18090034
JezWalters,

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

jeffc

 
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18091764
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.
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18092024
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
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18093489
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
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18100248
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.
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18100450
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 18102507
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!
:)

0
 
LVL 17

Author Comment

by:JezWalters
ID: 18102635
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.
0
 
LVL 17

Author Comment

by:JezWalters
ID: 18102697
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.
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 18368145
PAQed with points refunded (400)

DarthMod
Community Support Moderator
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 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

15 Experts available now in Live!

Get 1:1 Help Now