Microsoft SQL Server forum - export SQLServer2000 DB to Access

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

export SQLServer2000 DB to Access

Michael Peters

2006-02-25, 9:44 am

(sorry for crossposting, I usually don't but I need a solution quickly)

is there a simple way to remotely and programmatically (using a ColdFusion
application that can access the database) making a copy of an entire
SQLServer2000 database into Access?

Because I cannot remote control the server, DTS cannot be used.

I have created an Access mdb with empty tables in place, then tried using Ad
Hoc queries such as

insert into OPENROWSET('Microsof
t.Jet.OLEDB.4.0',
'c:\myaccessdb.mdb';;,mytable) select * from mytable

but although the SQLServer security settings apparently allow Ad Hoc
queries, I keep getting error messages saying that the ad hoc query access
was denied. I don't know what to do now.

-Michael

Sponsored Links