Table image

[FrancisC]

I am retrieving data from an Oracle database using ADO and want to make a mirror of a view locally, in an Access database.

Currently, I an using two sperate connections and two seperate recordset.

First I retrieve all the data from Oracle into an ADO recordset, then, one by one, I insert into the local table. this process is pretty long and I am looking for alternatives to speed things up.

Are there any techniques that would allow me to do that ?

[JHausmann]

I'd create a link in your Access database to the Oracle table you copy.

Then I'd:

1) delete the table native to Access
2) select * into Access_table from Oracle_table

[FrancisC]

Thanks a lot...

I see what you mean but I have no idea of the syntax, or the actual objects to use... Would you mind guiding me a little further ?

[JHausmann]

The previous post was pretty close.

I make the following assumptions.

1) the native access table is called a_table
2) the oracle table, that you've manually linked into your access database, is called o_table

to delete the access table in VB, issue the following SQL:

DROP a_table

to copy the Oracle table to an Access table, issue the following SQL:

select * into a_table from o_table

[FrancisC]

I might be more of a beginner than you think I am... I am still missing a piece of the puzzle.

I think it lies in what you mean by: "2) the oracle table, that you've manually linked into your access database, is called o_table"

I am really not sure what you mean by that...

Here is what I do:

' This is my connection to the Oracle database
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset

db.Open "Provider=MSDASQL;dsn=development;uid=dispersion;pwd=development;"
strSQL = "select employee_id, last_name, first_name from v_dispersion_valid_operators"
rs.Open strSQL, db, adOpenStatic, adLockOptimistic

' Now I connect to the local database
Set dbLoc = New ADODB.Connection
Set rsLoc = New ADODB.Recordset

dbLoc.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB Projects\CD400\CD400.mdb;"
rsLoc.Open "EMPLOYEE_IDS", dbLoc, adOpenStatic, adLockOptimistic, adCmdTable

' After that is where I can insert the records one by one but it takes for ever...

Am I at least on the right track ? When you tell me to issue the SQL: "select * into a_table from o_table", where do I do that ? It seems that I could do that to create the image of a table from Access into Access; I still don't see how to do that from one to the other...

Again, thanks a lot for all the help...