Getting SQL recordset into Access table

[DaveCop]

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

 

[Mary Chipman]

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

 

[DaveCop]

How? I have the VBA/ADO code to put the SQL info into a
recordset (rst). But I can't seem to query against the
recordset. After my pass thru query gets the data (and I
have verified it does collect the correct data), how do I
query against that? I tired:

cnn.Open
Set rst = cnn.Execute("exec SqlSP 'P1', '" & dtDate & "'")

sqlcmd = "INSERT INTO tblAccessTable SELECT ['rst'].*
FROM 'rst'"
DoCmd.RunSQL sqlcmd

but I get a runtime error - 3450 "Syntax error in query.
Incomplete query clause". I have tried different syntax
with no luck. If I try
"INSET INTO tblAccessTable SELECT * FROM rst" I get run
time error 3078 "MS Jet cannot find input table or query."

So while I think I understand the jist of your suggestion,
I can't find a syntax that will work.

Thanks again for the help.
Dave

 

[Mary Chipman]

Ditch the recordset concept -- it won't work. I said *nothing* about
using recordsets in my previous posting -- please read it again. You
can create a pass-through query or modify one in DAO using the
QueryDef object and setting its .SQL property and connection property.
Then you can do the same with a regular QueryDef object, setting it's
source to the pass-through query and specifying a WHERE clause (also
by manipulating the .SQL property). You simply can't mix SQL
statements like SELECT and Recordsets -- two entirely different
beasts. Look at the Access/VBA help file for DAO QueryDef objects.

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

How? I have the VBA/ADO code to put the SQL info into a
recordset (rst). But I can't seem to query against the
recordset. After my pass thru query gets the data (and I
have verified it does collect the correct data), how do I
query against that? I tired:

cnn.Open
Set rst = cnn.Execute("exec SqlSP 'P1', '" & dtDate & "'")

sqlcmd = "INSERT INTO tblAccessTable SELECT ['rst'].*
FROM 'rst'"
DoCmd.RunSQL sqlcmd

but I get a runtime error - 3450 "Syntax error in query.
Incomplete query clause". I have tried different syntax
with no luck. If I try
"INSET INTO tblAccessTable SELECT * FROM rst" I get run
time error 3078 "MS Jet cannot find input table or query."

So while I think I understand the jist of your suggestion,
I can't find a syntax that will work.

Thanks again for the help.
Dave

-----Original Message-----
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

 

[DaveCOP]

You are quite correct. I read results set buy I saw
recordset. My error. I did not think that the recordset
would work unless I stepped through each element which
would be ugly and slow.

I have the 'string' to create the pass thru query (with
the proper parameters built into it from VBA). But I am
struggling with the syntax to do what you are
recommending. (Though I love the sound of your
suggestion). Another (kinda separate) issue is that I
can't access help for ADO or DAO. I know this is an
identified problem with A2K, but I have tried the fixed
and had our site support look at the problem but so far no
luck actually getting it fixed.

Can you help me out some with the syntax around what you
are suggesting (any close example would be great.) I am
sorry to be such a novice but database programming is not
my real job, something I have recently been asked to do
for a special project.

Thanks again for any help,
DaveCOP