Access project and sql server

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I use a Access 2000 project as a front-end for SQL server
2000. I connect from a remote computer. When I connect to
the server, I can read all tables. But if I try to make
views or make any kind of change to the database, I get an
error message saying something like; You do not have single
user access to the database now. Cannot save changes.
But if I open the access project from the sql server,
locally, i can make all the changes i feel like. In both
cases i use the sa user to connect.....
So what is a man to do so he can connect from the remote
computer and don't have to work in the server rom all day?
It's noisy in there and cold and dark......
Any ideas?

Tim
 
First, Access 2000 doesn't work well with SQL-Server 2000 if you don't have
installed the Acess Readiness Kit for SQL-2000 (or something like that, I
don't remember exactly); as A2000 was developed for SQL-Server 7 instead.
Beware that even with the kit installed, Access 2000 is full of bugs when
it's come to ADP. Some of these bugs have been corrected in A2002/3 but
not all.

Second, make sure that you don't open the same table in design view while
trying to view or edit the data.

All of your tables that you want to change the data must also have a primary
key and, if I were you, I wounld't use primary keys set on two or more
columns.

S. L.
 
I wounld't use primary keys set on two or more columns. <

Why is that? I use 7.0 and have never had a problem with this. Although, I
generally create tables with T-SQL.

I assume you are describing a limitation of Access 2000 with SQL Server
2000, but I'm curious to hear more details.

Thanks
 
Oh, in this case, it's more of a design issue than a technically one about a
limitation of Access 2000/2/3.

True, multiple-fields primary keys are known to greatly reduce the speed of
inserts and updates statements on many cases (but many will disagree) and
also problems have been reported with RDI (Referential Data Integrity) and
delete statements under SQL-Server; but my primary concern was to indicate
that often, a primary key with multiple fields only adds confusion to the
database. It's often hard trying to understand a somewhat complex Join
(especially (but not only) when it has be written by someone else); it can
become a real pain when multiple fields are implicated.

I have co-workers who are telling me that primary keys with multiple fields
are one of the best thing of SQL-Server; but if it's true, how come that
they have so many problems with them when it's time to write code?

Also, Access 2000/2/3 ADPs are so full of bugs that I think that every
possible simplications can only ease the pain.

S. L.
 
First, Access 2000 doesn't work well with SQL-Server 2000 if you don't have
installed the Acess Readiness Kit for SQL-2000 (or something like that, I
don't remember exactly); as A2000 was developed for SQL-Server 7 instead.
Beware that even with the kit installed, Access 2000 is full of bugs when
it's come to ADP. Some of these bugs have been corrected in A2002/3 but
not all.
Click to expand...

I run a very complicated application with an AC2K ADP as the front-end and
SQL-Server 2000 as the backend. I have intalled no readiness kit.

IMO, your comments are wrong.
 
For the readiness Kit, you can find info at the following link:
http://www.microsoft.com/office/ork/2000/journ/accsql.htm .

Of course, with all the services packs around, maybe it's no more necessary
to install it for Access 2000, I don't know as I'm using Access 2002 now and
this has been the case for something like two years. But the question that
has been asked at the beginning of this thread was about a strange error
message, which was displaying when trying to make change to a database from
inside Access. This look to me exactly as the kind of problems that was
happening to me some years ago. Of course, if you are doing all of your
database design from inside the SQL-Server Manager, then this have never
been a problem for you and will never be, even without any service packs
applied, but in this case your are in another kind of situation.

As for me, I'm unable to follow all these versions and service packs and
interactions beetween them; but when someone come with the description of
bug that is reminiscent of a situation in which I have been myself some
years ago, then I try to be of some help. Maybe today I'm now totally
wrong, but personally I don't mind as I prefer, when I have a bug that I'm
unable to resolve for a period of time, to have some advice that was pretty
good some years ago than to have nothing or to have the statement that
today, everything is perfect and, as such, bugs don't exist anymore.

And for the fact that you have been able to develop and run a complicated
database application without any bugs from ADP, then I'm very glad for you.

S. L.
 
Lyle,

My company is finishing up MDB to ADP conversion for a client, and it seems
to be going pretty well. Would you be willing to send me your contact
information in the event we run into a bug/issue we can't resolve? I
promise you won't get bombarded with emails...:-)

--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001
 
Lyle,

My company is finishing up MDB to ADP conversion for a client, and it seems
to be going pretty well. Would you be willing to send me your contact
information in the event we run into a bug/issue we can't resolve? I
promise you won't get bombarded with emails...:-)
Click to expand...

IMO, Technical Newsgroups should not be used for commercial communications
and I try to be circumspect in this regard. Of course, anyone can email me by
going to the link below.
 
Point well taken... For what it's worth, I generally don't use a
multi-column key of more than two columns for some of the exact reasons you
mention.

Thanks for the insight!

-Andy
 
I use Access 2000 project as front end with SQL 200
backend all the time, I love the combo, but it's just my
preference.

When I create the project adp file, I keep a master in my
personal directory first, then I put a copy on a public
shared direcotry for the users, they then copy the adp
file to their personal directory. OR, I email the adp
file to each person who will need to use it, and they save
to their personal directory.

Access Project adp files cannot be used at the same time
in terms of design changes, etc. Therefore, if you save
it to your own directory, you will not get these sharing
violation error messages.

If you need more info or ideas, email me at
(e-mail address removed), as I don't check this newsroom daily.
 
I threw out the old Access 2000 and got hold of Access
2003, and now it works. Thanks for all the suggestions and
help. :)

Tim
 
Back
Top