Exporting tables to CSV - unparsable records (1 Viewer)

iea

New member
Local time
Today, 15:27
Joined
Nov 18, 2019
Messages
5
Hi,

I have set up a button linked to the "runsavedimportexport" function so that I can export my tables to CSV files in one click.

This was working fine for a while but after I recently expanded the size of my database, I am now encountering export errors for one particular table. Oddly, I only encounter the errors when I use the runsavedimportexport function to export it. When I export the table manually (using the Export to text file function in the External Data tab in the ribbon) I don't encounter errors.

The errors I'm getting are "Unparsable records" errors and it's not clear why.

Has anyone else encountered export errors when using a macro to export a table to CSV?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:27
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! If you can export the table manually, have you considered saving the export as a new saved export to see if you can run the new saved export using code without any errors? If so, you could maybe just replace the old saved export with the new one.
 

iea

New member
Local time
Today, 15:27
Joined
Nov 18, 2019
Messages
5
Hi DBGuy,

Thanks for the warm welcome and for your suggestion.

I've since realised that the error is also occurring when I run the export manually from the front end, but not the back end. In other words, when I run the export from the back end, It seems to parse all the records perfectly but when I run the export from the linked table in the front end, either through the macro or manually, the export contains unparsable records.

Grateful for any advice as to why this might be the case.
 

vba_php

Forum Troll
Local time
Today, 09:27
Joined
Oct 6, 2019
Messages
2,884

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:27
Joined
Sep 12, 2006
Messages
15,613
there may be some corrupt records in the file, often manifested by "chines" characters in text/memo fields. They seem to affect some bit not all operations. They can be hard to sort out, so it's worth checking. It can be hard to locate the problems in a big file, though.
 

moke123

AWF VIP
Local time
Today, 10:27
Joined
Jan 11, 2013
Messages
3,849
the experts that respond to the articles listed in my link also often mention corruption as a possible cause.

Your link does not work. says "About 0 results (0.39 seconds)"
 

vba_php

Forum Troll
Local time
Today, 09:27
Joined
Oct 6, 2019
Messages
2,884
Your link does not work. says "About 0 results (0.39 seconds)"

works for me! i just stripped all the junk that google adds in, and left the query string only! here it is (remove the spaces in it, and maybe you can go to it):

https:// www. google. com / search?q=ms+access+Unparsable+records+runsavedimportexport
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:27
Joined
Oct 29, 2018
Messages
21,357
Hi DBGuy,

Thanks for the warm welcome and for your suggestion.

I've since realised that the error is also occurring when I run the export manually from the front end, but not the back end. In other words, when I run the export from the back end, It seems to parse all the records perfectly but when I run the export from the linked table in the front end, either through the macro or manually, the export contains unparsable records.

Grateful for any advice as to why this might be the case.
Hi. If you can run the import from a different Access container (the BE), then corruption of the FE, as already mentioned, might be the reason for the problem. Can you create a new FE linked only to the problem table and try to import from there? If it works, I would just move (import) all the other objects from the old FE into this one.
 

iea

New member
Local time
Today, 15:27
Joined
Nov 18, 2019
Messages
5
Hi all,

I managed to run the export successfully from the backend so I think DBGuy was right - there must have been some corruption in the front end.

Is there any reason why I shouldn't just add a form with a button to the back end to run the 30-or so exports in one go from there from now on? If not, I think that's what I'll do.

Thanks again everyone.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:27
Joined
Oct 29, 2018
Messages
21,357
Hi all,

I managed to run the export successfully from the backend so I think DBGuy was right - there must have been some corruption in the front end.

Is there any reason why I shouldn't just add a form with a button to the back end to run the 30-or so exports in one go from there from now on? If not, I think that's what I'll do.

Thanks again everyone.
Hi. You could do that, but as much as possible, we recommend not putting any logic in the back end, only data. If your FE is corrupt, you will probably have to replace it anyway. Once it's fixed, then the export should work on the FE, so there's no need to put it anywhere else. Just my 2 cents...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:27
Joined
Feb 28, 2001
Messages
26,999
In fact, though I agree with theDBguy, I'm going to say it more strongly. If you have a corrupt FE file, you MUST eventually get rid of it because you don't know what else will get hosed by a broken file. You are flirting with DB corruption going widespread if you do nothing to the FE and it WAS corrupted.

If a Compact & Repair of the FE doesn't help, then look in this forum for other ways to fix a corrupted DB file.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:27
Joined
Sep 12, 2006
Messages
15,613
With a single corrupt record, sometimes you have to copy and paste the preceding and succeeding records into a new table, so that you lose the corrupt record. You then have to replace the corrupt record.

Now depending on the table and your data you might need to remove temporarily RI, and then replace it after you repair the data. It's not always easy - it's not trivial, and it does need to be done asap to avoid worse problems. If you let these problems proliferate you may end up with an unuseable database.

I think we should also note we are all assuming you have a split database, so we are talking about repairing data tables.
 

iea

New member
Local time
Today, 15:27
Joined
Nov 18, 2019
Messages
5
Thanks for all of the advice. I had been exporting from the backend in the short term but it sounds like I really need to work out what is going on that is preventing the front end from successfully running the export script so I'm going to try DBGuy's suggestion to isolate the problematic table in a new front end and then see if I can run the export from there, before bringing over all of the other non-problematic elements.

Gemma-the-husky, if I do have corrupt records in the back end tables, do you know why would they export successfully from the backend and not the frontend?
 

Cronk

Registered User.
Local time
Tomorrow, 01:27
Joined
Jul 4, 2013
Messages
2,770
Re #10
I managed to run the export successfully from the backend so I think DBGuy was right - there must have been some corruption in the front end.
That implies there is nothing wrong with any of the records in the table being exported and the problem is with the FE.


I suggest you create a new blank db and import everything from the existing FE. Anything too badly corrupted will not be imported and any other problems should be rectified.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:27
Joined
Feb 28, 2001
Messages
26,999
Adding to Cronk's comments - if you do that export and DID have a corrupt object, I believe Access would report errors in attempting to do the import AND it would name the specific object that failed. Which would perhaps help confirm that something was wrong with that object.

However, I have another point to consider. If the FE and BE are on separate machines, and you said you did a direct export of data from the BE rather than using the linked tables in the FE, then my question is, did you open the BE directly from the FE machine or did you have a version of Access on the host machine of the BE and use that copy of Access? Because that answer would rule out or point to issues with references depending on which answer you gave.
 

iea

New member
Local time
Today, 15:27
Joined
Nov 18, 2019
Messages
5
Hi all,

So I did some detective work and found that there was likely to be some corrupted data after we imported a few thousand records into the DB and the record id numbers went into an autonumber field (the primary key field).

Anyway, we remade the primary key field and now we are able to export from the front end to a csv file without any parsing errors.

I'm still not sure why the problem only occurred in the front end and not the back end but I'm happy it seems to be solved.

Thanks for the ideas.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:27
Joined
Oct 29, 2018
Messages
21,357
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom