Linked Excel table in Access - field values change as I navigate?

[CaraM]

Hope I'm explaining this clearly.

I have a sheet/table of very important data - data that I don't want to screw up - in Excel.

Some of the queries that I want to do against this table are complicated, so I decided to start using Access again, after being away from it for years.

I have linked the table in Excel into my Access database.

I have a query in Access to query that Excel data - and wish to edit ONE field of the Excel data from the datasheet view of the results of the Access query (though I'm displaying more than one of the fields).

HOWEVER, when I run the query and navigate through the fields of the records (using the arrow keys) - the values displayed CHANGE.

For example, I'm on record #1 of the results, and the [Task] field contains the value "Laundry". As I right-arrow from field to field, "Laundry" changes to "Pay the water bill," which is the value of the [Task] field from the next record in the query results.

What is wrong with my query, to be doing this? And - can I post a screen recording/video here, if this doesn't make any sense?

 

[welshgasman]

IF you are just moving from control to control on the same record, that should not happen, so more going on here than you have stated.
You would need to show the code from all of the form or upload the DB.

One can easily move to next record in a focus event of any control.

Easiest and quickest would be upload enough of teh DB to see the problem. Obviously you would need the Excel workbook as well.

 

[Micron]

You can't upload files here AFAIK. You'd have to upload to a file share, post a link and set permissions on the file share to allow access to anyone with the link.
Did you look at the Excel sheet itself, and did the data change there? I wonder if your query is grabbing new values from the sheet, not actually changing them.
Did you open the linked table and look to see if the data actually changed there?
Did you open Excel file, check the data on the sheet, then play with the query, then go back to the sheet and see if it changed?

I thought that without a special hack (I can't recall what that is) that Access cannot edit linked sheets. At least not since about 2003 version.

 

[CaraM]

I'm thinking it has to do with the IMEX value in the linked table manager connection string.

The part I'm currently stuck on is why, yesterday, I seemed able to edit the linked table and have the changes reflected in the XLSM file, which was OPEN. (Though I was having the original issues with values appearing to change.)

I'll update if I figure out exactly what's happening. Having trouble focusing, today.

 

[Micron]

OK, I think you hit on what setting I was referring to. I (wrongfully?) presumed that setting was not in play thus your linked sheet should not have been editable - especially by a Select query.

 

[welshgasman]

So it is an xlsm file?
Is there any code in that that is invoked if that cell is modified?