Getting SQL data into Access table

  • Thread starter Thread starter StuckNovice
  • Start date Start date
S

StuckNovice

I asked this question previously on the external data
group and was referred to use DAO, but being a Stuck
Novice I am not sure how.

I have a SQL stored procedure that returns data (if I run
it interactively). I currently run this as a pass-thru
query and can see the data returned. But the SP needs a
date parameter (which changes) and pass-thru queries don't
allow 'dynamic' parameters. Thus I 'need' to build the
appropriate query string in VBA (based on user input) and
run the procedure. Of course I am a pure novice and not a
professional DBA so I can quickly get in over my head.

That part I (feel I) understand. But what I need to do is
take the data returned from SQL and 'move it' into an
Access table (BTW, I am using Access 2000). I have
been 'successful' at getting the SQL data into a
recordset, but the only way I know how to move data from a
recordset to a table is to loop through the records
transferring field by field. That is both 'ugly' and way
too slow.

I know there is a better way to connect the SQL
data 'directly' to an Access table (or that is what has
been told to me.) But I don't have much of a clue on how
to do it. While I am reasonably proficient in writing code
(but coming from an old FORTRAN world) and I can usually
grind my way through sql. As a side note, my install of
Access also has an error so I can't get to most of the DAO
(or ADO) help. I realize this is a known issue but the fix
hasn't worked on my machine, so I am even more handicapped
in trying to get help from Access/DAO.

Can someone provide a boost in how to use DAO/VBA to get
the results from the SQL SP into an Access table?

TIA,
Stuck
 
Check out this link for getting your DAO/ADO help pages working - there is a
table at the bottom of the page which tells you which files to copy from you
Office CD into given locations - do this and it should get them working!

http://support.microsoft.com/default.aspx?scid=kb;en-us;249065&Product=acc2000


With regards to your query - its hard to say not knowing any details of
table/data/parameters, but could you use an INSERT statement, e.g.:

dim sql1 as string

sql1 = "INSERT INTO <AccessTablename> SELECT * FROM <QueryName>

docmd.RunSql(sql1)

Kelly
 
You can modify the SQL code of the existing query.


CurrentDB.QueryDefs("QueryName").SQL = "Select ...."


Chris
 
Thank you for the reply and assistance.

Yes, I am well aware of the MS fix for the help problem
but for some other reason (that I nor our experts), this
fix hasn't worked for us. But I will look at it and try it
again.

Now on your suggested inline SQL code,
sql1 = "INSERT INTO <AccessTablename> SELECT * FROM
<QueryName>
the problem is I don't have a query name. I have to build
the query string in VBA since it is a pass thru query with
runtime determined parameters. My inline sql looks like:
strDate="03/01/04"
sql2="exec usp_GetSQLData 'P1', '" & strDate & "'"
docmd.RunSql(sql2)

where strDate is an input parameter to my VBA module
provided by the end user.

But what I need to somehow do is to get the result from
the sgl2 execution to be the data source for the sgl1
statement. I.e. somehow I (think I) need to nest the pass
thru query as the data source for a make table (or insert)
query. Problem is I don't know the 'syntax' for doing this.

Thanks again for your input.
Stuck
 
Thanks for your reply and assistance.

Can you please expand on your comments. It sounds/looks
like you are suggesting something I have been thinking of
doing and trying but I don't know the syntax.

Like I originally posted, I can run the pass thru
query 'fine' but where I am struggling is getting the data
into an Access table. I am trying to figure out how to
nest a pass thru query inside another query as the data
source for the second query (i.e. a make table (or insert)
query which uses the pass thru query as its data source.)

Using inline SQL my pass thru looks something like this
dim strDate as string
dim sql2 as string

strDate = "03/01/04"
sql2 = "exec usp_GetSQLData 'P1', '" & strDate & "'"
docmd.runsql sql2

Or I could do it...

CurrentDB.QueryDefs("QueryName").SQL = "exec
usp_GetSQLData 'P1', '" & strDate & "'"


That works but how do I use that as a data source for
another query? In the second case, how do I use the
QueryDefs object as the data source for a make table query?
 
Back
Top