How do I preserve special characters in export from Access to Excel?

When I create a table in an Access mdb database and populate it with special characters such as Æ or ß, these characters are exported to the Excel spreadsheet, without a problem.

The problem is this: We use adps with the tables and queries in SQL Server 2008. In the VB code of the Access reports, we are using the DoCmd.OutputTo command to carry out the export to Excel. Carrying out the export in this way, the special characters are not preserved and appear in the Excel spreadsheet as question marks or strange characters.

How can these special characters be preserved when exporting to Excel using the DoCmd.OutputTo command?
Is there something that can be done from Excel to retrieve these special characters that don't show up correctly after the export, to reset them, so to speak?

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.

newbie46Author Commented:
I should have mentioned that when I created the table using an mdb and populated it with special characters and the special characters were seen in the Excel spreadsheet, I used the export to Excel feature from the Access Ribbon.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure why your OutputTo would munge the characters; perhaps it's the translation between SQL Server and Access?

That said, if you take the data from the remote Server table and move it frst to a local Access table, does the Export then work? You can easily move data to a local temporary table using SELECT INTO:

Currentdb.Execute "SELECT * INTO YourTempTable FROM YourLinkedSQLTable"

The only caveat is that YourTempTable must not already exist. You can force a Delete first by doing this:

Currentdb.Execute "DROP TABLE YourTempTable"

Once your data has been moved to YourTempTable, then try your Export using that new table.

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
newbie46Author Commented:
I will try that.  Thanks.
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 Access

From novice to tech pro — start learning today.