dynamic query to insert rows from sql server attached table to mdb table

  • Thread starter Thread starter webdb_advisor
  • Start date Start date
W

webdb_advisor

Hi -

I am:

working in VB6
have a sql server db with table attached to MDB

and

Need to insert rows from SQL Server into the Access table.

I have reviewed the FAQs recommending ADDNEW and DTS ... but I need to
make sure that there is no other efficent way (iterating and perfroming
ADD NEW is WAY TOO Slow)

I can insert into the MDB table from a view ... but that view needs to
filter using a Where clause ...

If I was doing this in SQL Server I would insert using a stored proc
that supplies the where clause variable as a parameter , but I have not
identified a way to do this from VB using ADO or DAO -- particularly a
way that will take advantage of pre-compiling the query in a Stored
Proc.

A fresh response or a pointer to a documented solution to this question
will be appreciated.

Thanks,

Steve
 
Steve,

You should set up a stored proc that returns a recordset of the "where"
records you want to add to the access table. You can call this procedure thru
a SQL pass thru query. I will call the query "GetSQLData". Next you would
make a make table query that will have the GetSQLData as its recordset.

Then you would attach to the access DB. You would change the pass thru
query via a line of code like ...

ObjAccess.QueryDefs("GetSQLData").SQL = "Exec SP Param1, Param2 …"


The second query would be something like
Select * from GetSQLData into NewTable

Next run the second query that is the make table query.

docmd.openquery "SecondQuery"

you might want to turn warning off by using this code:

docmd.setwarnings = false

Make sure you turn warning back on after the execution.

Good Luck
 
Thanks, Stewart -- I'm going to try it now ... but it will have to be
an append query (insert into) not an make table (select into) Query
def.

I also need to use this technique for using the output of a select
procedure as the transactions for an update query.

Any caveats ?

Steve
 
Steve,

I am not sure I undersand what you are asking in the second part. Have you
had any success in the first try? If so will you let the group know that
your problem was answered.

Regards
 
Yes ! It worked great ... Thanks !!!

Result set from Stored Proc on SQL Server is exposed to Access by
making that Stored Proc the property of a SQL Pass-through Query in
MDB .... that Pass Through Query is then datasource for Action Query to
insert data into MDB table !!!!

Very cool !

Steve
 
Back
Top