June 19, 2011 at 11:20 am
Robert Magrogan (6/19/2011)
Microsoft Access can be a marvelous Front End application to a SQL Server database, IF you plan well.
In my opinion even if you do plan well Microsoft Access can cause a lot of problems.:w00t:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 20, 2011 at 1:36 am
What's a timestamp and how do you add it to every table?
June 20, 2011 at 6:08 am
grovelli-262555 (6/20/2011)
What's a timestamp and how do you add it to every table?
Please refer to the following article:
http://www.sqlteam.com/article/timestamps-vs-datetime-data-types
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 20, 2011 at 7:59 am
The timestamp is a binary number used to determine whether or not someone has changed the data between the time you fetched the record and the time you updated the record. Its purpose is to prevent us from over writing someone else’s changes.
The following script can be used to add the TIME STAMP column to one table at a time.
ALTER TABLE yourtablename ADD TIMESTAMP
The below script will add the TIMESTAMP column to all tables in your database. WARNING! THIS WILL ALTER ALL YOUR TABLES! This script assumes you do not already have any TIMESTAMP columns in your tables.
DECLARE @TableName TABLE ( query VARCHAR(1000) )
INSERT INTO @TableName
SELECT 'Alter table ' + Name + ' ADD TimeStamp'
FROM sys.objects
WHERE Name <> 'dtProperties'
AND type = 'u'
--The below line is used to look at the data prior to executing it.
SELECT *
FROM @TableName
--WARNING - this script will ADD the TIMESTAMP COLUMNS TO ALL your tables!
--Comment out the RETURN line to auto run this script.
RETURN
DECLARE @sql VARCHAR(1000)
SET @sql = ''
WHILE EXISTS ( SELECT *
FROM @TableName
WHERE @TableName.query > @sql )
BEGIN
SELECT @sql = MIN(@TableName.query)
FROM @TableName
WHERE @TableName.query > @sql
EXEC(@sql)
END
June 20, 2011 at 8:11 am
Thanks! 🙂
June 22, 2011 at 11:54 am
Robert Magrogan (6/20/2011)
The following script can be used to add the TIME STAMP column to one table at a time.
ALTER TABLE yourtablename ADD TIMESTAMP
The below script will add the TIMESTAMP column to all tables in your database. WARNING! THIS WILL ALTER ALL YOUR TABLES! This script assumes you do not already have any TIMESTAMP columns in your tables.
DECLARE @TableName TABLE ( query VARCHAR(1000) )
INSERT INTO @TableName
SELECT 'Alter table ' + Name + ' ADD TimeStamp'
FROM sys.objects
WHERE Name <> 'dtProperties'
AND type = 'u'
--The below line is used to look at the data prior to executing it.
SELECT *
FROM @TableName
--WARNING - this script will ADD the TIMESTAMP COLUMNS TO ALL your tables!
--Comment out the RETURN line to auto run this script.
RETURN
DECLARE @sql VARCHAR(1000)
SET @sql = ''
WHILE EXISTS ( SELECT *
FROM @TableName
WHERE @TableName.query > @sql )
BEGIN
SELECT @sql = MIN(@TableName.query)
FROM @TableName
WHERE @TableName.query > @sql
EXEC(@sql)
END
You are going to grant permission to an Access Account to allow DDL?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 23, 2011 at 6:51 am
If you wanted to add a timestamp to every user table that does not already have one, you could use this script:
SELECT 'ALTER TABLE [' + OBJECT_NAME(t.object_id) + '] ADD my_timestamp TIMESTAMP NOT NULL'
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT 1
FROM sys.columns
WHERE system_type_id =
(
SELECT system_type_id
FROM sys.types
WHERE name = 'timestamp'
)
AND object_id = t.object_id
)
ANDt.type = 'U'
ORDER BY 1
which generates an ALTER script for each table...you would then inspect the list to remove any tables (e.g. import or staging tables) where adding a column would break something...and then you can run the ALTER scripts to add the timestamps & have a record of what was done.
June 26, 2011 at 4:40 pm
If you do add a column to a table, make sure you rebuild at least the clustered index.
As a side bar, you might want to avoid using ORDER BY X where X is an integer. First, it's considered to be a bad practice by lot's of folks for more than one reason and, second, it's been deprecated. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2011 at 2:05 pm
I've had extensive experience using this development model: ACCESS 2003 front-end --> SQL 2005 back-end.
It actually performs very well if both sides are done properly.
Unless you did something really REALLY bad in SQL, it's likely a front-end issue.
You did say you had set-up indexes in SQL over the primary search fields.
In your ACCESS app, are you coding full %LIKE% queries, or LIKE% (starts with), or searching by the full fields contents?
Are you connecting to tables using ODBC, OLE DB, or Pass-Thru queries?
Do you implicitly load FORM's initial recordset during LOAD event, or do you put empty FORM then prompt for search values / issue the query?
Big difference here, depending on back-end data and other network considerations.
June 27, 2011 at 6:33 pm
Jeff Moden (6/26/2011)
As a side bar, you might want to avoid using ORDER BY X where X is an integer.. . . and, second, it's been deprecated. 😉
Jeff,
Can you cite a reference for this? According to the 2008 R2 docs, you can still use it unless using a ranking function. TIA
June 27, 2011 at 7:17 pm
repicurus (6/27/2011)
I've had extensive experience using this development model: ACCESS 2003 front-end --> SQL 2005 back-end.It actually performs very well if both sides are done properly.
Unless you did something really REALLY bad in SQL, it's likely a front-end issue.
How many concurrent users did you have?
What was the size of your database and tables?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2011 at 7:41 pm
Welsh Corgi (6/27/2011)
repicurus (6/27/2011)
I've had extensive experience using this development model: ACCESS 2003 front-end --> SQL 2005 back-end.It actually performs very well if both sides are done properly.
Unless you did something really REALLY bad in SQL, it's likely a front-end issue.
How many concurrent users did you have?
What was the size of your database and tables?
In our FL office, hitting the same main operational SQL Server (2-node cluster):
575+ users: all of whom were using the "Paperless Sales Entry/Tracking System" ACCESS app, and 2 more ACCESS apps for Verifications.
Numerous SSRS reports, SSIS packages, and SQL Server Agent jobs running 'round the clock.
Client Web Portals - mixed bag of classic VBS/ASP web apps and increasingly more and more C#/ASP.NET apps.
In CA and NV offices, similar environment but with about half the users and running less hours each day.
We had many databases, including one for main telephony system.
Largest database was approaching 500 GIGS; smallest - 100 MEGS (?); median size - about 5 to 7 GIGS as I recall.
Tables, especially telephony history had over 50 million rows, and archive history about 120 million.
Table sizes used by ACCESS apps ranged from several hundred to millions of rows.
Data entry/update FORMS were designed to select one record or limited group and were mostly UNBOUND.
Search front-ends would put an empty FORM and prompt for search parameters with 1 to 2 arguments required prior to parsing a query string and issuing against the server.
It was a call-center, which is a bit like any "normal high transaction volume" business but on crack. 😉
May 8, 2013 at 11:59 am
http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.html
It is lengthy, but it covers the subject well. This should cover 95% of how to make it work the most efficient way.
Access 2007 has some great advantages to translating Access SQL to T-SQL to run in SQL Server.
Access 2010 with SQL Server back end is very powerful.
My method is to use code to: wipe clean all the linked tables.
Create linked tables in Access using the SQL Server Native Client. It is a free MS download. Avoids using the ODBC dialogue box.
When many tables are joined and used often - Create a view on SQL Server and use it as an attached table.
When writing a query in MS Access, there are do's and don'ts.
Do include a Where clause. The SQL Native Client will convert it to native T-SQL and only return the record(s).
This is true for Form filters and actual Access SQL.
Avoid the Like and other general statements.
Never use the MS Access SQL functions. (e.g. IIF([Name] > [Register], "New", "Old")) There is no equal in T-SQL.
Instead, run a nice SQL statement, return the recordset, then run a 2nd MSAccess query against the resulting recordset to use the custom funcitons and formatting we all love in Access. This is called "Topping Off the Query".
Rapid Prototype Cloud Based Applications with MS Access.
Running MS Access on a Citrix Server nearby to the SQL Server is extremely fast. Adding a new MS Access 2010 (or 2013) application can be very efficient. With Citrix, MS Access runs on PC, Mac and other platforms with very little bandwidth.
An application can be prototyped and securely distributed to get data entry and valuable customer feedback.
The rich user environment, class code modules, and countless events can lead to complex rule-based applicaitons reducing the need for the end-user to refresh the form.
If the user community is under 250 concurrent users, an Access front-end with a SQL Server back end can be a very fast and efficient soltuion. When combined with Citrix, it can be deployed very quickly.
May 31, 2013 at 10:56 am
My post on SQL Server Native Client 11.0 for MS Access
http://www.access-programmers.co.uk/forums/showthread.php?t=247756
SQL Linked Tables are always much faster than Access linked tables for me.
Here is my short list to MS Access programmers.
1. Design in MS Access then use the free SQL Server Migration Assistant for Access
This tool looks at a lot of things in the Access table and will get you 92% there.
While I am no stranger to MS SQL Server, why not let automation do the bulk of the work and data type conversions.
2. Download the SQL Server Native Client 11.0 then write code to:
- destroy all linked tables
- recreate DSN-Less linked table objects
I keep one local Access table with the name of the table to link and a check box for if it should be linked.
One subroutine cleans it up and re-establishes the linked tables.
3. Never use Access SQL functions in the queries. The Access query language allows things like the Immediate IF (iif) statement in a query.
For this and all the other Access SQL functions, there is no direct T-SQL translation. That can really slow down a query.
Write an Access generic query with parameters and the TSQL conversion of the Native Lanugage driver will not require any TSQL or even Views.
That includes multiple table joins. There are some great articles that show the fantastic translation of complex multiple table joins vs TSQL.
There is a lot of misinformation about data traffic and such from Access. To be clear, we exclude the IIF and other Access SQL functions from this.
Since Access 2007, table joins with SQL Server Native Client are evaluated and created on the SQL Server side. Have read some of the detailed analysis. For the majority of cases, it is not necessary to create the joins anymore. I use to write something called a SQL PassThrough Query in MS ACCESS. Take the T-SQL string - modify it in code (e.g. change the With statement or parmameter) then pass it to SQL Server and Execute it.
The SQL Server Native Client now appears to make this unnecessary and maybe even less efficient.
4. Get your data from SQL Server, then "Top it off" with Access functions locally. This can be Temp tables or many other methods.
My Access Applications link to multiple databases. They have hundreds of concurrent users. The Access front-end runs on a Citrix Server linked for example to the SQL Server close by. The Citrix client works for PC, Apple and other platforms. The field engineers often have a full rich front end experience with about 16K bandwidth (in rural areas). Basically, use KISS with some decent planning and be very productive.
Granted, my front-end is only 70 MB and the databases are only a few gigabytes. The front-end is very complex regulatory rules.
There is some fantastic advice in the other post. Stick with the basics and learn some of the advanced concepts as needed.
For Access Programming connected to SQL Server back end, visit us at the link above.
This site is the best for all SQL Server quesitons. I truly marval at the level of experts found here.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply