Re: Connectivity


"Nick 'The Database Guy'" <nickmcm@xxxxxxxxxxxxxx> wrote in message
news:5495113d-e56f-4fa7-aac6-581f77b491e8@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 19, 6:11 am, "Neil" <nrg...@xxxxxxxxx> wrote:
"Nick 'The Database Guy'" <nick...@xxxxxxxxxxxxxx> wrote in
messagenews:59138309-dcc1-44d3-bc47-64c1105d39d6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I know a great deal has been written about this but I can't seem to
find anything which solves my problem, which is that I cannot seem to
establish anything but a read only connection between Access 2003 and
SQL Server 2005. This does not include the transfertext command which
I can use to update the database, but it does include any way of
modifying the data, ie. DAO, ADODB, and using a delete query and an
update query. I think that it has something to do with the record
locks, as I keep getting error 3197 - another user is trying to update
the same data as you.

Thanks in advance.

Nick

It sounds to me that you're using linked tables, but you don't have a
primary key set up in your linked tables. If you have no primary key set
up
(or if you have the wrong primary key set up), then you won't be able to
delete the data, and it will be read-only. Go into your linked tables in
design view and see what the primary key is (if any) and check that it's
correct. That's where I'd put my money.

Neil

Thanks Neil, I was aware that you needed to have a prmary key, I added
an identity field, but then I got the problem where access tells me
that all the records have been deleted, but there is a field that I
believe that I could as the primary key which is not an autogenerated,
the only trouble is that the Server is now full, ie 0 bytes
available. I access it remotely and because the C:\ drive is full I
can so longer gain enough access to even go in and manually delete the
records to free up some space. The amount of space that I have been
given to work with is tiny, around 5Gb, and none of the network team
seem to be in today. So it may be a while before I can tell you
whether your solution solved my problem. This is just a note to say
that I appreciate your help and I will get back to you and tell you
what degree of success I have had in resolving it.

Nick
================

Well, even if you deleted the records, it won't change the database size
unless you shrink the database. Did you try shrinking the database in SQL
Server?

As for the primary key, doesn't matter if it's autogenerated or not. Just
whatever IS the primary key, make sure that that's the primary key in your
linked tables, as well. Then you'll be able to update and delete

Neil


.

Relevant Pages

  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data access perfomance
    ... Hi, thanks I kind reduce the time inserting into the database, the problem ... was that I had a primary key on the table, ... >> I can't discuss Oracle, but for SQL Server, the following measures ...
    (microsoft.public.data.oledb)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Enterprise Mgr - alter table fails
    ... any type of "design" table changes are failing in Enterprise Mgr. ... MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1' ... MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint. ... CONSTRAINT PK_prob1 PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.tools)
Loading