Access 2003 Unicode

A real challenge here ... I am building an access 2003 database on a Windows XP platform which takes data from another non access database via an ODBC connnection.

I want to have a series of linked tables to the source database.

Here's where the issue comes in ... I have established the ODBC connection all fine, but I cannot link the tables, I can however import the tables which work all fine, but these tables definitely need to be linked.

The other thing I noticed was that in the source database the table names contain a "." so when it's linked it keeps the same name, but when it's imported it changes the "." to an underscore eg... FFUsers.AllAssignments to FFUsers_AllAssignments - could this be the reason (if so what can I do about it, I assume changing the name won't help as access has already done this for me?)

Access gives me no error messages but overwrites the data with "#deleted" in each field. So I tried excell and got a bit of info from that ... The error message was " system does not support conversion between unicode and the requested character set, substituting the current ANSI code page".

I spoke to the makers of the source database who couldn't work this one out but said that it was written (or outputted (can't remember which as my brain was fried by this stage)) in sybase UCA collation sequence

Please help - if I was allowed to award 1000,000 points for this I would!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by "cannot link the tables"? Do you get an error, or does it simply fail, or are you unable to proceed at some point? Describe exactly what's happening (and what your "non access database" is) and we can probably be a bit more focused.

However, I'd bet is due to the fact that Access cannot use a period in the table name:

Can the owners of that source database define a "view" (i.e. a query) that you could link to, and name that query without special characters?

correlateAuthor Commented:
The database is a piece of software called FileFinder 9

As far as what's happening on the links... It appears to set up ok and you can open it etc, but the data inside is replaced with "#DELETED".

The owners could produce a query that I could link to but I would prefer to avoid this for a whole host of reasons.
Gustav BrockCIOCommented:
One or more of the fields probably holds a data type not supported by Access.
Ask the owner to create a query or view with only the fields you need. Note that the ID must be included if the view should be updatable. If this, however, is a BigInt (larger than Long) it cannot be used.

correlateAuthor Commented:
Dear All

Thanks for the suggestions. I had another go at it today and realised that what I had been doing was to assign a unique record ID to the source database's unique record ID field and it didn't like it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.