Passing an Access database to a case sensitive DBMS

[Philippe]

Hi,

I am developping a software that uses by default an Access database, but I
want to allow the user to create copies of this database on Oracle,
PostGreSQL and other DBMS, keeping the same structure and relations (or
foreign keys), and to access the data of these databases transparently.

To copy the data of my Access database to Oracle or PostGreSQL which are
case sensitive DBMS, I have first to check if the case of the Access database
is case consistent on each side of the relations. In fact, as Access is not
case sensitive, it allows to have a case in the referencing table which is
different from the case in the referenced table. And when copying the Access
data to the case sensitive DBMS, I get an error as those values don't comply
with the foreign keys that are defined.

Is there a way to ensure that an Access database is consistent on the case
its uses on both sides of its relations ?

 

[david epsom dot com dot au]

Use artificial keys instead of text keys. That is, use
a number, keyed to another table with the text value.

Or you might consider using 'binary' fields instead
of 'text' fields in your access database for related
fields.

'binary' fields don't have case defined, so they are
case insensitive. And they probably also don't handle
unicode.

You will have to use ADO, DAO, or DDL to create tables
with 'binary' fields instead of 'text' fields, because
the table designer in Access does not allow you to
create that kind of field.

(david)

 

[Philippe]

Hi David,

Thanks for your answer.

I chose to use Text keys in my indexes from the beginning (2000) and my soft
is already in use by about 300 persons in the world, in the field of
Hydrology. The users can create their databases by themselves (cloning an
existing database), and exchange data between different databases. So it is a
bit late late to change my database structure.

I made that choice because:

- it seemed safer to me to relate the records to a true key value rather
than to an automatic number, in case of a database crash, it allows to
retrieve easily the records related to a key.

- it is possible to retrieve directly information from the tables using
Excel, if you know the main key key name.

- the same keys introduced in 2 different databases produce the same results
for the attached time series, it would not be the same using automatic
numbers.

I know that it is not the best way for efficiency, but we have each time
more potent machines...

The solution I found is to programmatically edit all the fields in the
referencing tables, it rewrites all the values in cascade in the dependant
tables with the same case. But that is when I get the problems with not
enough temporary disk space...

 

[Albert D.Kallal]

If you have some export routines (assuming, the data has to come form
ms-access), then you could use a query, and a expression on the text field
to convert it to all lower (or perhaps upper case)...that would force the
relationships to match....

The solution I found is to programmatically edit all the fields in the
referencing tables, it rewrites all the values in cascade in the dependant
tables with the same case. But that is when I get the problems with not
enough temporary disk space...

Hum, how large are these tables? I would a compact and repair right before
you start this...but, if you taking small tables, of only 100,000 to 200,000
records, with such small tables, you should be receiving temporally disk
space errors.

If you data sets are actually very large, and much much larger then the
above, then I would consider simply exporting the data, but use a query with
a expression/function on the fields that needed for the relationship...that
way, you don't touch the data in the mdb file...but ONLY when you export it
for use in other database engines. And, perhaps a export option would send
the data to a new/temp mdb file anyway. You certainly want to keep any type
of "temp" type data processing outside of the production database to reduce
bloat problems...

You should try and fix/track down/ change your approach.....as you need to
eliminate that running out of disk space. (or, perhaps you data sets are so
large, you have exceeded the capacity of ms-access now?).

 

[david epsom dot com dot au]

See thread
"Not enough space on temporary disk, MaxLocksPerFile, MaxBufferSize"

It appears that there is a 250MB max size on the Jet Temp database.

The Jet Temp database is supposed to be used when you exceed the
available memory.

The available memory seems to be limited by the Max Buffer Size.

Jet is supposed to schedule immediate write backs when you exceed
the Max Buffer Size.

Also, Jet is supposed to do partial commits when it runs out of
locks.

It seems that the documentation is a best poor, and at worst just wrong.

(david)