Re: Getting SQL recordset into Access table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mary Chipman (mchip_at_nomail.please)
Date: 02/10/04 

Date: Tue, 10 Feb 2004 09:19:27 -0500

What you can do is create a pass-through query that will return the
result set from the stored procedure. You can do this in VBA/DAO by
supplying the parameter values in code:

cnn.Execute "myProc " & varParam1 & ", " etc.

This will return a read-only result set and you can base another,
regular query on that has a WHERE clause. Or you can dump the results
of the second query into a local Access/Jet table, where it's easy to
manipulate the data from a bound form. If you need to get this data
back into SQL Server, then you'll need to write VBA/ADO code to
execute INSERT or UPDATE statements.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Mon, 9 Feb 2004 19:55:27 -0800, "DaveCop"
<anonymous@discussions.microsoft.com> wrote:

>I have an Access application which needs to include/work
>with some data that resides on a SQLServer database.
>Currently I have access to a stored procedure on the SQL
>system which returns a recordset (ADO, but could use DAO).
>Since the sp on SQL needs to be called via pass thru (or
>at least I don't know of another way to execute it) and
>since it also requires parameters (e.g. a date but not
>normally todays date), I 'have to' (again AFAIK) build the
>proper pass thru sql statement in VBA and execute it from
>there.
>
>That all works fine (i.e. I get my data back in the
>recordset) but now I need to 'work on' this data.
>Basically I need to selectively move some or all of this
>data into an Access table. How do I 'move' the SQL
>recordset over into an Access table? I probably could open
>another rs in Access pointing to the proper Access table
>and do a field by field, row by row operation to populate
>the Access table, but I suspect this will be relatively
>slow. I hate doing things that way.
>
>Is there a better way to move the recordset into the
>table? Is there a way I can directly query the SQL
>recordset? Is there some way to have the SQL directly
>populate a table (even if that has to be on the SQL side
>[good!!!], I can probably get 'those guys' to modify the
>stored procedure)?
>
>[Personally since I typically only need a small sub-set of
>the data returned, I would prefer to just link to the sql
>table(s) and query them for the precise data I need but
>for other reasons (a long story) that is not possible.]
>
>Thanks for any help,
>Dave

Relevant Pages

  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Loading recordset at runtime
    ... difference in loading time for the entire recordset and the default 10 ... query or build the SQL string in VBA. ... Ah, ok, then use the forms "filter" option. ...
    (microsoft.public.access.forms)