External Query from Access Overwriting data

[dodom75]

I have a spreadsheet that we are pulling list data from an Access Database, it pulls 6 columns from access we have then added 7 columns of data.

So the spreadsheet looks like: (a = Access and U = User)

A B C D E F G H I J K L M
a a a a a a a u u u u u u

The problem is, when the data is refreshed even though I have chosen to insert "Entire Row" it is not inserting an entire row, and the data in the user columns is shifting.

Has anyone hit this problem before and is there a way around it?

 

[Richard Schollar]

Hi

Make sure that under Data Range Properties you have "Overwrite existing cells with new data, clear unused cells" selected and also check "Fill down formulas in columns adjacent to data"

 

[dodom75]

I tried that too and still overwrites the user entered data.

 

[Richard Schollar]

As long as the query is set up to only return 6 columns (and no more) then that behaviour shouldn't happen with the settings I gave. Please re-check.

 

[Fazza]

I don't clearly understand the descriptions, dodom.

What are the 7 columns of user data? Are they all formulas? Such as, every cell in the user data columns starts with an equals [=] sign?

regards, Fazza

 

[dodom75]

I apologize, the spreadsheet pulls from a query in an access database. This is a sales database, so it pulls the company name, effective date, product sold, who sold it and who assisted. We are using this spreadsheet to submit to another area for work to be done on these clients.

So the Admin who pulls the spreadsheet clicks enable refresh, it brings in new data based on the query, she then in column G enters a code, it is not a formula, in column H through M dates are tracked (when the group was sent on the sheet, when it was returned, audit, etc.)

What happens is the current sheet is sorted by effective date and then person who sold it. So a sale may come in late, so in the middle of the sheet a new row appears, but it isn't really a row, on the cells in columns A-F, it shifts the data in columns H-M.

The only way I can figure around this, is the database has an autonumber field and maybe it should be brought in and the sheet sorted by autonumber that way all new ones go to the bottom?

Please let me know if you can think of anything further.

Thanks

 

[Fazza]

OK. I understand more clearly now, dodom.

It is like these other recent threads,
http://www.mrexcel.com/forum/showthread.php?t=376984
http://www.mrexcel.com/forum/showthread.php?t=381918

Some similar implementation might work. That is, BEFORE the query is run to refresh from the Access database, run a query on the information you have. Such as the ID field and then the user filled in data. Say store that in Excel or Access. Then, either change the Access database query so that it pulls from two places (the same place as originally and at the same time in a single query the just run query) to give the desired result, or, run the Access query like you do already and somehow - your choice of how, there are many ways - update the user filled in fields from the other query (in Excel or Access).

I hope that is clear; cause it might not be. OK?

HTH, Fazza