troubleshooting Question

using ADO to return records into Access from SQL server

Avatar of frankytee
frankyteeFlag for Australia asked on
Microsoft SQL Server
6 Comments1 Solution791 ViewsLast Modified:
Currently, in MS Access, using DAO/ODBC I "download" data returned from an SQL Server stored procedure into my MS Access front end application. The resulting data drives a user report.

1) I create a pass thru query where the sql is "EXEC spMyprocedure..." etc
2) I then create a make table query which selects from the pass thru query
3) I run the make table query which will then create a table in MS Access containing the resultset of the stored procedure.

The sample DAO code is:

Dim db as database, sConnect as string
Dim qdfSp as querydef, sqlSp as string     ‘query which executes the sp
Dim qdfMake as querydef, sqlMake as string     ‘query which selects from sp and makes a temp table

Set db = currentdb
sConnect = "....."     ‘whatever ODBC connection string

‘now create and define query which calls the sp
sqlSp= "EXEC spMyProcedure " & anyRequiredParameters     ‘define string which executes sp
‘either delete and recreate queries or redefine it
Set qdfSp = db.Createquerydef("spQuery")
with qdfSp
     .connect = sConnect      ‘it’s a pass thru query
     .sql = sqlSp
     .ODBCTimeout = 0     ‘no time limit
     .ReturnRecords = True
end with

‘now create and define query which makes a table from selecting the sp
sqlMake= "Select .. (fields etc from the resulting output of sp) ... INTO tmpTable FROM " & qdfSp.Name     'select from the pass thru query

Set qdfMake = db.Createquerydef("makeTableQuery")
with qdfMake
     .sql = sqlMake
     .ODBCTimeout = 0     ‘no time limit
     .ReturnRecords = True
     .Execute     ‘execute the make table query
end with

set db=nothing
----------------------------------------------------------

Now as MS have been saying the ADO is the future and that DAO is becoming obsolete, I imagine the above must be possible using ADO. However, after experimenting with ADO, I can't get it to create or insert into an Access table that is easily achieved using DAO. The only way possible I can think of is to create an ADO recordset, and then have to loop thru each record and insert individually into an Access table but that would be inefficient when there are thousands of records involved.
Can someone show  me the ADO code to do this efficiently without inserting individual records or do I simply stick with DAO?


ASKER CERTIFIED SOLUTION
niklausj

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros