[dba-SQLServer] SQL Server ODBC optimization

Jim Lawrence accessd at shaw.ca
Sun Feb 8 17:37:25 CST 2009


Hi Doug:

Here is a link to a demonstration of how to connect MS SQL DB and populate a
local table. This demo is not exactly what you need but it might give you
some ideas of how handle data from BE to FE. :
http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo
rts.asp

There are some very interseting ways to populate combo boxes from your MS
SQL BE. This subject has been covered in detail, on this or the Access list
in the past and finding a reference should not be difficult using the
archive section of the DBA. Check the following link out:
http://www.databaseadvisors.com/archive/archive.asp

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Friday, February 06, 2009 2:31 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization

Hi Dan,

Thanks. I have set up pass through queries and stored procedures to pull
data.  What I am missing is how to update the data in the front end or
insert new records. In Access we would just set a table or query as a forms
record source and it was taken care of.  When I set a forms record source to
a view or SP it isn't updatable. My concern is that linking directly to the
SQL server tables will cause the FE to pull over a lot of records.  

Doug

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 06, 2009 2:16 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization

Your thoughts on the stored procedure are correct!  Another way (I believe)
is to create a view on the server (equivalent to an Access query), and then
link to that to pull up a limited dataset.  But, you won't be able to modify
that view/query dynamically (again - I believe).  And that's where the
stored procedure comes in.  You can design a stored procedure and then call
it by passing some variables to dynamically change it's output.

You do not need to convert your MDB to an ADP to use stored procedures.  In
fact, the only reason I can see to use an ADP is that it can manipulate SQL
Server objects in design view.  But Management Studio for SQL Server
2005/2008 works very well!  Management Studio for SQL Server wasn't
available until SQL Server 2005.  I prefer using MDB's because I can then
use temp tables in the FE.

Now the hard part - learning how to use a stored procedure to get a set of
data to fill a combobox/listbox or create a recordset for a form or report.
This is where the learning curve comes in, and there's really no way to
avoid that.  (The books I mentioned helped me a lot.)

On the other hand, John Colby has just hit the top of that learning curve,
so he may have something to say!  ;-)

Good Luck,
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Friday, February 06, 2009 3:55 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization

Hello Dan and others,

I'll ask a little more specific question.  I have an Access FE connected to
SQL server BE via ODBC. The BE tables can hold many records so I was
thinking I could put a stored procedure on the BE to limit the number of
records coming over the network to the FE to just those of interest.  The
problem I have found is that the records are not updateable and I can not
insert new records. I was trying to not use linked tables, but that seem
unavoidable.  I am not in the position of being able to convert the FE to an
ADP so need to keep the forms etc connected to updatable record sources.
How do you folks approach this type of situation.

Thanks.

Doug

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 06, 2009 6:35 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization

Hi Doug,

A few months ago I went through some conversion activities.  This is what I
learned:

1) To upsize an Access database to SQL Server, use the SQL Server Migration
Assistant (SSMA) for Access.  This is a free MS utility which is more
advanced than using the upsizing wizard, and will upsize queries in Access
to Views in SQL Server.  The manual for this utility has some good
instructions for what needs to be done prior to upsizing.  

2) On a LAN, the data transfer speed is perhaps 10% slower using Access FE /
SQL BE using ODBC table links.  However, on a LAN, no one will notice.  This
is an easy solution because your code can simply refer to the SQL table
links instead of the Access table links as long as the link names are the
same.

3) On a WAN, you don't want to use ODBC table links to SQL Server -
performance would be poor (but perhaps usable).  You'll want to use an OLEDB
Provider to make the connections.  Using an OLEDB Provider means that you'll
be rewriting your code significantly, because you won't have any table links
in the FE that your code can use!  This is a big learning curve - a good
start is 'Microsoft Access Developer's Guide to SQL Server.'  This is an
older book that uses Access 2000, but it's still valid and very helpful to
learn the basics of moving data between the FE and the BE.  Another good
book is 'Access 2002 Enterprise Developer's Handbook'.  Once you get this
running, your data transfer speed will increase by at least 5X, probably
more like 10X.

Good Luck!
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Thursday, February 05, 2009 6:42 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] SQL Server ODBC optimization

Folks,

I sent this to the AccessD list with no responses. Possibly some one on this
list can share their insight in how best to use an Access front end
connected to a SQL Server BE via ODBC. What I am trying to learn is how to
do this most efficiently from the SQL Server perspective.  Do you use
queries from Access, move all queries to SQL server and pass parameters
where required, etc.

I am know that there are some design guidelines that should be followed with
designing a system with an Access front end connected to a SQL Server back
end via ODBC to obtain good performance. This is a pretty broad question,
but could any of you folks who do this on a regular basis provide a list of
the techniques and design approaches you follow or point me to some
references? I am not looking for a treatise on the subject just a list of
items to be aware of and good design practices.

Thanks in advance.

Doug

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list