"Thomas Scheiderich" <tfs@deltanet.com> wrote in message
news:4070F813.304@deltanet.com...
> Is there a way to set up a stored procedure to read from an ODBC access
> database directly into a new table in Sql Server 2000?
>
> Something like:
> ****************************************
> Select field1, field2 into new_table
> from old_table
> where something
> ******************************************
>
> Where old_table is an ODBC connection?
>
> Thanks,
>
> Tom.
>

Thomas Scheiderich wrote:

> P. Ward wrote:
>
>> Thomas
>>
>> To query any OLEDB Datasource adhocly you can use either the
>> OPENROWSET or
>> OPENDATASOURCE commands; NOTE there are a few caveats to these commands
>> which are listed in the articles below. If you are going to perform
>> this on
>> a regular basis you want to look at the adding the Access database as a
>> linked server.
>>
>> Accessing External Data:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_23xd.asp
>>
>>
>> OPENROWSET:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp
>>
>>
>> OPENDATASOURCE :
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_2be1.asp
>>
>>
>>
>> The example below will select everything from the orders table (in the
>> northwind.mdb) to the order temp table (#orders):
>>
>> SELECT a.* INTO #Orders
>> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
>> 'C:\Program Files\Microsoft
>> Office\OFFICE11\SAMPLES\northwind.mdb';'admin';'', Orders)
>> AS a
>> GO
>
>
>
> That looks like what I am looking for. I am going to play with that today.
>
> How do I set up a linked server and how does it work?

>
> Thanks,
>
> Tom.
>
>>
>>
>