fastest way to copy result set to access table 1

[BitZero]

What is the fastest way to copy the data from a result set to an Access table? Is there a way to copy in bulk, rather than looping thru and doing an insert for each record?

 

[TheAceMan1]

How are ya BitZero . . .

Fastest I know of is an [blue]Append[/blue] query. However I have no idea what you mean by [purple]result set[/purple]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[BitZero]

Hi Ace Man

Sorry - I meant Record Set. I'm going out to an Oracle database and loading data into an ADODB Recordset. Then I want to copy this data into an Access table.

Could you give an example of an append query. I'm not familiar with that.

Thanks

 

[TheAceMan1]

BitZero . . .

The ADO recordset won't work with query. Is it possible to query the source of the data your loading into the recordset?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[BitZero]

Ace Man

The only way I know to query the data from an Oracle database is to setup an ODBC connection, and then create a linked table. I don't really like doing that because the ODBC setup must be done on whatever PC the access database runs on, and the ODBC is outside of the Access database.

Do you know of any other way to query an Oracle table?

Thanks

 

[TheAceMan1]

[blue] ... any other way to query an Oracle table[/blue]

I've exhausted searches in my library and it doesn't appear so.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[BitZero]

Ace Man

Thanks for taking time to research this.

It seems like it would be a common thing to copy data from an Oracle or SQL Server table to/from an Access table, and that there should be a quick and easy way to do this within Access.

Thanks again

 

[dhookom]

I would think you could create a pass-through query to Oracle without creating a DSN. I do this with SQL Server on a regular basis. A typical ODBC Connect Str property for SQL Server would be:

  ODBC;Driver={SQL Server};Server=MyServerName;Database=MyDB;Uid=MyUser;Pwd=MyPwd

You can then append using the p-t as the source.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

There is a great page Oracle Connection Strings with several different options that require no configuration on the workstation.

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

Duane

Thanks - this looks very promising. I didn't know there was such a thing as a pass-through query. I'm exploring the connect strings for an Oracle connection now.