Can ACCESS read blanks in names

[vanHel]

Hi there!
I have a problem with a db that has imported tables from an Oracle db. I imported the tables for performance purposes as direct queries to linked tables take very long in this case ...
Unfortunately the Oracle db contains recordsets for which the primary keys distinguish only through blanks at the end of the expression in the primary key field (eg 'Name1' and 'Name1 '). ACCESS cuts off the blanks at the end and screws the referential integrity.

Is it possible to tell ACCESS not to cut off the blanks at the end or translate the blanks during the import from the Oracle db into a characters ACCESS can read?

I really appreciate help!
Robert

 

[rbinnington]

Hey Robert,

You will need to import the table into Access, update the firlds with the TRIM() function and then apply the primary key and referential integrity.

Let me know if you need any help with this,
Richard

 

[lespaul]

Richard, I think Robert's problem isn't the values in the fields, it's the Field NAMES. My reading of this indicates that Robert's Oracle table has two fields named "NAME1" with the difference being the blank spaces at the end.

Does that sum it up correctly, Robert, or am I completely off base?

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[rbinnington]

Actually, after re-reading it again I think we both got it wrong. The spaces are in the field. Oracle is allowing

[Column 1]
'Name'
'Name '

Access doesn't allow for the space. I think I might have to do with the collation scheme Access uses. I don't know if it is possible to change that.

Are any of these interpretations correct?

Richard

 

[vanHel]

Thanks to both of you!

The last interpretation is correct. The problem is that the primary key column contains values that differ only because of free spaces. As you stated Access doesn´t distinguish these records.
I can solve the problem by linking the tables from the Orcale db, but the network reduces the performance of queries significantly. So that I would prefer to import the tables periodically.

Currently I am trying to solve the performance problem by having serveral queries in one form. So I can open the form and do something else until the form is loaded completely.

 

[traingamer]

I suppose no one has suggested adding a REAL primary key to your Oracle db?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[vanHel]

Unfortunately I don´t have writing access to the Oracle db, but there are primary keys defined.

 

[Tranman]

Robert,
I punched a test into my Oracle database, and was able to do what you are trying to accomplish. Just returned the length of the Oracle field, and then, after inserting it, padded the trim() of the field with spaces out to the length of the original Oracle field.

Of course, this assumes you have only trailing spaces.

It would probably be easier to just write your own import routine in VBA with an oledb connection to the Oracle database. I hate that rinky Access Import wizard...ranks right down there with the rinky csv loader in Oracle.

Tranman