Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
--
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx
.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
Hari
If you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

External scripts / imports / updates

Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.

As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.

Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.

FYI, they are running SQL 2000 on both XP Pro and W2K3 server.

Thanks
StarbYou can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.|||You can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.
Thanks. Dont want to use a third party tool (OSQL etc) and can't use such as DTS and Exec as it is the 'End User' that will use the funtion. It must be run via a simple script from the Mac app.

Cheers
Starbsql

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
LijunHave a look at oSql in BooksOnLine.
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
Lijun
Have a look at oSql in BooksOnLine.
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>

Extended Procedure

Does anyone know any undocumented extended procedures that can red SQL
scripts from a text file and execute it. The SQL scripts contain multiple
batches (contain key word 'GO', which will not be recognized by the regular
EXECUTE statement).
Thanks,
LijunHave a look at oSql in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:OHF8MBgYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Does anyone know any undocumented extended procedures that can red SQL
> scripts from a text file and execute it. The SQL scripts contain multiple
> batches (contain key word 'GO', which will not be recognized by the
> regular
> EXECUTE statement).
> Thanks,
> Lijun
>