Make Table Query Only Runs After Edit

[kagard]

Greetings:

In Access 2010 SP1, if I try to run this query from vba, or by double clicking on it in the navigation pane:

SELECT Key, [Curr Qtr TY], [Curr Qtr LY], Diff, Pct, [YTD Pct]
INTO dctCurrQtrActYTD
IN '\\ds3\Access Databases\DataCentral\DataCentral.accdb'
FROM tblCurrQtrActYTD;

I get this error:

"You cannot record your changes because a value you entered violates the settings defined for this table or list."

Any of the following changes make the query work:

1. Uncheck and recheck the show checkbox for any field in design view.

2. Delete a comma between fields in the SELECT clause in SQL view and then put the comma back.

3. Edit the linked Excel workbook (xls format), then change it back to its original value, save and close the workbook.

In each case, the query then works in Access the first time I run it. After that, it fails again.

I did a compact and repair. No help. I also copied the SQL text, created a new query, and pasted the SQL into it. Same result. Does anyone know what might cause this?

TIA
Keith

 

[RobXMiller]

Came across this since I am creating a Publisher / Subscriber Access database where each database is in different Trust areas on the network.

One thing noticed is that after the first time running a Make Table and putting data in the table of the second database, I could not run the query again if the created table was open.
That is because the second table is locked for editing.

MS Access was nice enough to explain that.
That said, I see your concern about an edit to the Navigation payne.

The code you have should update the remote Access table if the remote Access table is closed, or if the table is closed.
Is this problem happening only if your are viewing the data on the external table?