Append Query from Access to SQL Server

[reprosser]

I have created an append query in my MSAccess front end to the linked table in SQL Server. There is an ID field which is common in the query that is type autonumber/identity. If I do not include the ID field in the query, SQL Server gives me an error that indicates that I need to explicitly provide a value. (for an auto number field??) If I include the ID field in the query, I have to provide a unique number as an ID* - instead of SQL Server generating the next value in the sequence.

I cannot figure out how to append records with the query and allow SQL Server to provide the automatic values in the ID field.
Any suggestions? (sql code below)
I guess I could write code to copy each record over one at a time - but I would rather have the query handle it if possible. Maybe I will have to make the update in SQL Server instead of running it from MSAccess...

*I am surprised that I am allowed to input a value for a field that is supposed to automaticaly create a value. I can do this for one record, but the next record that is input normally will increment the automatic value from the value I entered thru the query instead of continueing from the last auto number.

INSERT INTO [tbl:issues] ( IssueID, issuenumber, issuedesc, issuestatus )
SELECT 290014 AS exp, 999 AS IssNum, [qry:OpenIssues].issuedesc, [qry:OpenIssues].issuestatus,  
FROM [qry:OpenIssues]
WHERE ((([qry:OpenIssues].SolutionTestID)=[Forms]![frm:IssueForward]![PreviousTestID]));

thanks
rick

[iburyak]

Can you create pass-through query in Access?
This way you would be able to use native SQL and ID will be auto-generated.

Good Luck.

[reprosser]

There is a pass-thru query option in MSAccess. I am not familiar with it, but I will give it a try - see if I can figure out how to use it.

[iburyak]

What it does. When you create a pass-through query it doesn't check syntax on Access side and passes it to the server as is to execute. On the server side code executed and if syntax is correct it will work. If query is not pass-through it will check syntax first and will not recognize table names that are on SQL side for example or just differences between syntax will be impossible to work with.
Not sure thou how to pass parameters to this query but if you can create it on a fly it is the best bet. Need some research and programming skills…. :)

You probably better off using something like ADO to insert records directly.

Good Luck.

[reprosser]

OK - sounds like I need to write the Access SQL with syntax that works on SQL Server and save it as a pass thru query. Since some of the query parameters are derived from Access form controls , I am not sure this will work.

What I am trying to accomplish is to copy test issues that were not resolved from a closed test project - to the next project, so the issue can continue to be worked. When a new test project is created, the tester picks an old test project from a combo box to drive which open issues should be forwarded to the new project.