Create an SQL ODBC Connection Read/Write 1

[lradon]

Hi,

I have a similar need as the previous post. We use SQL 7 and I need my ODBC connection to be Read/write, so that users can do data entry thru an Access 2000 form and be able to edit the records and delete them as necessary.

I have a feeling that the problem lies in the SQL login and I am missing the spot where I should be granting permissions to read/write a particular table, but, I am unclear where.


HELP!!!

Thanks in advance.

 

[mrdenny]

If you want to grant rights to a specific table in SQL Server double click on the table in Enterprise Manager and click the permissions button. You'll need to grant Update and Delete seperatly as they are seperate functions.

You'll also need to ensure that there is a primary key or unique index on the table. Access can't update or delete from a SQL Server table without a primary key or unique index.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lradon]

Thanks Mr. Denny. I will follow your suggestions.

I have spent much time googling this and reading BOL and am getting frustrated because I haven't had to do this before and there are all sorts of options out there and I'm not sure I was finding any I could use, due to my knowledge, until you answered this post. I may have additional questions. Thanks, Laura

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lradon]

Thank you, Mr. Denny. I added a primary key to my Access table before I upsized it to the SQL db and tested it and it worked!!! I could edit and add new records.

So thank you. Laura