saving recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
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"
 
Back
Top