RecordSet to Table?

From what I've seen online, I see examples of people looping through each row in t开发者_StackOverflowhe record set and placing an insert statement to copy data from a recordset to a table (in ms-access)

I am wondering if there was a simpler alternative? E.G some sort of RecordSet Export into table feature...

What I am trying to do is copy a table from an oracle database to a ms-access table, I am not filtering based off anything and want the entire data...

Thanks for the help!

An ADO recordset object includes a Save method which would allow you to persist the recordset as a file. But then you would have to import the file into your Access database.

Actually, I don't understand why you want to use a recordset to import your Oracle table into Access.

I would suggest creating a DSN for your Oracle instance, then using that DSN from the Access user interface. From the main menu in Access 2003, File->Get External Data->Import. Then select "ODCB Databases()" in the "Files of type" dropdown on the Import dialog. If your Access version is 2007 or 2010, look on the ribbon for a similar option.

If this is something you need to do repeatedly, consider linking to (rather than importing) the Oracle table. That way your Access database would always be using the current Oracle table data without any further intervention on your part.

If you want to accomplish this with VBA code instead of using the UI methods, look at DoCmd.TransferDatabase Method. That method will allow you to either link or import your Oracle table (look at AcDataTransferType on that MSDN page).

If you have trouble creating your Oracle DSN, see Connection strings for Oracle