linked server between microsoft sql server 2005 and access proplem

[ahm1985]

Hi guys Can i make linked server between sql server 2005 found in domain and Microsoft access 2010 found in another computer outside domain.

 

[djj55]

Are you trying to access the SQL server from Access or the Access database from SQL server?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[ahm1985]

The Access database from SQL server?

 

[ahm1985]

when i make linked server which place i write ip or address the computer that have access database

 

[gmmastros]

This will be more difficult if the Access database is on a different computer.

SQL Server runs as a service. By default, when you install SQL Server, the log on account for the sql server service is set to "Local System". The local system account does not have permissions to other computers on the network.

In order to set up a linked server to an Access database on another network computer, you'll need to make sure that the logon account for the SQL Server service has permissions.

On the SQL Server computer...
Click Start -> Run
type: services.msc
scroll down to SQL Server
Right Click -> Properties
Click the "Log On" tab.

If your sql server is configured for "Local System Account", you will need to change this. Be aware that changing this will require you to restart the SQL Server service. Restarting the service doesn't usually take long (a minute or 2), but all connections to the server will be stopped, and nobobdy will be able to access it while the service is restarting. Also be aware that all of the table data SQL Server had cached in RAM will be cleared from RAM when you restart the service. The result of this is that your database may appear to run a little slower after it is restarted while it is re-loading that cached data back in to RAM.

Anyway....

I always recommend that people create a special windows active directory account specifically for SQL Server. You'll want to make sure that account has full permissions to the SQL Server computer, and only enough permissions on the network that you absolutely need. For example, the account you create should only have permissions to the folder where you have your Access database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ahm1985]

ok good
but what i write in these fields when i make new linked server linked server,product,datasource
my connection between sql server 2005 and office 2010

 

[gmmastros]

I usually ignore the GUI interface and create linked servers from TSQL code. Try this:

Open SQL Server Management Studio
Click on "New Query"
Copy/Pate the following code:

exec sp_addlinkedserver	@server='[!]WhateverYouWantToCallIt[/!]',
	@srvproduct='Access',
	@provider='Microsoft.ACE.OLEDB.12.0',
	@datasrc='[!]C:\Folder\subfolder\DatabaseName.mdb[/!]'

You'll need to change the parts in red. @server is simply the name that you will use in code to refer to the access database.

@datasrc is the full path and filename for your access database.

If your access database is password protected, you'll need to identify the login SQL Server will use to connect to the database. Like this:

exec sp_addlinkedsrvlogin @rmtsrvname='[!]WhateverYouWantToCallIt[/!]', 
	@useself='false', 
	@rmtuser='[!]UserNameHere[/!]', 
	@rmtpassword='[!]PasswordHere[/!]'

Again, you need to change the parts in red. For @rmtsrvname, you need to use the same name that you used in the previous query.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ahm1985]

I try to execute linked server first from my local system i have sql server 2005 and i bring database access and put it in this path 'D:\SHARED HOM\Hom.mdb' in my local system and i use provider Microsoft jet oledb.4.0 because database i bring created to version before 2010
then i write this code in query analyses
exec sp_addlinkedserver @server='DATTA6',
@srvproduct='ACCESS',
@provider='Microsoft Jet OLEDB.4.0',
@datasrc='D:\SHARED HOM\Hom.mdb'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'DATTA6',
@useself = 'false',
@locallogin = null,
@rmtuser = 'Admin',
@rmtpassword = null
To test i use below query
SELECT * FROM OPENQUERY(DATTA6, 'SELECT * FROM CityDirectory')
then after this message below error show to me why
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft Jet OLE DB.4.0" has not been registered.
why it give me this error

 

[gmmastros]

I see two problems.

The d drive is on your local computer. When you specify the location of the drive, it must be as the server sees it. The servers d drive is not your d drive. Try something like, \\your-computer-name\d\folder\database.mdb

The not her problem is that your server does not have the jet oledb provider installed on it. Try a Google search for "Microsoft jet oledb provider download". Install the provider directly on the server.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom