Method to insert all record from Access table to SQL server one

Anyone know if there is method that can insert all record from a table
in an MS Access 2000 database to a table in MS SQL Server 2000
database by a SQL statement? (Therefore, I can execute the statement
in my program)

--
Posted via http://dbforums.comTry OPENROWSET. For example:

INSERT INTO MyTable
FROM OPENROWSET
(
'Microsoft.Jet.OLEDB.4.0',
'c:\MyDatabases\\MyDatabase.mdb';
'admin';
'',
'SELECT * FROM MyTable'
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"king" <member29622@.dbforums.com> wrote in message
news:3341972.1063004486@.dbforums.com...
> Anyone know if there is method that can insert all record from a table
> in an MS Access 2000 database to a table in MS SQL Server 2000
> database by a SQL statement? (Therefore, I can execute the statement
> in my program)
>
> --
> Posted via http://dbforums.com|||Thanks for Dan Guzman!

I have tried your method, but I get returning error like:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
denied. You must access this provider through a linked server."

What can I do now?

--
Posted via http://dbforums.com|||Check out MSKB 327489:

http://support.microsoft.com/defaul...kb;en-us;327489

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"king" <member29622@.dbforums.com> wrote in message
news:3345907.1063072590@.dbforums.com...
> Thanks for Dan Guzman!
>
> I have tried your method, but I get returning error like:
> "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been
> denied. You must access this provider through a linked server."
>
> What can I do now?
>
> --
> Posted via http://dbforums.com