'dumping' a Recordset

  • Thread starter Thread starter nh
  • Start date Start date
N

nh

Can someone tell me how to 'dump' a recordset into a new table?

If I have a resordset, rs how can I save it as a new table in my database?

Thanks

Nick
 
not sure how you are generating the rs but usually this is
done with a select sql statement.
change the statement from SELECT.... to SELECT INTO
TABLENAME........
good luck.
 
Jeff said:
Copy and paste?
Click to expand...
Nah, you cannot copy a RecordSet!

You could tweak my dumpRs routine to write to a table instead of the
debug window.

Sub dumpRs(rs As Recordset)
If rs.RecordCount = 0 Then Exit Sub 'don't bother
rs.MoveFirst
dumpRecord rs, True
Do Until rs.EOF
dumpRecord rs
rs.MoveNext
Loop
End Sub

Sub dumpRecord(rs As Recordset, Optional fieldnames = False)
Dim fd As Field
For Each fd In rs.Fields
If fd.OrdinalPosition > 0 Then Debug.Print ", ";
If fieldnames Then
Debug.Print fd.Name;
Else
Debug.Print fd.Value;
End If
Next
Debug.Print
End Sub

Or, if you want something more cut to the job:

Sub dumpToTable(rs As Recordset, cTable As String)
Dim fd As Field
Dim fdE As Field 'to enum the recordset
Dim td As TableDef
Dim db As Database
Dim rsT As Recordset 'target recordset on new table
If rs.RecordCount = 0 Then Exit Sub 'don't bother
'copy the table structure
Set db = CurrentDb
Set td = db.CreateTableDef(cTable)
For Each fdE In rs.Fields
Set fd = td.CreateField(fdE.Name, fdE.type, fdE.Size)
td.Fields.Append fd
Next
db.TableDefs.Append td
Set td = Nothing
'dump the recordset
Set rsT = db.OpenRecordset(cTable)
rs.MoveFirst
Do Until rs.EOF
rsT.AddNew
For Each fdE In rs.Fields
rsT(fdE.Name) = fdE.Value
Next
rsT.Update
rs.MoveNext
Loop
rsT.Close
Set rsT = Nothing
Set db = Nothing
End Sub
 
If I could view the recordset I would.

What I have done is opened a DAO recordset using a SQL statement which pulls
data from another application.

At the moment I just keep the recordset open until Access is closed.

This seems an inefficient way of doing it. What I would like to do is open
the recordset, and then save it to a table, so I can close the connection
the other application..
 
I am using the following code to get open the recordset...

sql = {Pre-prepared sql SELECT statement}
Set odbcConn = CreateObject("ADODB.Connection")
odbcConn.Open "Driver={SQL
Server};Server=SQLTEST01;Database=database1;UID=sa;PWD=password"
Set rsODBC = odbcConn.Execute(sql)

Does this make it more difficult?

Nick
 
hi,
use Bas Cost Budde code. it looks good.
-----Original Message-----
I am using the following code to get open the recordset...

sql = {Pre-prepared sql SELECT statement}
Set odbcConn = CreateObject("ADODB.Connection")
odbcConn.Open "Driver={SQL
Server};Server=SQLTEST01;Database=database1;UID=sa;PWD=pas sword"
Set rsODBC = odbcConn.Execute(sql)

Does this make it more difficult?

Nick




.
Click to expand...
 
Back
Top