Connect to MSAccess on server X from SQL on server Y

I would like to connect to MSAccess 2003 database from a SQL 2005 database.
I tried using Linked Server but it will not work as my MSAccess database is
on a different server.
I am also trying to use OpenDataSource but I am getting an error.
SELECT top 100 *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Server\MyDatabase.mdb";
User ID=Admin;Password=xxxxx'
)...MyTable
Is there a way to do this?
Thanks!You didn't mention what error you are getting so it's hard
to say. But...you mention that a linked server won't work as
the access database is on another server. A linked server
will work in this case and if a linked server won't work due
to this, Opendatasource won't either. Does the service
account that SQL Server is running under have access to the
share on the remote server? Make sure you are using UNC
paths as well (instead of mapped drives).
-Sue
On Thu, 10 Aug 2006 13:16:02 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>I would like to connect to MSAccess 2003 database from a SQL 2005 database.
>I tried using Linked Server but it will not work as my MSAccess database is
>on a different server.
>I am also trying to use OpenDataSource but I am getting an error.
>SELECT top 100 *
>FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
>'Data Source="\\Server\MyDatabase.mdb";
>User ID=Admin;Password=xxxxx'
> )...MyTable
>Is there a way to do this?
>Thanks!