Copy Contents of Recordset to a table

[paulc05]

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.Connectio n
Dim rst As New ADODB.Recordset
Dim strDB As String

strDB = "\\RemoteSVR\Re moteDir\MyDB.md b"

Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & strDB & ";"
rst.Open "SELECT * FROM t_journal_uploa d_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

[Rick Brandt]

pa*****@gmail.c om 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.Connectio n
Dim rst As New ADODB.Recordset
Dim strDB As String

strDB = "\\RemoteSVR\Re moteDir\MyDB.md b"

Conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & strDB & ";"
rst.Open "SELECT * FROM t_journal_uploa d_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

[pmc1]

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

[pmc1]

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

[Rick Brandt]

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

[Lyle Fairfield]

"pmc1" <pa*****@gmail. com> wrote in news:1148917674 .126685.241810
@j73g2000cwa.go oglegroups.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.TransferS preadsheet?

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