Re: load data from Access to SQL

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 09:00:36 -0400

It's probably closer to:

INSERT INTO SQLtable
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
              'Data Source="YourAccessDB.mdb";User
ID=Admin;Password=;)...AccessTable

Note the extra dots before AccessTable. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Al Reid" <areidjr@reidDASHhome.com> wrote in message
news:%23BxApg1jEHA.3056@TK2MSFTNGP10.phx.gbl...
"Andre" <no@spam.com> wrote in message
news:uBq392wjEHA.2908@TK2MSFTNGP10.phx.gbl...
> Al,
>
> If I can speak in simplistic terms, what I'd like to do is the following:
>
> insert SQLtable
> select * from AccessTable
>
> Is something like this possible without having to loop through every
record
> in my Access table?  I'm at the point where I have the Access table in a
> recordset, but am unsure how do the Insert, without having to loop through
> every record.
>
> Thanks, Andre
>
>
Andre,
>From what you have provided, I don't know why DTS will not work for you,
but...
This MAY work for you.  I have not tried or tested it.
INSERT INTO SQLtable
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
              'Data Source="YourAccessDB.mdb";User
ID=Admin;Password=;).AccessTable
For additional info look at the MSSQL BOL.
I hope this helps.
-- 
Al Reid
How will I know when I get there...
If I don't know where I'm going?

Relevant Pages

  • Re: ADO Recordset Update & SQL Server 2005 ??
    ... I've tried explicit .Update inside the loop and outside the loop as a batch ... There's no 500 error (asp and iis 6) posted on the page. ... This error shows up ONLY when connected to a sql server 2005 server. ... the same database schema/data in sql server 2005, ...
    (microsoft.public.data.ado)
  • Re: DTS Workflow Question
    ... >I am using SQL Server 2000 and I am writing a DTS package that loops ... >Script handling the loop routine. ... You could duplicate the ActiveX Script to restart the loop, ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Vlookup nightmare
    ... >excel can _NOT_ do loops like SQL can. ... >SQL Server can loop through using a cursor. ... Procedural code can loop through a cursor into a dbms table. ... But for those not so few who have MSFT Office ...
    (microsoft.public.excel)
  • Re: Ideas on running a large UPDATE causing Locks...
    ... >Yep, it's a loop. ... >Columnist, SQL Server Professional ... >>SQL Server MVP ... >>Toronto, ON Canada ...
    (microsoft.public.sqlserver.server)
  • Re: Ideas on running a large UPDATE causing Locks...
    ... Columnist, SQL Server Professional ... www.pinnaclepublishing.com/sql "Roz" wrote in message ... I imagine I'd do this in a loop? ... you are not blocking too many>users at a time. ...
    (microsoft.public.sqlserver.server)