Allowing Read Write priveledges for users to SQL Server Back End

[Lightwave]

Dear all

Experimenting with an Access 2003 database linked to SQL Server 08 R2 express.

Using the DSN less connection code I can connect to the database no problem.

Unfortunately I can't edit or add any information to the SQL Server.

I have tried going into SSMS and setting security both for the instance first and then in the database itself.

I still can't seem to get the user to be allowed to edit the information. Struggling to figure out the correct configuration.?

Any help or links to good sources would be much appreciated.

 

[Galaxiom]

What message do you get when trying to edit or add a record?

 

[Lightwave]

I don't get any message the table is simply locked cannot edit delete or add.

I have tried adding privileges to windows accounts but doesn't seem to work. I think I have got the configuration incorrect on SQL Server 08 R2 SSMS somehow.

 

[Lightwave]

So further notes

I can sign into my fresh copy of SQL Server Express 08 R2 SSMS as the SA and I can even use a connection string that includes the SA password but somehow I am still not able to edit things in the database.

Things I have tried so far
* Logging into SSMS with SA and giving as many permissions to users as possible
* Putting the SA username and Password in the DSN less connection
* Logging into SSMS with other privileges and giving more privileges in security
* Making up a username and password and then putting those in the DSN connection string
* Changing format of the file to 2003 from default format of 2000
* Starting and Stopping the SQL Server - (only did it once to reset SA password)

Nope still the table is brought across but no editing or deleting of records.


Hmmmmmm

 

[Lightwave]

Ok just a thought.

Would it be a problem of a 32 bit client Access 2003 connecting to a 64 bit SQL Server Express?

 

[Galaxiom]

What Authentication have you set on the server. Windows Authentication or Mixed? What is the network environment? Domain, workgroup?

Long shot but what about permissions or attributes on the data (mdf) files?

 

[Lightwave]

Thank you G I will test out and come back to you.

Pity your not a little bit closer I would pay you to have a look.

 

[Lightwave]

Apologies G I've been somewhat blinkered.

I had set a field to is identity and put an increment on it and had thought that was setting up the field as a primary key ID - turns out I needed to place the key symbol on field as well to make it work.

There is now NO problem with editing new information in the access front end.
Editing existing records now throws up the following.

Any ideas

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the value the other user entered, and then paste your changes back in if you decide to make changes

 

[Lightwave]

Right

Now although I can enter new rows.
If I edit a formerly entered row it comes up with the above error.

Likewise when I close down a table and open it again the values within the existing records show up as

#Deleted

I am putting the keyon the field as big int with SQL Server 08 R2 Express it seems to be incrementing the PKID not problem.

I looked up the error message and they said that default values should be set on certain fields - there are no numerical fields except the PKID field that don't have default values.

Its actually only proof of principle table at the moment
PKID Firstname Surname

 

[Lightwave]

Galaxiom

Thanks again for your assistance.
I had used Big Int as the field type within SQL Server 08 R2 express and apparently Access 2003 does not recognise this data type.

Changing the data type of the Primary Key to INT would appear to have fixed the problem.

I've been a bit naughty on this post I posted in Utter Access as well. It was really important for the progress of some databases that I get to the bottom of this as it can mean I can really open up some databases.

 

[Rx_]

It is a dreary afternoon here as I read your post, so forgive me if I missed something.
You did use a primary key on the SQL Server side (e.g. autocounter equal), right?
I kind of remember moving a table from Access that had no index or primary key over to SQL once. The table was just used for some list box or something non-standard.
When it was migrated over to SQL Server with the SQL Server Migration Tool for Access, it would connect, but could not read-write.

Hope that helps.

 

[Galaxiom]

I had used Big Int as the field type within SQL Server 08 R2 express and apparently Access 2003 does not recognise this data type.

Changing the data type of the Primary Key to INT would appear to have fixed the problem.

Yes, the integer types in SQL are all twce as wide. SQL Int is the same as Access Long. And as you found, bigint has no equivalent.