Developing For SQL Server backend

I have been developing MS Access applications for my clients and subscribing to EE for over 15 years.    EE has been an invaluable resource working thru many projects.

In my Access applications I always split the procedures from the data.  Up to this point all of the data backends have been MS Access.

The story:
I am using Access 2013 to develop my first app that will have a SQL Server backend.  I have many SQL tables defined and I have developed many logic routines converting data from the old legacy format to the new SQL tables.  Currently most of my SQL tables are linked to the Access front end using the Linked Table manager.

I have some questions as I think about moving toward production

Connecting to Data:

In my prior Access apps I have a routine that will automatically link the backend Access tables based on a .ini parm read at startup.  Each system has it's own ini.  This way the app adapts to each operating environment without having to manually link the tables.

SQL tables can also be linked to an Access front end using the Linked table manager.  Is there a routine that will automatically relink SQL table based on an .ini parm just like my current routine does for linked Access tables?

Bound or Unbound Forms?

Using SQL tables linked thru the Linked Table Manager, it is possible to create forms bound directly to a SQL table, just like you can do with Access tables.  Is something that is recommended?  Or, is it better not to bind forms to tables in SQL server?  What are the pro's and cons of either approach?

Stored Procedures?
I will be using Views extensively.  I have heard and read differing opinions on using stored procedures.  Some say there in no great benefit, other say they should be used extensively.   I came upon one response to a SQL question stating that every create, read, update and delete (CRUD) of a record should be done using stored procedures.  What is your experience?

TimeStamp?
As instructed/read I have included a field defined as 'TimeStamp'  on all of my record definitions.  My understanding is that SQL will use this field to determine what to do in record locking situations.  I don't use this field for anything in the app.  Do I need to load a value in the field or does SQL handle it on its own.

SQL Setup?

I installed SQL 2014 Developer accepting the standard set up options.  Are there any setup options or values that should be set?

If there is any other insight into this process you can offer I would appreciate it.

I realize this is not a specific directed question with a correct answer but I am looking for feedback from developers that have been thru this process.  I will award points to all helpful contributions.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
TimeStamp?
As instructed/read I have included a field defined as 'TimeStamp'  on all of my record definitions.  My understanding is that SQL will use this field to determine what to do in record locking situations.  I don't use this field for anything in the app.  Do I need to load a value in the field or does SQL handle it on its own.

This is not correct.  First of all if you are going to use it you should be using rowversion and not timestamp.  Secondly this is the definition for timestamp:
timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
That is it, no more no less and certainly nothing to do with locking.
Anthony PerkinsCommented:
Stored Procedures?
I will be using Views extensively.  I have heard and read differing opinions on using stored procedures.  Some say there in no great benefit, other say they should be used extensively.   I came upon one response to a SQL question stating that every create, read, update and delete (CRUD) of a record should be done using stored procedures.  What is your experience?

This depends on many factors.
1. Experience using T-SQL and specifically Stored Procedures.
2. Security.  Using Stored Procedures you can prevent users from reading and writing directly to tables.
3. Encryption.  This is not full-proof, but you can encrypt your code in your Stored Procedures to hide the logic in it.

The problem with Views is that developers tend to abuse them and you end up a mess where one view calls another view that calls a third.

In summary, there is no hard and fast rule it really depends on you and your level of experience.
mlcktmguyAuthor Commented:
Thanks for the responses.  
Does anyone have insight on using on using Bound vs non-bound forms?
or
Automatically linking SQL tables based on a parameter card?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PatHartmanCommented:
Connecting to Data:You could use the same .ini file technique you are currently using.  I've included a database sample of what I use.
Bound or Unbound Forms?I always use bound forms but here is where the technique between ODBC and Jet/ACE will differ.  Using Jet/ACE you could link directly to tables and use filtering to allow the user to locate the record he wanted to work with.  Using ODBC, you want the server to do the filtering.  You DO NOT want to ever bind a form directly to a table since that will cause Access to request ALL rows from that table to be downloaded from the server.  This will not make you any friends in the DBA group.  You want to always bind to queries that have selection criteria that limits the rows/columns to be retrieved.  Depending on the situation, I do this in one of two ways.  In simple cases where a client will always want to work on a single record at a time, I use combos or text boxes in the header of the form.  The query uses those as criteria so the form will always open empty.  The user either chooses from the combo or types into the text box to enter his "filter".  If it is a one field filter, the AfterUpdate event runs the Requery.  If it is a multi-field filter, I use a button to run the Requery.  The other method I use when the client needs to search and the search can return multiple records.  This is done with a search form that has multiple criteria.  If the query returns more than one row, an intermediate form is opened with just some key information.  The user would then double click on the record he wants to work with and that would open the edit form.  If the search only returned one row, the app would go directly to the edit form and not open the intermediate list.
Stored Procedures?I rarely need to use them and virtually all my apps are SQL Server/DB2/Oracle.  When I do use them, it is for complicated reports or for long running batch processes.  I always start with Access queries and VBA code loops.  If they are too slow, I move to stored procedures or preferably views since those can be updateable and therefore are useful when bound to forms.
TimeStamp?Everybody calls this a timestamp because that is what the data type is called.  It is really a "rowversion" indicator and Access uses it to determine if someone has updated the record in the table AFTER you read it.  So userA reads rec 1 and userB reads rec 1.  UserB saves rec 1 and that changes the timestamp on the record in the table.  So when userA goes to save rec 1, Access determines that the timestamp in the table is different from what it has and so you get notified that some other user has updated the record and you get three options.  Overwrite (always a bad idea), discard (probably the right move), and copy to the clipboard (I haven't experimented with this sufficiently).  Without the Timestamp field, Access has to check every column in the table and compare it to all the columns you have bound to the form to determine if there is a conflict.
SQL Setup?Although I have installed SQL server dozens of times for myself I would NOT RECOMMEND that you install the production server.  Please, get the client to hire someone certified to install the server and set up the backup/recovery professionally.  You really don't know enough at this time to be able to do it yourself.  You don't want to be responsible for the client losing his data due to your error.

In general, the biggest design difference you will find is with how you set up your forms.  Using DAO in VBA, you will find that there are additional gotcha's.  For example, Jet/ACE populate the autonumber immediately after the rs.AddNew so you can retrieve it within the .AddNew/.Update block if you need it.  That is because Jet/ACE are closely connected with Access.  ODBC is a disconnected process so Access doesn't see the autonumber until AFTER the .Update and you may need to make a call to the server to get it.  Another gotcha along the same lines is defaults.  Again, Access populates the defaults as soon as the first character is typed in a form so you see them immediately.  ODBC will not populate them until AFTER the record is saved so you have to be careful in your code if it depends on certain fields.  One way around this is to add the defaults to the form also.  Using that method, the form provides the defaults so you see them immediately after the first character is typed although, I still recommend defining them on the table because you will need them if you use append queries.

You will run into many posts complaining about how slow Access is with bound forms.  Those posts will be from people who took an existing application and simply swapped linked Jet/ACE tables for ODBC.  They never considered the difference in how Access acts with ODBC and so never adjusted their form design technique.

You also need to be careful with queries.  Keep in mind that SQL Server knows from nothin' about VBA functions or your own user defined functions.  So, whenever you use a function in a query, make sure you are using one that has a direct conversion to the set that comes with SQL Server.  It is possible to use VBA and UDF functions in the SELECT clause because Access can send the query to the server and then apply the functions to the return recordset but functions in other sections may cause Access to decide that it must do everything itself so it will request all rows from all tables and then perform the query locally and you want to avoid this at all costs.

Search for articles on optimizing Access for client/server.  I don't have any links handy but I'll look also.
RelinkODBC_POMS140228.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Pat Hartman, thanks so much for taking the time to give these in depth answers.  You have helped me out on many topics before and it is greatly appreciated.

You eased my mind on the portability of the Access app with you linking routine.

I can ease your mind by saying that I am not installing or setting up SQL on the production server, way out of my league.  My question related to any setup tips in my test environment.

TimeStamp - I now understand its usage but still am not clear on whether I populate this field or SQL handles that.  If I populate it, what kind of value do I load?
Anthony PerkinsCommented:
Everybody calls this a timestamp because that is what the data type is called.
"Everybody" maybe a tad exaggerated.  The truth of the matter is that the data type is deprecated and you should not be using it.  It is a bit like usint text and ntext in SQL Server, it is only a matter of time before they are removed entirely.  And we can only do the right thing here on this site and warn developers, if they then choose to continue to use "timestamp" is is their responsibility entirely.

Incidentally, I recommend you re-read the link I posted earlier.  Here is the specific quote (my emphasis:
Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms.

TimeStamp - I now understand its usage but still am not clear on whether I populate this field or SQL handles that.  If I populate it, what kind of value do I load?
It is covered in the link I posted and many others like it.  Simply put you cannot insert or update it (it would also defeat the whole point). A simple test will show you that:
CREATE TABLE TestTimeStamp (Col1 int , ts timestamp)
go
INSERT TestTimeStamp (Col1, ts) VALUES (3, 0x00000000000007D2)
go
DROP TABLE TestTimeStamp

Here is the error message:
Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
:
Anthony PerkinsCommented:
I also should have clarified my original comment: "That is it, no more no less and certainly nothing to do with locking.".  This was in response to the question: My understanding is that SQL will use this field to determine what to do in record locking situations. .  Perhaps what I should have stated:
"That is it, no more no less and certainly nothing to do with SQL Server locking."
I thought it was obvious at the time, but in hindsight it may not have been as clear as it should have.
mlcktmguyAuthor Commented:
Thanks for the clarification.  I completely missed the link you had imbedded in your answer.
mlcktmguyAuthor Commented:
Very Helpful, my thanks to both responders.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.