John Colby
jwcolby at ColbyConsulting.com
Wed Jan 25 22:08:13 CST 2006
Jim, Please do send code samples. Let's just discuss this for a minute though. I am getting a bunch of different answers from build "pass through queries" to "open ADO record sets" (with painfully little detail). If I were to do a pass through query, what in the heck is a pass through query? These are the kinds of answers that I find frustrating, because they just don't provide enough substance to do anything with. If I knew what a pass through query was, then I would not even be having this conversation. How do I build one? What does it look like? How do I feed it parameters? How do I reference it in a form or combo? If I am going to use a recordset, I at least understand this idea, but can I (for example) build a function that returns an ADO recordset, then set the rowsource of the form / combo to "=MyADORs()"? Do I need to set a "form global" recordset object, get that set, then poke that RS object into the form/combo's property? When I build a stored procedure in SQL Server, how do I make that visible in Access? Does it "look like" a linked table, visible in the table tab? Is it something similar except in the query window? Is it just a connection string in code somewhere? Does anyone have any links to professionally written articles out on the internet that show the code, the stored procedures, graphics of the properties dialog for the object using the recordset/pass through query? Understand that I am a FE/ JET BE kinda guy up to this point and any answer that goes something like "use a pass through query" (as the old "microsoft joke" goes) while perhaps technically correct is simply useless. Thanks for the offer of details, please do send them my way. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, January 25, 2006 1:41 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Moving some object's recordsource to SQL Server Hi John: 1. Set the ADO objects 2. Create an ADO connection. 3. Create a SP on the SQL that will accept parameters. 4. Set up a recordset to received data from the SP. 5. When the recordset is populated use its data to fill the combo/list box (use the old combo box object if you do not have it I will send you a working copy but I think you do.) Every time a change is made in the request (filter) processes from 1 to 5 are invoked. If you need code samples I will be glad to send then to you. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: January 24, 2006 9:10 PM To: 'Access Developers discussion and problem solving'; dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Moving some object's recordsource to SQL Server I need a "step by step" if you will of moving some object to SQL Server - combo or form - where the Access query was filtered and the filter won't work in SQL Server. For example a reference to a control on a form. I know that I have to create a SP in SQL Server that accepts a parameter. How do I "connect" to that query from Access? Is it a n ODBC (or other?) link, just like a table? Then how do I feed the parameters to the SP out in SQL server. I am getting ready to do this and am way under prepared for this. Environment: SQL Server Express 2005 Access (Office) XP or 2003 Fes now talking to the SQL Server BE using the standard (ODBC I think) "links" that the upsize wizard created when it moved the tables. The FE seems to work as it did before except slower. Now if I can start replacing the slow "Access Queries over ODBC" with "SQL Server SPs with passed params????" I might be able to see what this is capable of. I just haven't a clue how to move to that "SP with Params". John W. Colby www.ColbyConsulting.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