Experts in Recordsets , Plz Help

[bomayed]

How do I select an entire table and insert it in a new table which exists in a different database?


I'm trying to export a table from ACCESS file to Oracle Table using VB recordset but I don't want to loop in a recordset , I just want to INSERT the entire table

Is thie possible ?

[Pasvorto]

I don't know about Oracle, but I use this code to archive records from one database to another (SQL Server 2000).

sql = "INSERT INTO ARCHIVE.DBO.INVENTORYHISTORYARCHIVE SELECT * FROM INVENTORYHISTORY WHERE [XACTDTE] <= " & "'" & glbDate & "'"
CnxnTechSQL.Execute sql

of course, you have to be connected to both databases before this can be executed.

[bomayed]

sql = "INSERT INTO ARCHIVE.DBO.INVENTORYHISTORYARCHIVE SELECT * FROM INVENTORYHISTORY WHERE [XACTDTE] <= " & "'" & glbDate & "'"
CnxnTechSQL.Execute sql

Can you please explain what is [XACTDTE] means and what is glbDate means?

[GaryMazzone]

Can't you open Access and attach the Oracle tables (don't import just attach) and create a query in Access that does the insert (if this is a one time only action)?

[kfcSmitty]

XACTDTE would be a field in his database, and glbDate would be a control that contains the date he is using as the condition of the insert.

[bomayed]

GaryMazzone,Can you please explain it more to me

I opned the oracle database using ODBC connection ( I didn't import the table)

Now what should I do ?

[GaryMazzone]

Are you in Access or VB?

[GaryMazzone]

The attached images are attaching an Oracle set of tables to Access. These are based on a Datasource build in the system.

[bomayed]

I'm in Access right now

[GaryMazzone]

Select table tables to link. Then create the Query in Access as an append query from the Access table to the Oracle table.

[bomayed]

Can you give me an example of the query ?

1- I opened an oracle table using (ODBC Connection )
2- I Imported the Access table to the current Access file.
Now I have the two tables in the tables section

What should I do next , go to quries and do what exactlly? use wizard ?

I need help please be patient with me =(

[Al42]

What should I do next , go to quries and do what exactlly? use wizard ?

Yes. Select New, Design View, select the Access table, Close.

Select Query Type (the icon with the 2 tables), Append Query.

Select the Oracle table.

Drag the fields from the Access table to the Field row (1 to a box) and select the Oracle field in Append To.

When you have all the fields you want to insert, click on the ! (run the query).

(I think I got all the steps.)

[bomayed]

Wow , Amazing , Thanks Al42 and thank you GaryMazzone

I tried to copy the data from the access table and paste it to the oracle table in ACCESS but it took forever . . days to copy half the data. But with the append and query stuff It took 15 minutes! I wonder what's the difference between copy and paste and append . . . I mean why is it faster?