Solved

Null character in Acess 97 ValidationRule after amending with Access 2000

Posted on 2006-11-28
15
395 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

14 Experts available now in Live!

Get 1:1 Help Now