We would like to have SQL connect with an Access database over a WAN.
Is the best way to use MSDE and allow SQL to connect directly to the
Access database or to convert this database every so often to SQL?
Thanks!
Ernie AdsettThe big question would be if you can get to the Access database at all over
the WAN, permission/security wise. For instance, you have a sqlserver
(server1) running under 'Joe' account and this account in no way can
access/connect to a remote computer (server2) due to windows permission, you
will not be able to get to the Access database.
If you can resolve the windows security issue, you can get to the Access
database either through a linked server (sp_addlinkedserver), ad-hoc
distributed query (opendatasource/openrowset). You can look these up in sql
book online for more info.
-oj
http://www.rac4sql.net
"Ernie Adsett" <ernie@.amt.nb.ca> wrote in message
news:POAPb.69773$IF6.1700881@.ursa-nb00s0.nbnet.nb.ca...
quote:
> We would like to have SQL connect with an Access database over a WAN.
> Is the best way to use MSDE and allow SQL to connect directly to the
> Access database or to convert this database every so often to SQL?
> Thanks!
> Ernie Adsett
>
|||To create a linked server to access an Access database
Execute sp_addlinkedserver to create the linked server, specifying Microsoft
.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb d
atabase file as data_source. The .mdb database file must reside on the serve
r. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
For example, to create a linked server named Nwind that operates against the
Access database named Nwind.mdb in the C:\Mydata directory, execute:
sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'c:\mydata\Nwind.mdb'
To access an unsecured Access database, SQL Server logins attempting to acce
ss an Access database should have a login mapping defined to the username Ad
min with no password.
This example enables access for the local user Joe to the linked server name
d Nwind.
sp_addlinkedsrvlogin 'Nwind', false, 'Joe', 'Admin', NULL
To access a secured Access database, configure the registry (using the Regis
try Editor) to use the correct Workgroup Information file used by Access. Us
e the Registry Editor to add the full path name of the Workgroup Information
file used by Access to thi
s registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Je
t\4.0\Engines\SystemDB
After the registry entry is configured, use sp_addlinkedsrvlogin to create l
ogin mappings from local logins to Access logins:
sp_addlinkedsrvlogin 'Nwind', false, 'Joe',
'AccessUser', 'AccessPwd'
Access databases do not have catalog and schema names. Therefore, tables in
an Access-based linked server can be referenced in distributed queries using
a four-part name of the form linked_server...table_name.
This example retrieves all rows from the Employees table in the linked serve
r named Nwind.
SELECT *
FROM Nwind...Employees|||Is Access forming a front-end db for a SQL DB?
Would it not be possible to utilise some kind of web site (ASP) or similar.
It will run much faster over a WAN.
Or are you 'replicating' the SQL Server db to an AccessDB?
You dont need MSDE to connect to a SQL Server DB. MSDE is the 'desktop'
version of SQL Server.
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
