January 18, 2017 at 9:05 am
We are planning to create an access database and used linked tables to coonect to a sql server table.
Users could edit the table data from Ms Access.
How many concurrent users allowed ?
What is the best way to set up Access in order to edit Sql table?
Thanks
January 18, 2017 at 10:15 am
sqlfriends - Wednesday, January 18, 2017 9:05 AMHow many concurrent users allowed ?
In Access, or on SQL. If SQL, how many connections can you have on your SQL Server? Are you running licences with CALs, or Cores? The answer depends on your answer to that. If Cores, then as many as you want, realistically. If CALS, how many CALs do you have?
On Access, it's not recommended to have more than one user to have the same Access file open at the same time (talking from experience) if you're storing data locally. It's not really a problem if all your data is hosted on SQL, as SQL Server handles multi users well.
Your latter question is very open ended. What is your goal? Are you creating a front end interface using Access, datasheet views? I'm not sure there is really a "best practice", but personally I'm not really a fan of Access amending data, or inserting on SQL (although, I haven't done it since Access 2010).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 18, 2017 at 10:25 am
Thanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.
You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?
Thanks
January 18, 2017 at 11:16 am
sqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
Give every user a copy of the same Access database and let them access SQL Server through their own copy.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2017 at 11:22 am
Phil Parkin - Wednesday, January 18, 2017 11:15 AMsqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
Give every user a copy of the same Access database and let them access SQL Server through their own copy.
Proceed to cry every time that access db needs to be updated.
January 18, 2017 at 11:29 am
sqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
I've worked with a number of systems like this over the years. Yes SQL Server is handling the concurrency and manages any locks on records. The locks taken out may depend on what type of form your Access application has. If data entry screens are one record at a time then it's just going to be using record locks, but if you're letting people modify multiple records in a grid-like screen then it could get more complicated. A recommendation I can make is to try to use pass-through queries to do the database work inside of Access, otherwise sometimes Access can do silly things like try to read a whole table locally to the workstation.
January 18, 2017 at 11:34 am
what is pass through queries? thanks
January 18, 2017 at 12:28 pm
ZZartin - Wednesday, January 18, 2017 11:22 AMPhil Parkin - Wednesday, January 18, 2017 11:15 AMGive every user a copy of the same Access database and let them access SQL Server through their own copy.Proceed to cry every time that access db needs to be updated.
Maybe not though. I've seen quite a few places handling this by having a versioning of the access db usually in a table in the access database and keeping the main version on a file share and users having one locally. Whenever the users started up their access db, it checked the version of their local database to the one on the file share and downloaded the updated version if their local copy wasn't up to date. So it was just a matter of updating the one access db on the file share.
Sue
January 18, 2017 at 2:17 pm
sqlfriends - Wednesday, January 18, 2017 11:34 AMwhat is pass through queries? thanks
Pass-through queries are a feature within Access that let you query the SQL Server directly with code you write. Here's an article that describes setting one up:
https://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/
January 20, 2017 at 2:52 pm
Sue_H - Wednesday, January 18, 2017 12:28 PMMaybe not though. I've seen quite a few places handling this by having a versioning of the access db usually in a table in the access database and keeping the main version on a file share and users having one locally. Whenever the users started up their access db, it checked the version of their local database to the one on the file share and downloaded the updated version if their local copy wasn't up to date. So it was just a matter of updating the one access db on the file share.
Sue
How to achieve and implement : check version of db and downloaded updated version?
January 20, 2017 at 2:54 pm
Chris Harshman - Wednesday, January 18, 2017 2:17 PMPass-through queries are a feature within Access that let you query the SQL Server directly with code you write. Here's an article that describes setting one up:
https://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/
Thanks, will take a look.
January 20, 2017 at 3:02 pm
ZZartin - Wednesday, January 18, 2017 11:22 AMPhil Parkin - Wednesday, January 18, 2017 11:15 AMsqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
Give every user a copy of the same Access database and let them access SQL Server through their own copy.
Proceed to cry every time that access db needs to be updated.
I think Tony Toews wrote something that does that. even so. yuck.
January 20, 2017 at 3:03 pm
sqlfriends - Friday, January 20, 2017 2:52 PMSue_H - Wednesday, January 18, 2017 12:28 PMMaybe not though. I've seen quite a few places handling this by having a versioning of the access db usually in a table in the access database and keeping the main version on a file share and users having one locally. Whenever the users started up their access db, it checked the version of their local database to the one on the file share and downloaded the updated version if their local copy wasn't up to date. So it was just a matter of updating the one access db on the file share.
Sue
How to achieve and implement : check version of db and downloaded updated version?
Read all about it. http://www.autofeupdater.com/
January 22, 2017 at 11:05 am
pietlinden - Friday, January 20, 2017 3:02 PMZZartin - Wednesday, January 18, 2017 11:22 AMPhil Parkin - Wednesday, January 18, 2017 11:15 AMsqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
Give every user a copy of the same Access database and let them access SQL Server through their own copy.
Proceed to cry every time that access db needs to be updated.
I think Tony Toews wrote something that does that. even so. yuck.
I am curious why an Access database needs to be installed at all. Why not simply create an Access Switchboard application which will run under the Access Runtime Environment and connects directly to the SQL Server database?
January 24, 2017 at 12:23 pm
ZZartin - Wednesday, January 18, 2017 11:22 AMPhil Parkin - Wednesday, January 18, 2017 11:15 AMsqlfriends - Wednesday, January 18, 2017 10:25 AMThanks, our SQL server is core based, so there is no problem of users limitation.
What I concerned is MS access concurreny issue. We currently have no resource to make a .net webform. So we consider using Access to edit one of the SQL tables. There are about 10 users that may need to do the work.
I am concerned if some users are working on updating same records, will the table locked or transaction corrupted etc.You mentioned since it is a linked table in Access which points to MS SQL server database, it handles the concurreny in SQL server?
Any other options we can reach the goal?Thanks
Give every user a copy of the same Access database and let them access SQL Server through their own copy.
Proceed to cry every time that access db needs to be updated.
PUBLISH the access database as an Access executable. This will create a one-click application from a central place on your network. one of the nice features of such a thing is that it will prompt the user to UPDATE when you need to adjust the DB.
There's no crying in DB design
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply