sql tables used by an access application

Question

Post reply

sql tables used by an access application

Juanita

SSCertifiable

Points: 5300
More actions
April 4, 2005 at 10:05 am

#163877

HI. I have a developer that has an access application going against sql tables.
One of the functions of this application is to copy/append records from 1 file to a backup file and then delete the records from the original file. I have given the user all permissions to all the tables in sql for this app. Well the user cannot do the copy/append/insert records to the sql table but can do the delete from the original file. The only way it appears to let them do the copy/append/insert is if I make the user a member of the DATABASE BASE OWNER ROLE. I have never had to do this for any other application, I just set permissions to the sql tables as necessary. I even tried making the user a member of the DATAREADER/DATAWRITER role and that wouldn't work.
This is the 1st access application we have worked with that is using sql tables.
The error that the user gets reads like this:
Microsoft Access cannot append all the records in the append query.
Microsft Access set 0 fields(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations
ANY help is appreciated here !!
Juanita

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lizzarrd SSC Rookie Points: 38 More actions · Answer 1

Hi Juanita,

It sounds like you have an issue not with permissions but with a duplicate entry on your primary key.

The error:

"Microsoft Access cannot append all the records in the append query.

Microsft Access set 0 fields(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations"

Check that you are not trying to add a record that has already been added to the archive table, or at least that has the same PK as an existig record.

Hope this helps.

Luke

Juanita SSCertifiable Points: 5300 More actions · Answer 2

Luke, Thank you. I'm checking that right now. I know the original tables key is an identity field but I didn't make the key in the backup table to be identiy because it only being copied. maybe it should be identity also???

Thanks,

Juanita

Juanita SSCertifiable Points: 5300 More actions · Answer 3

One more note, what really confuses me is that if I change the ownership of the user to database owner, the problem doesn't occur? does being a database owner override issues like duplicate keys, etc?

tjandrasa SSC Journeyman Points: 97 More actions · Answer 4

It should not override duplicate keys whether you are db owner or not.

Probably the problem is not duplicate keys.

Juanita SSCertifiable Points: 5300 More actions · Answer 5

HI all,

I finally fixed the problem. it was that the file that was being written to had the key as an auto number. once I removed that property it was fine. I still don't understand why making the user a member of the database owner role allowed it to work.

Juanita