I'm hoping someone out there can offer some good performance and best practice tips!
Here's a little background...
I have several Access XP Front Ends using SQL 2K as a back end data source. All front ends rely on user input to return records.
The user selects values from the available combo boxes (Depending on the application it may be one or several combo boxes). The values in the combo boxes are usually derived from a linked table (from SQL).
Any linked tables are linked via ODBC/File DSN, to avoid having to manually create a DSN at each workstation.
Once the user selects the criteria, they must click on a Hyperlink to view the data. The on click event kicks off a set of code which passes the seleced values to a pass through query which is executing a stored procedure on SQL Server. Which then returns any associated records, unfortunately the returned data is usually several fields long (this cannot be helped).
There are some applications that have weekly updates to the data, this has prevented me from using a static table in Access that feeds the combo box's. (Simply because the data tables are fairly large and I can't have users waiting too long, they get impatient very quickly!!)
Now for the questions...
1. I have always perfered to copy the Access file locally to the user's PC, as opposed to creating a shortcut to the db on a network drive. This creates a bit of an issue when I have an update to the front end and 50+ people need to get the update. Could anyone recommend the best way to roll out a multi-user Front End? (Am I creating more possible issues by allowing users to access the same db on a network drive?)
2. I use pass through queries to execute stored procedures rather than running a select statement. What is really the best practice? (In a multi-user environment)
3. Currently I use a single SQL Login account, but I really should be switching to NT Authentication for Auditing Purposes in our Industry. What's the best method to implement this in a mulit-user environment? (i.e. I don't want to physically go to each user and make updates to every PC every time I make an update to the Front End.)
4. Can anyone tell me if Access Project offers a better development/performance environment over a regular .mdb?
Sorry for the long winded message, I greatly appreciate any suggestions.
Here's a little background...
I have several Access XP Front Ends using SQL 2K as a back end data source. All front ends rely on user input to return records.
The user selects values from the available combo boxes (Depending on the application it may be one or several combo boxes). The values in the combo boxes are usually derived from a linked table (from SQL).
Any linked tables are linked via ODBC/File DSN, to avoid having to manually create a DSN at each workstation.
Once the user selects the criteria, they must click on a Hyperlink to view the data. The on click event kicks off a set of code which passes the seleced values to a pass through query which is executing a stored procedure on SQL Server. Which then returns any associated records, unfortunately the returned data is usually several fields long (this cannot be helped).
There are some applications that have weekly updates to the data, this has prevented me from using a static table in Access that feeds the combo box's. (Simply because the data tables are fairly large and I can't have users waiting too long, they get impatient very quickly!!)
Now for the questions...
1. I have always perfered to copy the Access file locally to the user's PC, as opposed to creating a shortcut to the db on a network drive. This creates a bit of an issue when I have an update to the front end and 50+ people need to get the update. Could anyone recommend the best way to roll out a multi-user Front End? (Am I creating more possible issues by allowing users to access the same db on a network drive?)
2. I use pass through queries to execute stored procedures rather than running a select statement. What is really the best practice? (In a multi-user environment)
3. Currently I use a single SQL Login account, but I really should be switching to NT Authentication for Auditing Purposes in our Industry. What's the best method to implement this in a mulit-user environment? (i.e. I don't want to physically go to each user and make updates to every PC every time I make an update to the Front End.)
4. Can anyone tell me if Access Project offers a better development/performance environment over a regular .mdb?
Sorry for the long winded message, I greatly appreciate any suggestions.
