By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

Getting data into Access Table from SQL

P: n/a
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!!!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <fo******@yahoo.com> wrote in message
news:d5*************************@posting.google.co m...
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!!!

Nov 12 '05 #2

P: n/a
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" <kn*****@hotmail.com> wrote in message news:<_%*****************@news.uswest.net>...
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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.