Monday, April 23, 2007

Access 2007 Bug

Access 2007 breaks previous text import specs if you deal with dates (at least of format yyyymmdd) in the text file.

The specs imported these fields properly in Access 2003. In 2007, you get an error
"The specification XML failed to validate against the schema. There is an error in the following line of the XML document: ."
Those specs that have no date fields seem to import without problems.

I posted a question about this on the Microsoft newsgroups, which should be the right place to post. No answers.

Then I moved on to the Joel on Software forums, where I often have better luck. Albert D. Kallal, MVP for Access, was able to reproduce the issue and filed a bug report.

My guess is that this is not very high priority for a bug fix, since I don't see anyone else complaining about it on any forums. Is it really that unusual to import text files? I would have thought thousands of people would be affected by this...

15 Comments:

At Fri May 11, 06:22:00 am , Anonymous Anonymous said...

oh, i feel your pain

dates of yyyymmdd format are the only ones that matter!

(assuming of course you really mean yyyyMMdd unless minutes are so important to you)

here in Aus we are doomed to face many date format issues in all US software and software platforms. even Joel sposlsky stuffs it up most of the time, ;-)

lb .; secretGeek.net ;.

 
At Fri Aug 31, 07:24:00 am , Anonymous Anonymous said...

I can not import text file, delimited by certain characters, in Access 2007 which I was able to import it without any problem in access 2003 and previous version.

I do not have any date field in this text file even. They all are simple TEXT type field. I get this error message " The specification XML failed to validate against the schema. There is an error in the foloowing line of the XML document: |."

Anybody ???

Thanks in advance

 
At Mon Oct 15, 07:52:00 am , Blogger Pat said...

I agree, I have also reported exact same bug to Microsoft technical support. We are using this feature a lot at my work and so this became very critical. As usual Microsoft has assigned me bug ID. Its been a while, more than 2 months, and there is no follow up from them.

 
At Wed Dec 05, 03:51:00 pm , Anonymous Cheryl said...

Yes, I am having the same problem and went looking to see if there is a fix or work around. I am *not* a happy Canadian University camper....

 
At Fri Dec 07, 07:11:00 am , Anonymous Kathy said...

I have a workaround, though it was NOT quick and easy. I changed my import specification to export the date field as a text field. Note that when I tried to just change the data type field from date to text, it still gave me the XML error. However, when I recreated my whole import specification from scratch, this time using text instead of date for that position, it worked. After importing the text file, I used the CDate function in Access to convert the text field into a date field.

 
At Sun Jun 29, 01:14:00 am , Blogger Martijn said...

Same story here,

"0130051234","EUR",20070301,"D",57.92,"0000000000","064 ALDI ALPHEN AAN DE",20070301,"BA","","Pinautomaat 10:57 pasnr. 001","","","","",""

is a record from a file that won't import anymore. I compared all the import definition tables and all seems equal.

Martijn

 
At Thu Jul 03, 05:32:00 pm , Anonymous Duane in Salem, OR said...

Just upgraded (?) to 2007 and within one day ran into this bug. Posted it on the MS Community board and Scott replied with this link.
Add me to the list.

 
At Fri Nov 14, 12:47:00 pm , Anonymous Anonymous said...

Found a decent solution... when you import the text file using the "import text wizard", it breaks with that XML error.
However, if you create a macro using the TransferText action, and use the same import specification used in previous Access versions... it works!

 
At Wed Dec 03, 09:31:00 am , Anonymous Anonymous said...

Can you give some further information about this macro you created? I'm not sure how to do a transfer action.

I have to find a way to make this work!

 
At Tue Dec 23, 01:07:00 pm , Blogger C. Scott Smith said...

I've also experienced this problem and have found no solution, aside from importing using an older version of Access (which is what I've been doing).

 
At Thu Apr 02, 08:57:00 am , Anonymous Anonymous said...

So happy to see I'm not the only one! If you create a macro with the import spec in an older version it will work in the newer version via the compatibility mode. For now it'll be cvdate for me. WTG Microsoft!

 
At Thu Jul 30, 05:31:00 am , Anonymous Anonymous said...

If you're able to alter the delimiters, I changed mine from Concordance delimiters to pipes (|) and carots (^). The import worked perfectly.

 
At Fri Sep 03, 07:22:00 am , Anonymous Anonymous said...

So... Did this ever get fixed? Anyone ever hear anything? It's now been three years since this blog was posted, and I just ran into this issue while trying to export a table to Excel (2007)...

 
At Tue Jan 04, 11:54:00 am , Anonymous Anonymous said...

I was getting that error message when I used "Export to Excel" on a table I'd created using a "Make Table" query.

My table was called "A & B". When I removed the ampersand (&) from the table name and called it "A and B", I was able to export it.

God only knows what posessed me to think of removing the ampersand. I could have been there forever and not figured it out.

 
At Wed Nov 09, 02:56:00 pm , Anonymous Anonymous said...

I had the same issue as the previous post: "I was getting that error message when I used "Export to Excel" on a table...", although it was a regular query, not a make table query.

Renaming the query to avoid the "&" symbol seems to have fixed it.

Thanks!

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home