June 29, 2009 at 10:36 am
I have many Access queries that run on attached SQL tables. These are not pass-through queries, but native Access queries. Yet on some of these queries I get errors as if they expecting a more SQL-like syntax. Why? I thought that unless I was running a pass-through query, I could run queries on SQL tables exactly the same as if they were Access tables. In what cases should I expect this NOT to be true?
THe current Access query is this:
UPDATE AllottedStagingTimeUpdate1 LEFT JOIN StagingTimes_for_Setupwheel ON AllottedStagingTimeUpdate1.SetupName = StagingTimes_for_Setupwheel.SetupName SET StagingTimes_for_Setupwheel.Total = allottedstagingtimeupdate1.totalnew, StagingTimes_for_Setupwheel.SetupName = allottedstagingtimeupdate1.setupname;
AllottedStagingTimeUpdate1 is an Access table, and StagingTimes_for_SetupWheel is a SQL table. When StagingTimes_for_SetupWheel was an Access table, this ran fine. The purpose is to update existing records and add any new ones, in one step. I know in SQL you have to do this in two steps (append and update separately) but this query is still in Access, so....?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
June 29, 2009 at 12:03 pm
Passthrough or not, when you send a query up to SQL Server, it gets converted into TSQL syntax because that's the only syntax that SQL Server understands. Although I thought that Access did it's work locally by simply copying the entire table down.
What error are you getting?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2009 at 12:07 pm
Be sure that the linked table you have set up in access knows there is a "primary key" /clustered index on the SQL table. If it doesn't know how to identify which specific row is being updated in SQL (due to lack of a unique index), the linked table will be flagged as read-only.
If that's the case - drop the linked table and recreate it. When it asks you what is the unique index, pick a column or a combination that will be used to ID the rows uniquely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 29, 2009 at 12:33 pm
Primary key is there, that's not the issue. I know how to fix it. I'm just curious as to why it's happening, because as Grant said "Although I thought that Access did it's work locally by simply copying the entire table down". But that apparently is NOT the case.
In this case, while I can do this as a single query in Access, I know that within SQL I would need to break it into two queries -- one to edit existing records, one to add new records. If I break my Access query down into two separate queries:
UPDATE AllottedStagingTimeUpdate1 LEFT JOIN StagingTimes_for_Setupwheel ON AllottedStagingTimeUpdate1.SetupName = StagingTimes_for_Setupwheel.SetupName SET StagingTimes_for_Setupwheel.Total = allottedstagingtimeupdate1.totalnew, StagingTimes_for_Setupwheel.SetupName = allottedstagingtimeupdate1.setupname;
and
INSERT INTO StagingTimes_for_SetupWheel ( Total, SetupName )
SELECT AllottedStagingTimeUpdate1.TotalNew, AllottedStagingTimeUpdate1.SetupName
FROM AllottedStagingTimeUpdate1 LEFT JOIN StagingTimes_for_Setupwheel ON AllottedStagingTimeUpdate1.SetupName = StagingTimes_for_Setupwheel.SetupName
WHERE (((StagingTimes_for_Setupwheel.SetupName) Is Null));
it works fine.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply