Doug Murphy
dw-murphy at cox.net
Fri Feb 6 15:55:01 CST 2009
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