Importing large amount of data from a remote database into access

[SkyHigh]

Hi Folks

I have to import large amount of data from remote database into local access tables using ADO and DAO via ODBC - the data involves about 2 million rows in one table, if the data is not that large it goes smoothly otherwise it results in out of memory error, is there a more effecient way of doing this, here is the code I am using :

---

---

Dim Rs As New ADODB.Recordset
Dim sqlBillData As String
    
            
            sqlBillData = "SELECT field1, field2, ...fieldn FROM remote_table"
                            
        Set Rs = cPostgreSQL.Execute(sqlBillData, , adOpenForwardOnly)
            
            Do Until Rs.EOF
                oConn.Execute "INSERT INTO local_table ([field1],[field2], ...[fieldn] " & _
                                    "VALUES(" & Rs![field1] & ",'" & Rs![field2] & "'," & ... Rs!fieldn & ",'" & ")"
                Rs.MoveNext
            Loop
            
        Set Rs = Nothing

---[Code]---

 

[mik18]

Try setting up a linked table(s) to the remote database and using a make table query. After you initially make the table you can just use an append query that captures only unique records to update the local table(s).

mike

 

[Elysium]

Just out of curiousity, why do you have to use both ADO and DAO?

Randy

 

[SkyHigh]

ADO to fetch dataset from remote_table and DAO to coputhe dataset into local access table.

Please let me know of using ado also for the local table would help ?

Thanks

 

[Elysium]

You can use ADO to add data from a recordset into a local table. I was a long time DAO user that just switched to ADO about 2 months ago. My opinion? I prefer ADO because it seems to make more sense.

There are a few options to add the data to a local table. You can iterate through each record and then, using AddNew combined with Update, add the records. Or, you can build an Insert SQL statement and utilize the Connection object's execute method to add the records.

Personally, I would not combine ADO and DAO. But, that's just my opinion.

Regards,


Randy

 

[SkyHigh]

The problem I am facing is a large set of records being buffered in the memory fetched by ADO and I believe it remains in the memory until the last record has been copied and the recordset is set to nothing ?

Can someone please give me some good ideas how to overcome this problem, some code will be even more helpful.

Thanks a bunch.

 

[PHV]

Isn't the linked table an option ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244