Doug Murphy
dw-murphy at cox.net
Fri Feb 6 19:06:32 CST 2009
Hi Dan, Refrence 2 makes this much clearer to me. I like to understand what is going on and this explains what is under the hood. Things should be much easier to design now. Thanks again. 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 3:52 PM To: 'Discussion concerning MS SQL Server' Subject: Re: [dba-SQLServer] SQL Server ODBC optimization Take a look at these articles: Create a Combobox that is Based on a Parameterized Stored Procedure http://support.microsoft.com/kb/304252 Optimizing Access Apps Linked to SQL Server http://msdn.microsoft.com/en-us/library/bb188204.aspx How to Bind Access Forms to ADO Recordsets http://support.microsoft.com/kb/281998 These helped me quite a bit. 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 4: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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com