Interface & SQL

[Curtis Stevens]

Never dealt with sql, only access, but curious.

Lets say you have a company with a network and many users trying to access
the sql database. How do the users interact with it, add & update records,
etc? Is access used on their local computer, which would open the db on the
sql server? Like they would open up the form in Access and it pulls
everything from the sql server?

Thanks
Curtis

 

[John Vinson]

Never dealt with sql, only access, but curious.

Lets say you have a company with a network and many users trying to access
the sql database. How do the users interact with it, add & update records,
etc? Is access used on their local computer, which would open the db on the
sql server? Like they would open up the form in Access and it pulls
everything from the sql server?

Thanks
Curtis

Typically one would use an Access database on each user's computer,
using ODBC to link to the data stored in the SQL "backend" database.
The Access frontend needs to be appropriately designed for this to be
efficient - you certainly do NOT want to pull all the data down to the
local computer; instead you would base Forms on queries (which SQL can
execute very efficiently) which retrieve the minimum number of
records.

You can certainly get started by just using the kind of bound forms
that you're familiar with, though.

John W. Vinson[MVP]

 

[Curtis Stevens]

So you would have a db that basically has forms & nothing else. Those forms
would be based on queries located on the backend sql server? If so, what if
you already have a bunch of forms & queries with lots of buttons and commands
& events already. Would you expect there to be a lot of problems or those
events not to work when changing from local queries to ones located on the
sql server?

If you wanted to update forms, wouldn't you have to go to each person's
computer & update their db? Is this what big major corporations do?

Thanks
Curtis

 

[John Vinson]

So you would have a db that basically has forms & nothing else. Those forms
would be based on queries located on the backend sql server? If so, what if
you already have a bunch of forms & queries with lots of buttons and commands
& events already. Would you expect there to be a lot of problems or those
events not to work when changing from local queries to ones located on the
sql server?

If you wanted to update forms, wouldn't you have to go to each person's
computer & update their db? Is this what big major corporations do?

For a multiuser system, you'll want a split architecture even if you
use an Access database backend! The queries (mostly) would be in the
frontend along with the forms and reports; you may want some SQL Views
or Stored Procedures in some cases, but in general just the tables.

See Tony's website:

http://www.granite.ab.ca/access/tipsindex.htm

He has excellent tips on upsizing to SQL/Server (including some
cautionary advice when you should NOT do so, it's *not* a cureall!)

John W. Vinson[MVP]