Data moves when query refreshed

[BSmith]

When I refresh my query from my data source some of the cells shift around in the data table. The cells that are shifting are being manually entered next to the query data. Each time the query is refreshed, the cells shift down x number of rows. Then when the query is refreshed again, the cells shift back to their original place. Any insight into this problem??

 

[Luke M]

Check the query options possibly? I know there's a setting for how new data is entered either:

a) Overwrites old data

b) Inserted above old data

c) Added to old data

This might also be tied into the option about extending formulas? (guess)


Again, I'm making a lot of guesses here, hopefully it helps.

 

[archmaster]

Hi BSmith,


I´m having the exact same problem. On a simple MS Query with 4 columns as an Excel Table, If you enter data in the adjacent column and hit refresh, the data jumps randomly all over the place, destroying the record integrity... ¿Were you able to figure this out? ¿How did you fix it?

Thanks a lot!


Archmaster

 

[SirJB7]

@all

Hi!


As Luke M said your queries might be inserting data instead of overwriting it.


a) If you use a manual query.

- Select a cell from the query output.

- Go to the Data tab, Connections group, Properties icon.

- Select Overwrite Existing Cells.

- Accept.


b) If you use a macro.

Change the refresh style to overwrite in the VBA code:

-----

.RefreshStyle = xlOverwriteCells

-----