Darryl Collins
Darryl.Collins at iag.com.au
Mon Jan 24 17:00:16 CST 2011
_______________________________________________________________________________________ Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________ John, I could be off track here, but from memory you need to have the PK set up correctly in SQL Server (bigint?) to be able to view the data in Access 2000. Or is that just tables? sorry, been a while since I have used A2000 and SQL Server BE. hth a bit cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, 25 January 2011 9:04 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access 2K to SQL Server I am running into something that I have never seen before. When I try to create a DSN back to the sql server I end up seeing the server, but when I select the server I only see a small set of existing views, 8 or so. there are hundreds of tables and dozens of views but I can't see any of them. I don't know why, or how SQL Server limits what I can see for the DSN build process. I am assuming that it has to do with security but this is new to me. John W. Colby www.ColbyConsulting.com On 1/24/2011 4:25 PM, Rusty Hammond wrote: > John, > > Have you tried an Access query tied to the linked view where the query > provides the filtering? I'm sure I'll be corrected if I'm wrong but it > should return from SQL only the records you need. > > If the dataset being returned can be read-only (no editing) then you can > use a pass-through query in Access to call a stored procedure or send a > SELECT statement directly to the SQL server. You can edit the SQL of > the pass-through query in code just like any other query. To setup a > pass-through create a blank query, go to the SQL view, go to the Query > menu, choose SQL Specific, then Pass-Through. Then right-click on the > title bar of the query window, go to Properties, Use the build button on > the ODBC Connect Str property to build your connect string to the SQL > server, set Return Record to Yes. > > HTH, > > Rusty > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Monday, January 24, 2011 3:09 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Access 2K to SQL Server > > And can I link to a stored procedure? How to I send the parameter to > the stored procedure. > > You are just a little too light on the actual details for me to get this > done. > > I know how to create stored procedures, and I know the syntax in the > stored procedure to pass in a parameter. I do not know the syntax in a > stored procedure to return a recordset. And I haven't a clue how to use > any of this on the Access side of things. > > Thanks, > > John W. Colby > www.ColbyConsulting.com > > On 1/24/2011 4:00 PM, David McAfee wrote: >> CREATE PROCEDURE stpSomeNameHere (@AsOfDate AS DATETIME) AS SELECT * >> FROM vwSomeView WHERE SomeDate>= @AsOfDate >> >> Modify the querydef as needed for the input parameter in Access. >> >> >> >> On Mon, Jan 24, 2011 at 12:50 PM, > jwcolby<jwcolby at colbyconsulting.com>wrote: >> >>> One of my clients is mired in Access 2K. He is linking to views in >>> SQL Server but these are fixed views that at this point are pulling >>> hundreds of thousands of records when he really only needs the last X > >>> days, or for Claim X etc. >>> >>> How can I create a view (or stored procedure) out in SQL Server that >>> accepts a parameter such as a date or a claim ID and allow sql server > >>> to perfrom the filter and return a small result set. >>> >>> REMEMBER this is A2K. It is my understanding that A2K does not allow > >>> some of the fancy stuff that later versions of Access allows - like >>> binding a form to an ADO recordset and having it be R/W. >>> >>> Any help would be hugely appreciated. >>> >>> -- >>> John W. Colby >>> www.ColbyConsulting.com >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > ********************************************************************** > WARNING: All e-mail sent to and from this address will be received, > scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc. > corporate e-mail system and is subject to archival, monitoring or review > by, and/or disclosure to, someone other than the recipient. > ********************************************************************** > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________________________________________________ The information transmitted in this message and its attachments (if any) is intended only for the person or entity to which it is addressed. The message may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information, by persons or entities other than the intended recipient is prohibited. If you have received this in error, please contact the sender and delete this e-mail and associated material from any computer. The intended recipient of this e-mail may only use, reproduce, disclose or distribute the information contained in this e-mail and any attached files, with the permission of the sender. This message has been scanned for viruses. _______________________________________________________________________________________