Getting data into Access Table from SQL

First please note I am a novice at VBA and not even that experienced
with DAO/ADO and MS-SQL. Any assistance is appreciated. That said...

I have an application written in Access w/ VBA. I need to get some
data out of a SQL server and into an Access table. I don't know
anything about the SQL server data model or structure. I don't have
access rights to link to the SQL table. What I do have is the rights
and information to run a stored procedure on the SQL server. This sp
returns the data I need in a View.

I have built and executed (statically) a stored query in Access which
can and does return the data. This query is written as a pass-thru
query which executes on the SQL box. Unfortunately, the SP requires a
parameter (a date) to return the correct data. From what I have been
able to find out so far is that the only way to 'pass' a parameter to
a pass-thru query is to build the correct SQL string in VBA and then
execute that SQL command.

I have been able to do just that and get the SQL data into a recordset
in Access BUT a recordset doesn't do me much good because I need to
get the data into a table inside of access. From the record set I
could step through each record and field and move the data into a
table piece by piece. But this would (probably) horrendously slow (at
least some testing showed it would).

Someone had told me I could use DAO and code using the querydef
object. The I could use this querydef as the datasource for a second
query which would create and/or populate the local table. This sounds
very promising but I am at a loss about how to actually do it and the
person who suggested the idea is not available.

The pass-thru query looks something like:
exec usp_GetData 'StaticParam', '2/13/04'

where there are static parameters and also a 'dynamic' date (i.e. will
be different from run to run.)

Sorry for being so long winded and again thanks for any help

Fore!!!

re: Getting data into Access Table from SQL

SenseForAll,
I'd look at a Jet .mdb with the pass through query handing data to a Jet
based make-table query on the first pass and then an append query on
subsequent passes. If you need the history of each import then don't
truncate your Access table with each run. If not, run a macro which uses a
delete query to dump the data from the last run, then your append to post
data for the current run. It shouldn't need VBA, though it would be better
in code because exception handling is better.

"SenseForAll" <fore6996@yahoo.com> wrote in message
news:d538ef6f.0402181803.6e690c1@posting.google.co m...[color=blue]
> First please note I am a novice at VBA and not even that experienced
> with DAO/ADO and MS-SQL. Any assistance is appreciated. That said...
>
> I have an application written in Access w/ VBA. I need to get some
> data out of a SQL server and into an Access table. I don't know
> anything about the SQL server data model or structure. I don't have
> access rights to link to the SQL table. What I do have is the rights
> and information to run a stored procedure on the SQL server. This sp
> returns the data I need in a View.
>
> I have built and executed (statically) a stored query in Access which
> can and does return the data. This query is written as a pass-thru
> query which executes on the SQL box. Unfortunately, the SP requires a
> parameter (a date) to return the correct data. From what I have been
> able to find out so far is that the only way to 'pass' a parameter to
> a pass-thru query is to build the correct SQL string in VBA and then
> execute that SQL command.
>
> I have been able to do just that and get the SQL data into a recordset
> in Access BUT a recordset doesn't do me much good because I need to
> get the data into a table inside of access. From the record set I
> could step through each record and field and move the data into a
> table piece by piece. But this would (probably) horrendously slow (at
> least some testing showed it would).
>
> Someone had told me I could use DAO and code using the querydef
> object. The I could use this querydef as the datasource for a second
> query which would create and/or populate the local table. This sounds
> very promising but I am at a loss about how to actually do it and the
> person who suggested the idea is not available.
>
> The pass-thru query looks something like:
> exec usp_GetData 'StaticParam', '2/13/04'
>
> where there are static parameters and also a 'dynamic' date (i.e. will
> be different from run to run.)
>
> Sorry for being so long winded and again thanks for any help
>
> Fore!!![/color]

re: Getting data into Access Table from SQL

I am doing this in an Access .mdb but AFAIK you have to build the pass
thru query in VBA because you have to constuct the paramaters on the
fly as parameters aren't allowed in a normal pass thru query because
Access doesn't do any processing on the pass-thru query, thus there is
no way to pass a date value/string into the query without constructing
it on the fly. Also a pass-thru query can't (or I don't know how) to
use it as the data source for a make/append table query.

If I am in error please explain how to do this.

"Alan Webb" <knogeek@hotmail.com> wrote in message news:<_%XYb.133$LH3.61456@news.uswest.net>...[color=blue]
> SenseForAll,
> I'd look at a Jet .mdb with the pass through query handing data to a Jet
> based make-table query on the first pass and then an append query on
> subsequent passes. If you need the history of each import then don't
> truncate your Access table with each run. If not, run a macro which uses a
> delete query to dump the data from the last run, then your append to post
> data for the current run. It shouldn't need VBA, though it would be better
> in code because exception handling is better.
>[/color]