Editing Table in SQL Server from Access 2002

Hi,
I am posting this message in the hope that somebody will be able to assist me.
I have a user who has the following setup:
We have a server running SQL Server 2000 SP3 (version 8.00.818) running on Windows 2000 Server SP3 and this server has a database which the user used to access and edit using his locally installed copy of Access 2002.
We have recently transfered this database to this server using the DTS wizard, which successfully moved the database. I have now configured a System DSN on the user's PC to connect to this database using SQL authentication and the user can successfully c
onnect to this database, as the DSN has demonstrated. The user has created an Access database and has links to each of the tables in the SQL database, using the 'Get External Data' component, selecting the System DSN as the connection object. The user i
s able to open the tables in Access, but he is unable to edit the data.
When this database was hosted on the previous SQL server, the user was able to edit the data in the tables. On the previous server, this user's Windows user account was a member of the local Administratos group on the server, but this database used SQL a
uthentication. To increase the security on this database, it was moved to the new SQL server, where the user's Windows account is not a member of the local Administrators group.
I have managed to replicate this on my PC, where I have an installation of Access 2003, but I have been specifiying that the database files should either be '2000' or '2002 or 2003' version. I have used SQL and Windows authentication and I in both config
urations I am unable to edit any of the tables in Access. I have given my SQL or Windows account the 'Select', 'Update', 'Delete' permissions on the table - but this has not worked at all. Does anybody have any ideas?
Many thanks in advance.
Neil
If you've verified that it's not a permissions issue, then does the
table have a primary key or unique index?
--Mary
On Thu, 8 Jul 2004 07:53:01 -0700, "NeilH"
<NeilH@.discussions.microsoft.com> wrote:

>We have a server running SQL Server 2000 SP3 (version 8.00.818) running on Windows 2000 Server SP3 and this server has a database which the user used to access and edit using his locally installed copy of Access 2002.
>We have recently transfered this database to this server using the DTS wizard, which successfully moved the database. I have now configured a System DSN on the user's PC to connect to this database using SQL authentication and the user can successfully
connect to this database, as the DSN has demonstrated. The user has created an Access database and has links to each of the tables in the SQL database, using the 'Get External Data' component, selecting the System DSN as the connection object. The user
is able to open the tables in Access, but he is unable to edit the data.
>When this database was hosted on the previous SQL server, the user was able to edit the data in the tables. On the previous server, this user's Windows user account was a member of the local Administratos group on the server, but this database used SQL
authentication. To increase the security on this database, it was moved to the new SQL server, where the user's Windows account is not a member of the local Administrators group.
>I have managed to replicate this on my PC, where I have an installation of Access 2003, but I have been specifiying that the database files should either be '2000' or '2002 or 2003' version. I have used SQL and Windows authentication and I in both confi
gurations I am unable to edit any of the tables in Access. I have given my SQL or Windows account the 'Select', 'Update', 'Delete' permissions on the table - but this has not worked at all. Does anybody have any ideas?
|||Hi Mary,
Thankyou for replying to my posting. Could I ask you to explain what terms
'Primary Key' and 'Unique Index', as I do not understand databases?
Neil
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ietqe0dd6osr6f812l6pgo8gjtrete0uke@.4ax.com... [vbcol=seagreen]
> If you've verified that it's not a permissions issue, then does the
> table have a primary key or unique index?
> --Mary
> On Thu, 8 Jul 2004 07:53:01 -0700, "NeilH"
> <NeilH@.discussions.microsoft.com> wrote:
on Windows 2000 Server SP3 and this server has a database which the user
used to access and edit using his locally installed copy of Access 2002.[vbcol=seagreen]
wizard, which successfully moved the database. I have now configured a
System DSN on the user's PC to connect to this database using SQL
authentication and the user can successfully connect to this database, as
the DSN has demonstrated. The user has created an Access database and has
links to each of the tables in the SQL database, using the 'Get External
Data' component, selecting the System DSN as the connection object. The
user is able to open the tables in Access, but he is unable to edit the
data.[vbcol=seagreen]
able to edit the data in the tables. On the previous server, this user's
Windows user account was a member of the local Administratos group on the
server, but this database used SQL authentication. To increase the security
on this database, it was moved to the new SQL server, where the user's
Windows account is not a member of the local Administrators group.[vbcol=seagreen]
of Access 2003, but I have been specifiying that the database files should
either be '2000' or '2002 or 2003' version. I have used SQL and Windows
authentication and I in both configurations I am unable to edit any of the
tables in Access. I have given my SQL or Windows account the 'Select',
'Update', 'Delete' permissions on the table - but this has not worked at
all. Does anybody have any ideas?
>
|||I think understanding databases is going to be necessary in order for
you to succeed in your work with Access and SQL Server. I'd highly
recmmend Mike Hernandez book, Database Design for Mere Mortals, which
explains the basic concepts in an easy-to-understand way. Anyway, a
primary key or unique index uniquely identifies each row so that the
server knows which one to update. If you don't know what they are,
then you probably don't have one defined on the SQLS table. Access
will refuse to update a SQLS table that doesn't have a PK or unique
index.
--Mary
On Thu, 15 Jul 2004 09:34:39 +0100, "Neil Hobbs" <nhobbs@.sgb.co.uk>
wrote:

>Hi Mary,
>Thankyou for replying to my posting. Could I ask you to explain what terms
>'Primary Key' and 'Unique Index', as I do not understand databases?
>Neil
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
>news:ietqe0dd6osr6f812l6pgo8gjtrete0uke@.4ax.com.. .
>on Windows 2000 Server SP3 and this server has a database which the user
>used to access and edit using his locally installed copy of Access 2002.
>wizard, which successfully moved the database. I have now configured a
>System DSN on the user's PC to connect to this database using SQL
>authentication and the user can successfully connect to this database, as
>the DSN has demonstrated. The user has created an Access database and has
>links to each of the tables in the SQL database, using the 'Get External
>Data' component, selecting the System DSN as the connection object. The
>user is able to open the tables in Access, but he is unable to edit the
>data.
>able to edit the data in the tables. On the previous server, this user's
>Windows user account was a member of the local Administratos group on the
>server, but this database used SQL authentication. To increase the security
>on this database, it was moved to the new SQL server, where the user's
>Windows account is not a member of the local Administrators group.
>of Access 2003, but I have been specifiying that the database files should
>either be '2000' or '2002 or 2003' version. I have used SQL and Windows
>authentication and I in both configurations I am unable to edit any of the
>tables in Access. I have given my SQL or Windows account the 'Select',
>'Update', 'Delete' permissions on the table - but this has not worked at
>all. Does anybody have any ideas?
>