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

Insert an ADO recordset in an Acess 2002 table

P: n/a
Hello everybody,

I'm working with Access 2002. I have to import Data from a Foxpro table that
contains 25000 records in an Access table.

I have a couple of restrictions placed on me for the solution:
1. I am not allowed to use ODBC
2. I have to use ADO
3. I am not allowed to use Linked tables

I make the following code to insert the datas from FoxPro to an Access table
:

Sub ConnectionFoxPro2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

cnn.Open "Provider=vfpoledb;" & _
"Data Source=C:\WinBIZ Data\DAT\D5\2004\ADRESSES.DBF;"

rst2.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Open "adresses", cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
Do Until rst.EOF

'Debug.Print rst.Fields(2).Value, rst.Fields(3).Value
rst2.AddNew
rst2.Fields(0).Value = rst.Fields(1).Value
rst2.Fields(1).Value = rst.Fields(3).Value
rst2.Update

rst.MoveNext

Loop

rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub

The code works, but it insert one by one the record in the Access Table.
Is it possible to block-copy the ado recordset in an Access table ?

Thank you in advance for your help

Have a nice day
Fabrice
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Wed, 4 May 2005 09:04:38 +0200, "Fabrice" <ze***@hotmail.com>
wrote:

One by one is all you will get when working with recordsets.
That's what you get for tying your hands on your back not allowing
linked tables etc. I would try it with linked tables anyway, and show
whoever put these restrictions on it what can be achieved if that
restriction is waved.

-Tom.
Hello everybody,

I'm working with Access 2002. I have to import Data from a Foxpro table that
contains 25000 records in an Access table.

I have a couple of restrictions placed on me for the solution:
1. I am not allowed to use ODBC
2. I have to use ADO
3. I am not allowed to use Linked tables

I make the following code to insert the datas from FoxPro to an Access table
:

Sub ConnectionFoxPro2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

cnn.Open "Provider=vfpoledb;" & _
"Data Source=C:\WinBIZ Data\DAT\D5\2004\ADRESSES.DBF;"

rst2.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Open "adresses", cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
Do Until rst.EOF

'Debug.Print rst.Fields(2).Value, rst.Fields(3).Value
rst2.AddNew
rst2.Fields(0).Value = rst.Fields(1).Value
rst2.Fields(1).Value = rst.Fields(3).Value
rst2.Update

rst.MoveNext

Loop

rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub

The code works, but it insert one by one the record in the Access Table.
Is it possible to block-copy the ado recordset in an Access table ?

Thank you in advance for your help

Have a nice day
Fabrice


Nov 13 '05 #2

P: n/a
Fabrice:

ADO supports batch updating through the UpdateBatch method. You will need
to use the adLockBatchOptimistic parameter when you open your recordset.
For example:

rst.Open "adresses", cnn, adOpenKeyset, adLockBatchOptimistic

Do Until rst.EOF

'Debug.Print rst.Fields(2).Value, rst.Fields(3).Value
rst2.AddNew
rst2.Fields(0).Value = rst.Fields(1).Value
rst2.Fields(1).Value = rst.Fields(3).Value
rst2.Update

rst.MoveNext

Loop
rs.UpdateBatch

David Lloyd
MCSD .NET
http://LemingtonConsulting.com
"Fabrice" <ze***@hotmail.com> wrote in message
news:42********@news.bluewin.ch... Hello everybody,

I'm working with Access 2002. I have to import Data from a Foxpro table that contains 25000 records in an Access table.

I have a couple of restrictions placed on me for the solution:
1. I am not allowed to use ODBC
2. I have to use ADO
3. I am not allowed to use Linked tables

I make the following code to insert the datas from FoxPro to an Access table :

Sub ConnectionFoxPro2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

cnn.Open "Provider=vfpoledb;" & _
"Data Source=C:\WinBIZ Data\DAT\D5\2004\ADRESSES.DBF;"

rst2.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Open "adresses", cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
Do Until rst.EOF

'Debug.Print rst.Fields(2).Value, rst.Fields(3).Value
rst2.AddNew
rst2.Fields(0).Value = rst.Fields(1).Value
rst2.Fields(1).Value = rst.Fields(3).Value
rst2.Update

rst.MoveNext

Loop

rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub

The code works, but it insert one by one the record in the Access Table.
Is it possible to block-copy the ado recordset in an Access table ?

Thank you in advance for your help

Have a nice day
Fabrice

Nov 13 '05 #3

P: n/a
are you trying to use ADO OLE as opposed to ADO ODBC? is this to avoid
having a DSN on your machine?

if this is a one off data import then the restrictions are ridiculous.
just link the tables and create the import queries or query code for
this instance.

Nov 13 '05 #4

P: n/a
Tom van Stiphout wrote:
On Wed, 4 May 2005 09:04:38 +0200, "Fabrice" <ze***@hotmail.com>
wrote:

One by one is all you will get when working with recordsets.


Perhaps this is less one to one than the code pasted originally; it adds
5 records; I find it very powerful.

Sub AirCode()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open "SELECT ID, TextValue, IDS, hjkh, NewColumn FROM Table1
WHERE False"
.AddNew Array(0, 1, 2, 3, 4), Array(11, "b", 12, 3, 6.3)
.AddNew Array(0, 1, 2, 3, 4), Array(7, "fred", 20, 9, 7.1)
.AddNew Array(0, 1, 2, 3, 4), Array(19, "joe", 1, 1, 5.2)
.AddNew Array(0, 1, 2, 3, 4), Array(9, "molly", 36, 36, 0.369)
.AddNew Array(0, 1, 2, 3, 4), Array(10, "a", 0, 1#, 0.00003)
.UpdateBatch
End With
End Sub
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet
Nov 13 '05 #5

P: n/a
Hello everybody,

First, i want to thank you all for your help.

As you say, I create a DSN on my machine with Microsoft FoxPro VFP Driver
(*.dbf). Then I link the foxpro tables in Access. It works, but it is very
slow. For that reason, I think to use the code :

cnn.Open "Provider=vfpoledb;" & _
"Data Source=C:\WinBIZ Data\DAT\D5\2004\ADRESSES.DBF;"

rst2.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Open "adresses", cnn, adOpenKeyset, adLockOptimistic

Is it OK what I'm doing, or if it exists a better solution.

Thank you in advance for your help
Fabrice
"Fabrice" <ze***@hotmail.com> a écrit dans le message de
news:42********@news.bluewin.ch...
Hello everybody,

I'm working with Access 2002. I have to import Data from a Foxpro table that contains 25000 records in an Access table.

I have a couple of restrictions placed on me for the solution:
1. I am not allowed to use ODBC
2. I have to use ADO
3. I am not allowed to use Linked tables

I make the following code to insert the datas from FoxPro to an Access table :

Sub ConnectionFoxPro2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

cnn.Open "Provider=vfpoledb;" & _
"Data Source=C:\WinBIZ Data\DAT\D5\2004\ADRESSES.DBF;"

rst2.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Open "adresses", cnn, adOpenKeyset, adLockOptimistic

rst.MoveFirst
Do Until rst.EOF

'Debug.Print rst.Fields(2).Value, rst.Fields(3).Value
rst2.AddNew
rst2.Fields(0).Value = rst.Fields(1).Value
rst2.Fields(1).Value = rst.Fields(3).Value
rst2.Update

rst.MoveNext

Loop

rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub

The code works, but it insert one by one the record in the Access Table.
Is it possible to block-copy the ado recordset in an Access table ?

Thank you in advance for your help

Have a nice day
Fabrice

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.