Recordset to Table

[Guest]

I have created a recordset (rstSQL) in VBA. Later in the program, I want to
put this recordset into a table (tblResult). I would like to know if there
is a more efficient way to do this than the "brute force" method:

Set rstTable = db.OpenRecordset("tblResult")
Do While rstSQL.EOF = false
rstTable.AddNew
rstTable("Field1") = rstSQL("Filed1")
.... (repeat for all fields) ...
rstTable.Update
rstSQL.MoveNext
Loop

I would like to do something more elegant, like this:
newSQL = "Select * INTO tblResult from rstSQL"
DoCmd.RunSQL newSQL
However, mixing objects in an SQL string doesn't work.

Any ideas? Or is my initial looping code as good as it gets?

Thanks,
George

 

[Guest]

newSQL = "INSERT * INTO tblResult SELECT * FROM rstSQL;"
CurrentDb.Execute newSql

 

[Guest]

Looks good - but the code does not work (error 3078). Microsoft Jet could
not find the recordset rstSQL. I think because rstSQL is a recordset, and
not the actual name of a query, it cannot be incorporated into the SQL
statement.

Any other ideas? Anyone?

 

[Guest]

Replace rstSQL with the name of the query.

Looks good - but the code does not work (error 3078). Microsoft Jet could
not find the recordset rstSQL. I think because rstSQL is a recordset, and
not the actual name of a query, it cannot be incorporated into the SQL
statement.

Any other ideas? Anyone?

 

[Guest]

I would if I could, but....
The recordset rstSQL doesn't have a name. It is a pass-through query that
is defined and created within VBA. This is the essence of my question - how
do I combine a make-table query with a VBA-defined recordset?

Thanks

 

[Guest]

I am a little confused here. If rstSQL has been created and is open, then
the original code I sent should work (I think). Not having used pass through
queries, I am perhaps underqualified to help with this.

I will, however continue to try. Maybe creating a query that is not a pass
through that returns the data you need would do the trick.

 

[Tim Ferguson]

The recordset rstSQL doesn't have a name. It is a pass-through query
that is defined and created within VBA.

In that case, just recast that SQL command into an INSERT.

By the way, it is really unsafe to use INSERT without a field list. Better
to do

INSERT INTO MyTable(One, Two, Three)
SELECT (Hay, Bee, Sea) FROM OtherTable WHERE etc

so that you know which column gets put into which field.

Hope that helps


Tim F

 

[Dirk Goldgar]

newSQL = "INSERT * INTO tblResult SELECT * FROM rstSQL;"
CurrentDb.Execute newSql

No, that won't work. The query engine doesn't recognize a recordset as
a table from which records can be selected. George's original solution
of looping through the source recordset, adding each record to the
target recordset is the only real solution. The code could be made
somewhat more concise, if the fields in the table mirror those in the
source recordset, by looping through the fields in both recordsets, like
this:

'----- start of example code -----
Dim fld As DAO.Field

' ... other objects have been declared and initialized ...

Set rstTable = db.OpenRecordset("tblResult")

Do Until rstSQL.EOF

rstTable.AddNew

For Each fld In rstSQL.Fields
' skip autonumber fields in target table
If (rstTable(fld.Name).Attributes And dbAutoIncrField) = 0
Then
rstTable(fld.Name) = fld.Value
End If
Next fld

rstTable.Update

rstSQL.MoveNext

Loop
'----- end of example code -----

As you see, the above code skips autonumber fields in the target table.
If it's necessary to have an autonumber field, but you want to load that
field, too, from the recordset. you'll have to build and execute INSERT
queries to add each record, instead of using Recordset.Addnew.

Also, this code assumes that every field in the source recordset exists
in the target table. If that's not the case, it would be possible to
modfy it to skip unmatched fields.

 

[Guest]

Dirk,
Thanks for confirming my suspicions. I appreciate your insights too.

Thanks!

 

[Dirk Goldgar]

Dirk,
Thanks for confirming my suspicions. I appreciate your insights too.

Thanks!

You're welcome. Tim Ferguson's suggestion about transforming your
original, code-built SQL into an append query, is an even better
solution, if your circumstances permit it.

 

[Rob Wills]

Hi,

This is exactly the issue that I have.

I wish to connect to a Sybase database using a recordset - to remove the
need of having to link the table through ODBC using DSN. (to prevent the
users from fiddling with the source data)

I can create the recordset programatically - but then I want to save to a
local table without writing the code to loop through each field in each
record to append the data within Access.

I've recently read about saving the recordset to disk - might the following
be a better soloution?
1, Open the ADO recordset
2, Save the ADO Recordset to disk
3, Close the recordset
4, Import into Access using a schema

Any other ideas are welcome
(Preferably one that means I can just run a single SQL string)

Thanks
Rob