Link Tables - Access 97 bug...?

[richand1]

God, I hope someone can explain this to me or help me sort it out - it's been the bain of my existence for two solid days now.

I have written some code in Excel that cuts up a large spreadsheet and splits the data between two other spreadsheets. This all works fine. the code then saves and closes the spreadsheets.

The problem lies with the link tables I have created in Access (97). It doesn't matter how I sort the data in the two spreadsheets, the link tables randomly return the columns in either the wrong order or, even though the column titles are always at the top of the spreadsheets, the titles are placed within the data and a random line of data replaces the column titles! Funnily enough, when I import the spreadsheets as static data, they come through exactly how they appear in Excel.

I have no idea how to sort this problem out as I have tried (I think) pretty much everything to provide a solution. this is what I have tried:

1. Re-running the code from start to finish, deleting the 'corrupt' link tables and re-linking to them.
2. Saving the spreadsheets as text files and linking to these instead.
3. Ignoring the corruption and using append queries to append the data to a static table. Similiarly, I have used make table queries using the corrupt link tables.

It doesn't matter what I do, the data within the link tables is all over the place. Sometimes the some of the column headers will be correct and others will be data within the table.
I'm sure you can all see my frustration!!

This project is huge and it's my first within my new role, so it's pretty important personally and professionally. You lot have never once let me down and I'm grateful beyond words. Please help me this time...

Rich

 

[abaldwin]

How are you getting your data? Are you using MS-Query or an ado connection or what? The "randomness" of your returned data seems to lie in the sql statement used to retrieve the originial data.

Am I to gather that you are retrieving data from access into the first database and then copying the data to two others?

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[richand1]

The flow of the data is this:

1. Data starts in Excel spreadsheet.
2. Code splits data into two spreadsheets.
3. Two link tables in Access retrieve the data from the two spreadsheets.

There is no SQL involved in the data link between spreadsheet and database - I have just created two new link tables in Access.

Thanks Andy.
Rich

 

[abaldwin]

As for the field names being in the data that seems to be the fact that the linking was not told to use the first row as the field names.

Is the data always starting in row 2 with teh field names in row 1?

I think the sorting is based on this somehow as well and really what difference does the sorting mean in the actual linked table. Any reports queries etc should handle the sorting and grouping of data.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[richand1]

Every time I've created the links, I've told the system that the titles are in the first row, but this doesn't seem to make any difference.

Regarding the sorting of the data, I've tried sorting it AND leaving it as is after the code has produced the spreadsheets, but this makes no difference either, so I doubt it's that.

I'm going to start the process from the very beginning again tomorrow and see if I've missed something. I need to go home and take some headache pills. Thanks for your help so far Andy.

Kind regards
Rich

 

[ddiamond]

Rich,

I've tried everything I can think of to duplicate your problem using Access 97 and Excel 97, but I always get the desired results - first row of spreadsheet = field names, table order matches spreadsheet order. When you link your table, does the link table wizard pop up?

- Dan

 

[richand1]

Hi Dan

Yes, is does.

I've also tried copying the spreadsheets, as values, into new spreadsheets, and it still comes returns the data as a jumbled mess.

The only way I get it to return the data in the same order as the spreadsheet is by deleting out a huge chunk of the data. The spreadsheet is 'only' 7mb, so I don't know why the size is causing a problem, unless some of the data is causing some sort of corruption.

I guess I could split the data down in to a few smaller spreadsheets and have a link table pointing to each of these, then append the data from all these link tables into a static table...

 

[ddiamond]

Rich,

So you are saying the amount of data is causing a problem? That would explain why I couldn't reproduce it. I was testing with a tiny 10 row spreadsheet. This is probably a silly question, but have you tried reparing and compacting the database? Whenever I get utterly bizarre results from Access, reparing the database usually fixes it.

- Dan

 

[richand1]

This the only thing I haven't tried.

I'll do this on Monday and let you know.

Thanks mate.

 

[easyit]

Beside R&C thru decompile as well.
Alternative: Why not import in access and then split the data?

Size can not be a problem (I think). You could look into the system tables, to see how they are linked. I'm no expert there, but I believe that is is shown there.