Friday, February 17, 2012

export SQLServer2000 DB to Access

is there a simple way to remotely (using a ColdFusion application that can
access the database) making a copy of an entire SQLServer2000 database into
Access?
I can make an Access DB with empty tables that match the SQLServer tables,
they only have to be filled.
Somebody suggested this
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\myaccessdb.mdb';;,mytable) select * from mytable
but it is possible that this only runs with SQLServer2005 ... because it
requires enabling the Ad Hoc Distributed Queries feature which was
introduced with SQLServer2005 ... or can I run statements like this also in
SQLServer2000 ?
-MichaelMichael
1)DTS doesnt work?
2)
INSERT INTO OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\myaccessdb.mdb"; User
ID=Admin;Password='
)...Table(col1)
SELECT col1 FROM Table
"Michael Peters" <mp@.michaelpeters.de> wrote in message
news:dteomt$8eu$01$1@.news.t-online.com...
> is there a simple way to remotely (using a ColdFusion application that can
> access the database) making a copy of an entire SQLServer2000 database
> into Access?
> I can make an Access DB with empty tables that match the SQLServer tables,
> they only have to be filled.
> Somebody suggested this
> insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\myaccessdb.mdb';;,mytable) select * from mytable
> but it is possible that this only runs with SQLServer2005 ... because it
> requires enabling the Ad Hoc Distributed Queries feature which was
> introduced with SQLServer2005 ... or can I run statements like this also
> in SQLServer2000 ?
> -Michael
>
>|||What about using the DTS wizard for this ?
HTH, Jens Suessmeyer.|||How many tables in the DB? About how many total records?|||> DTS doesnt work?
no, DTS is not available as the server is remote and not under my control. I
have to do it programmatically, as I wrote,

> How many tables in the DB?
several dozen

> About how many total records?
thousands
-Michael|||> INSERT INTO OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\myaccessdb.mdb"; User
> ID=Admin;Password='
> )...Table(col1)
> SELECT col1 FROM Table
"Der Ad-hoc-Zugriff auf den OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' wurde
verweigert. Sie mssen auf diesen Provider ber einen Verbindungsserver
zugreifen."
-Michael

No comments:

Post a Comment