Export Access Table stops Export to Excel

[notconfident]

I have an Access Table called LogTable which is updated from various events within the Database. It is quite a simple table with 5 fields, Number, Text, Date/Time, Number then Text. I can export the table to Excel ok but sometimes the output stops at a particular record (call it Record x) which looks ok. Further records are in the table but dont get exported. If I compact the database new records will be exported but there is a gap in the Spreadsheet from Record x to after the compact date. I assume there is some sort of "end of file" character that I can't see but why does the compacting change things.

 

[Blorf]

Are any of the field auto number or replication ID's? If so, I would suggest using a query that adds a simple value to the auto number, like MyNumber:0+MyAutoNumber to make it just a value.

I have no idea why this would work, but it is the only thing I can think of, and so far no other suggestions.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

Please post record x and record x-1.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[notconfident]

Thanks for the two replies.
To Blorf
The only number fields are Field1 and Field4 but they are just numbers.

To SkipVought

20 Normal Open Pending Count = 1 30/05/2013 13:38 23328 IOMTTMA-D
20 Normal Open Pending Count = 1 30/05/2013 13:49 23328 IOMTTMA-D
20 Quit Button Used to Close 30/05/2013 14:56 23328 IOMTTMA-D
20 Normal Open Pending Count = 4 10/06/2013 20:13 19000 IOMTTMA-A
20 Quit Button Used to Close 10/06/2013 20:13 19000 IOMTTMA-A

Here is a sample of the export to Excel after the Compact and Repair.
Even though there are many records between the 30/5/2013 and 10/06/2013 the export stopped at the 30/05/2013. After the Compact and Repair it starts again on the date of the Compact and Repair (10/06/2013) but misses out all the records in the middle which show in the table.
This is a Copy/Paste from the spreadsheet which may not help but I dont know how I can attach the actual table.

I have just attempted to attach a small database with just the offending table. I hope it works and hope either of you are still interested in my problem.

Thanks

 

[Blorf]

Interesting that it stops where it does. If Excel is interpreting the data as a date, it may be stopping because 30/5/2013 would be invalid, while 10/6/2013 would not.

One test I would suggest is again, exporting from a query that changes the format so that it is outputting as MM/DD/YYYY instead of DD/MM/YYYY.

If this guess is right, the 10/6/2013 is really June 10, 2013, but Excell would see it as October 6, 2013, and would be OK with it.

Hope that is is really this simple and the compact date is just coincidence.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[notconfident]

I just went back into the Tek-Tips site and found that I failed to attach the database but noticed something which may be relavent ??
I created an empty Database then exported my table to it so that I could attept to attach the small database. When I now compare my original table and the small one I find that the exported table is the same as the Spreadsheet. There isn't a gap in the records, the problem is they are not in the date order that I expected. I add records to the table with vba and expected them to stay in order. I am confused now.
Does this help ??
Thanks again

 

[notconfident]

Hi Blorf (ChaZ)
Thanks for the quick reply, I think you have answered the problem.
My REAL problem is that this database sits on a website which I don't control. The pal that writes that side of the job lets me access the table via an address so I don't have any control on the output. I found that the export to Excel exibits the same problem so that's what I described.
I have just looked at the website and the June records are there but slotted in after 26th May ??
On the Website the date format shows as 2013-05-30 14:56:45 ???
I think if I add an AutoNumber Field at the beginning of the table it should then sort on that and stay in order.

Again thanks for your help

 

[Blorf]

Glad to help. You can not modify the data, but if you are able to access it as an ODBC table, or what ever your connection to the web site is, can you not read the data with a query, that changes the format of the date, and export from the query, in the sort order of the query?



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[SkipVought]

Understanding Dates and Times & why they seem to be so much trouble? faq68-5827

Never send "dd/mm/yyyy" structure to Excel!!!!

Use yyyy/mm/dd or mm/dd/yyyy to ENTER data or send data.

Then set the NUMBER FORMAT to whatever format suites you.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

the date format shows as 2013-05-30 14:56:45
This is the ISO format and Excel should be happy with it ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886