Run Time error when uploading to SQL Server

[phineas629]

Hi all,



I'm trying to upload my data from Access to SQL Server.

        
        .Execute "INSERT INTO Payment ( PaymentId, FundId, Amount, AdjustedAmount, GrantFundId )" & _
                " SELECT Payment.PaymentId, AWARDFundId, Payment.Amount, Payment.Amount, GrantFunds.GrantFundId" & _
                " FROM (tblNEWAward INNER JOIN Payment ON tblNEWAward.AWRD_GrantId = Payment.GrantId)" & _
                " INNER JOIN GrantFunds ON tblNEWAward.AWRD_GrantId = GrantFunds.GrantId" & _
                " WHERE ReadyForUpload = True"

I get run time error 3622. You must use dbSeeChanges with OpenRecordSet when accessing a sql server table that has an identity column


However, when I use the dbSeeChanges option then I get run-time error '3000': reserved error (-7711); there is no message for this error.


Any ideas?

Thanks

[rpeare]

have you run a debug.print statement on your SQL statement to see if it's actually giving you a valid command? Sometimes if you cut and paste the generated SQL statement into a query window and try to run it you'll get a different error message that is sometimes more helpful than when you try to run it within your code.

You are defining the table every field came from in your query other than AWARDFUNDID, so if that appears in more than one table of your query it may be causing issues because it doesn't know which table to use to pull that piece of information from.

[John_G]

I think the problem is the PaymentID field in your insert statement. If PaymentID is an Identity column in SQL Sqerver, then you can't assign values to it - it is automatic (sort of like MS Access autonumber). Take PaymentID out of the Field list and the values list to see if that works.

Now, if you need PaymentID to be YOUR value, and not something SQL Server assigns, then you will need to change the data type of PaymentID in the SQL Server table.

You can find more info here : http://www.sqlteam.com/article/under...entity-columns

HTH

John

[lfpm062010]

Another way around this problem is to "SET IDENTITY_INSERT" to off.
http://technet.microsoft.com/en-us/l...v=sql.80).aspx

To be able to turn on and off the "IDENTIRY_INSERT" you must be sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner as the above article stated. At least you must have "ALTER" privilege to the table in question.

Be aware if you use the IDENTITY field as UNIQUE or PRIMARY KEY, you might get an error when you are trying to insert a duplicate key value.