INSERT INTO table FROM Persisted Recordset?

[Guest]

I have a persisted recordset, created in VBA with ADO using
rst.save strFileName adPersistADTG

I would like to be able to INSERT INTO (i.e. append) an Access table
strTableName. I don't know if it's possible, or if I'm just doing this wrong.

From the Access VBE my code for the INSERT INTO is:

strPathFileName = c:\work\Thefile.rst

strSQL_FROM = "SELECT * FROM [" & strPathFileName & "]"

strSQL = ""
strSQL = strSQL & "INSERT INTO " & strTableName & " "
strSQL = strSQL & strSQL_FROM


With connTo
.Execute strSQL
.Close
End With


If I have
set connTo = CurrentProject.connection

I get "-2147217865: The Microsoft Jet database engine cannot find the input
table or query 'c:\work\Thefile.rst'. Make sure it exists and that its name
is spelled correctly."


I can retrieve it fine - back into a recordset using
With connFrom
.Provider = "MSPERSIST"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.Open strPathFileName, connFrom, , , adCmdFile
End With

The rst is then ready for business - but I can't use it directly in the
INSERT INTO statement.

I know that I can take the recordset and feed, row by row, into a new
recordset opened from the destination table, but I'd like to do this as a
Batchupdate with INSERT INTO if at all possible.

I haven't been able to find any examples of how to do this.

Why am I using a persisted recordset, anyway?

I would like to see if it is any quicker than using the rst.updatebatch for
inserting data from the web (with 500,000 + rows) directly into the
destination table. I'm currently doing that using

rst_INTO.Filter = adFilterPendingRecords
rst_INTO.UpdateBatch

at intervals of 100 records. I don't want to leave it all to the end to
append - because I don't want to risk getting the data, and then it crashing
with a huge append. I suspect that as the table gets larger (from the
..updateBatch) it gets less efficient, because of the existing (and
continuously updating) indexes.

It may be that creating a persisted recordset and then trying to INSERT it
INTO the Access table is just as slow, but I'd sure like to try (or have it
confirmed for me).

 

['69 Camaro]

Hi.

I don't know if it's possible, or if I'm just doing this wrong.

The FROM clause requires a data source that Jet can read from (such as an
MDB table), or that an ISAM or ODBC driver can read from. A Recordset
doesn't qualify as a valid data source.

I haven't been able to find any examples of how to do this.

There aren't any. Store the data in a table or tab-delimited or CSV text
file and read the data from that for the record append. It will be much
faster than trying to manipulate a Recordset.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I have a persisted recordset, created in VBA with ADO using
rst.save strFileName adPersistADTG

I would like to be able to INSERT INTO (i.e. append) an Access table
strTableName. I don't know if it's possible, or if I'm just doing this
wrong.

From the Access VBE my code for the INSERT INTO is:

strPathFileName = c:\work\Thefile.rst

strSQL_FROM = "SELECT * FROM [" & strPathFileName & "]"

strSQL = ""
strSQL = strSQL & "INSERT INTO " & strTableName & " "
strSQL = strSQL & strSQL_FROM


With connTo
.Execute strSQL
.Close
End With


If I have
set connTo = CurrentProject.connection

I get "-2147217865: The Microsoft Jet database engine cannot find the
input
table or query 'c:\work\Thefile.rst'. Make sure it exists and that its
name
is spelled correctly."


I can retrieve it fine - back into a recordset using
With connFrom
.Provider = "MSPERSIST"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.Open strPathFileName, connFrom, , , adCmdFile
End With

The rst is then ready for business - but I can't use it directly in the
INSERT INTO statement.

I know that I can take the recordset and feed, row by row, into a new
recordset opened from the destination table, but I'd like to do this as a
Batchupdate with INSERT INTO if at all possible.

I haven't been able to find any examples of how to do this.

Why am I using a persisted recordset, anyway?

I would like to see if it is any quicker than using the rst.updatebatch
for
inserting data from the web (with 500,000 + rows) directly into the
destination table. I'm currently doing that using

rst_INTO.Filter = adFilterPendingRecords
rst_INTO.UpdateBatch

at intervals of 100 records. I don't want to leave it all to the end to
append - because I don't want to risk getting the data, and then it
crashing
with a huge append. I suspect that as the table gets larger (from the
.updateBatch) it gets less efficient, because of the existing (and
continuously updating) indexes.

It may be that creating a persisted recordset and then trying to INSERT it
INTO the Access table is just as slow, but I'd sure like to try (or have
it
confirmed for me).

 

[Guest]

thanks - but I think the persisted recordset is a valid data source. It can
be read with the driver=mspersist using ado from any application (and can be
brought back into Access). Its getting the web data into the table in the
first place that takes so long, so I am trying to find other ways of doing it
to speed it up.

The problem, to me, appears to be that you need to different connections
operating at the same time - one for the Access table recordset (Conn =
CurrentProject.Connection) and one for the persisted recordset in the ADTG
(Advanced Data TableGram) format (conn2.provider = strFileName, adPersistADTG)

Perhaps it would help if someone could give me the conn settings and SQL for
INSERT INTO an Access table from another external file source (e.g. another
ODBC source). Maybe I can back what I'm trying to do into that. Thanks.

Hi.

I don't know if it's possible, or if I'm just doing this wrong.

The FROM clause requires a data source that Jet can read from (such as an
MDB table), or that an ISAM or ODBC driver can read from. A Recordset
doesn't qualify as a valid data source.

I haven't been able to find any examples of how to do this.

There aren't any. Store the data in a table or tab-delimited or CSV text
file and read the data from that for the record append. It will be much
faster than trying to manipulate a Recordset.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I have a persisted recordset, created in VBA with ADO using
rst.save strFileName adPersistADTG

I would like to be able to INSERT INTO (i.e. append) an Access table
strTableName. I don't know if it's possible, or if I'm just doing this
wrong.

From the Access VBE my code for the INSERT INTO is:

strPathFileName = c:\work\Thefile.rst

strSQL_FROM = "SELECT * FROM [" & strPathFileName & "]"

strSQL = ""
strSQL = strSQL & "INSERT INTO " & strTableName & " "
strSQL = strSQL & strSQL_FROM


With connTo
.Execute strSQL
.Close
End With


If I have
set connTo = CurrentProject.connection

I get "-2147217865: The Microsoft Jet database engine cannot find the
input
table or query 'c:\work\Thefile.rst'. Make sure it exists and that its
name
is spelled correctly."


I can retrieve it fine - back into a recordset using
With connFrom
.Provider = "MSPERSIST"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.Open strPathFileName, connFrom, , , adCmdFile
End With

The rst is then ready for business - but I can't use it directly in the
INSERT INTO statement.

I know that I can take the recordset and feed, row by row, into a new
recordset opened from the destination table, but I'd like to do this as a
Batchupdate with INSERT INTO if at all possible.

I haven't been able to find any examples of how to do this.

Why am I using a persisted recordset, anyway?

I would like to see if it is any quicker than using the rst.updatebatch
for
inserting data from the web (with 500,000 + rows) directly into the
destination table. I'm currently doing that using

rst_INTO.Filter = adFilterPendingRecords
rst_INTO.UpdateBatch

at intervals of 100 records. I don't want to leave it all to the end to
append - because I don't want to risk getting the data, and then it
crashing
with a huge append. I suspect that as the table gets larger (from the
.updateBatch) it gets less efficient, because of the existing (and
continuously updating) indexes.

It may be that creating a persisted recordset and then trying to INSERT it
INTO the Access table is just as slow, but I'd sure like to try (or have
it
confirmed for me).

 

['69 Camaro]

Hi.

but I think the persisted recordset is a valid data source.

A persisted Recordset is not a valid data source for the FROM clause of a
SQL statement in any dialect of SQL, not just Jet SQL.

Perhaps it would help if someone could give me the conn settings and SQL
for
INSERT INTO an Access table from another external file source (e.g.
another
ODBC source).

It won't help, but you need to prove this to yourself, so please see the
following Web page for many examples of connection strings to Access
databases:

http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

And here's an example of the SQL syntax for inserting records into a Jet
table from an external database file:

INSERT INTO tblDailyOrders
SELECT *
FROM [;DATABASE=C:\Data.mdb;].tblOrders;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

thanks - but I think the persisted recordset is a valid data source. It
can
be read with the driver=mspersist using ado from any application (and can
be
brought back into Access). Its getting the web data into the table in
the
first place that takes so long, so I am trying to find other ways of doing
it
to speed it up.

The problem, to me, appears to be that you need to different connections
operating at the same time - one for the Access table recordset (Conn =
CurrentProject.Connection) and one for the persisted recordset in the ADTG
(Advanced Data TableGram) format (conn2.provider = strFileName,
adPersistADTG)

Perhaps it would help if someone could give me the conn settings and SQL
for
INSERT INTO an Access table from another external file source (e.g.
another
ODBC source). Maybe I can back what I'm trying to do into that. Thanks.

Hi.

I don't know if it's possible, or if I'm just doing this wrong.

The FROM clause requires a data source that Jet can read from (such as an
MDB table), or that an ISAM or ODBC driver can read from. A Recordset
doesn't qualify as a valid data source.

I haven't been able to find any examples of how to do this.

There aren't any. Store the data in a table or tab-delimited or CSV text
file and read the data from that for the record append. It will be much
faster than trying to manipulate a Recordset.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

I have a persisted recordset, created in VBA with ADO using
rst.save strFileName adPersistADTG

I would like to be able to INSERT INTO (i.e. append) an Access table
strTableName. I don't know if it's possible, or if I'm just doing this
wrong.

From the Access VBE my code for the INSERT INTO is:

strPathFileName = c:\work\Thefile.rst

strSQL_FROM = "SELECT * FROM [" & strPathFileName & "]"

strSQL = ""
strSQL = strSQL & "INSERT INTO " & strTableName & " "
strSQL = strSQL & strSQL_FROM


With connTo
.Execute strSQL
.Close
End With


If I have
set connTo = CurrentProject.connection

I get "-2147217865: The Microsoft Jet database engine cannot find the
input
table or query 'c:\work\Thefile.rst'. Make sure it exists and that its
name
is spelled correctly."


I can retrieve it fine - back into a recordset using
With connFrom
.Provider = "MSPERSIST"
.Open
End With

Set rst = New ADODB.Recordset
With rst
.Open strPathFileName, connFrom, , , adCmdFile
End With

The rst is then ready for business - but I can't use it directly in the
INSERT INTO statement.

I know that I can take the recordset and feed, row by row, into a new
recordset opened from the destination table, but I'd like to do this as
a
Batchupdate with INSERT INTO if at all possible.

I haven't been able to find any examples of how to do this.

Why am I using a persisted recordset, anyway?

I would like to see if it is any quicker than using the rst.updatebatch
for
inserting data from the web (with 500,000 + rows) directly into the
destination table. I'm currently doing that using

rst_INTO.Filter = adFilterPendingRecords
rst_INTO.UpdateBatch

at intervals of 100 records. I don't want to leave it all to the end
to
append - because I don't want to risk getting the data, and then it
crashing
with a huge append. I suspect that as the table gets larger (from the
.updateBatch) it gets less efficient, because of the existing (and
continuously updating) indexes.

It may be that creating a persisted recordset and then trying to INSERT
it
INTO the Access table is just as slow, but I'd sure like to try (or
have
it
confirmed for me).