Solved

Null character in Acess 97 ValidationRule after amending with Access 2000

Posted on 2006-11-28
15
421 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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