By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Copy Contents of Recordset to a table

P: n/a
Hi,

I have a DB on a remote share that I want to update to a local DB
periodically. My plan is to create a VBA procedure and then trigger the
procedure a few times a day to the process is automated. My question is
how do I copy the contents of a Record set to a Table. This is what I
have so far:

Sub ImportDecap()

Dim Conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String

strDB = "\\RemoteSVR\RemoteDir\MyDB.mdb"

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
rst.Open "SELECT * FROM t_journal_upload_data", Conn
'What I have tried is
Conn.Execute _
"INSERT INTO Table_in_Local_DB VALUES " & rst.Fields(1)

But recieve an error so obviously I'm wrong. Has anybody any
suggestions of how this can be done?

Thanks

PC

May 29 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
pa*****@gmail.com wrote:
Hi,

I have a DB on a remote share that I want to update to a local DB
periodically. My plan is to create a VBA procedure and then trigger
the procedure a few times a day to the process is automated. My
question is how do I copy the contents of a Record set to a Table.
This is what I have so far:

Sub ImportDecap()

Dim Conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String

strDB = "\\RemoteSVR\RemoteDir\MyDB.mdb"

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
rst.Open "SELECT * FROM t_journal_upload_data", Conn
'What I have tried is
Conn.Execute _
"INSERT INTO Table_in_Local_DB VALUES " & rst.Fields(1)

But recieve an error so obviously I'm wrong. Has anybody any
suggestions of how this can be done?

Thanks

PC


This is not a great place to use a Recordset. Just link to the external table
and THEN an append query will work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 29 '06 #2

P: n/a
If I go this route what are the access rules for linked tables i.e.
will the linked table update each time the append query is run, does it
update immediately a change is made on in the DB that holds the linked
table or is there some other rule that applies?

Thanks in advance

May 29 '06 #3

P: n/a
I tried this and I'm hoping there is a quicker way to achive this. The
remote DB is on a remote share and connection speeds aren't create. one
of the uses of the DB on the remote share is to serve as a central
point for dynamic tables that are used in excel by variuos users in
various locations. Using Recordsets I pull approx 7000 rows of Data
from the remote db directly into an Excel table on the client. This
takes approx 45 second.

When i try to refresh the linked table in access this takes over a
minute so would a recordset not be better in this instance?

Thanks

Paul

May 29 '06 #4

P: n/a
pmc1 wrote:
I tried this and I'm hoping there is a quicker way to achive this. The
remote DB is on a remote share and connection speeds aren't create.
one of the uses of the DB on the remote share is to serve as a central
point for dynamic tables that are used in excel by variuos users in
various locations. Using Recordsets I pull approx 7000 rows of Data
from the remote db directly into an Excel table on the client. This
takes approx 45 second.

When i try to refresh the linked table in access this takes over a
minute so would a recordset not be better in this instance?

Thanks

Paul


Why refresh it? If you create it once you should be done (unless the location
changes).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 29 '06 #5

P: n/a
"pmc1" <pa*****@gmail.com> wrote in news:1148917674.126685.241810
@j73g2000cwa.googlegroups.com:
I tried this and I'm hoping there is a quicker way to achive this. The
remote DB is on a remote share and connection speeds aren't create. one
of the uses of the DB on the remote share is to serve as a central
point for dynamic tables that are used in excel by variuos users in
various locations. Using Recordsets I pull approx 7000 rows of Data
from the remote db directly into an Excel table on the client. This
takes approx 45 second.

When i try to refresh the linked table in access this takes over a
minute so would a recordset not be better in this instance?


Have you tried DoCmd.TransferSpreadsheet?

While this command is not fast its use would do away with intermediate
databases and intermediate recordsets. You should be able to just create an
instance of an access application, open the remote db with that (nothing
will show), do the Transfer (AcExport) and close the application instance.
TTBOMK this will create the worksheet in the existing Excel file, or write
over it if it (the worksheet not the file) already exists.

In many years of working with Access I can think of only two cases where
recordset manipulation may have been the best way; one was because I was
using someone else's bastard normalization scheme and the other, my own, I
ditched and retreated to user-defined SQL Server function; it was slower
but cleaner.

999 times out of a 1000
RecordSet = Inefficency

Of course, I may totally misunderstand what you are trying to do.

--
Lyle Fairfield
May 29 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.