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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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:
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.
:
"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.
:
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.
"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.
ASKER
Thanks for the clarification. I completely missed the link you had imbedded in your answer.
ASKER
Very Helpful, my thanks to both responders.
ASKER
Does anyone have insight on using on using Bound vs non-bound forms?
or
Automatically linking SQL tables based on a parameter card?