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?

[Webinar] Streamline your web hosting managementRegister Today

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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.
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.
newbie46Author Commented:
I will try that.  Thanks.
All Courses

From novice to tech pro — start learning today.