saving recordset

J

Jamesmor

Is there a way to save a recordset as a table? I've found ways to save them
as local files outside of Access, but not a way to save them as a table in
Access. The reason I ask is because I do a lot of heavy queries to our SQL
server, and when I use linked tables it creates a lot of network traffic, so
I'm trying to build all my queries and all my connections in ADO to reduce
the network traffic.

Any help is greatly appreciated.
 
D

Douglas J Steele

When you say you build all your queries in ADO, are you saying you're not
using pass-through queries? A pass-through query should be just as
efficient, and you can use it as the source for a make table query.
 
J

Jamesmor

I'm not using pass-through queries - hadn't thought of that. A simple sample
of the way I have been doing it is below.

strConn = "Driver={SQL
Server};Server=*******;Database=******;Uid=******;Pwd=*******;"

adoConn.Open (strConn)

strSQL = "SELECT accounts.account_id, accounts.email" & _
" FROM accounts"



objRs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

objRs.e

'Clean up
objRs.Close
Set objRs = Nothing


Again, it's a very simple sample from when I first started to work on this.
 
O

Ofer

Something you can try, involved pass through queries

Create an empty pass through query, name it GlobalPass
Dim db As Database , MySet As Recordset , myqs As QueryDef, NewTableName
as string

Set db = CodeDb()
Set myqs = db.QueryDefs("GlobalPass")
myqs.ReturnsRecords = True
' Define the execute line for the SP
myqs.sql = "SELECT accounts.account_id, accounts.email FROM accounts "
' Run a create table query, using the query above and assigning a new
tablename
NewTableName = "Choose A Name Here For A New Table"
Docmd.RunSql "SELECT GlobalPass.* INTO " & NewTableName & " FROM GlobalPass"
 
Top