Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

SQL SERVER 2005 LINKED SERVERS

I want to link a password protected Microsoft Access 97 Database to SQL Server 2005 as a linked server so that all of the mdb tables will be available through the sql server engine as if that database was a database inside of sql server.

Can i do this?

 if so can you give me a step by step

Thanks in advance.
0
talker2004
Asked:
talker2004
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Mark WillsTopic AdvisorCommented:
use the sp_linkedserver facility... Here I have an Access database name DB4 and lives in c:\access+mdb\db4.mdb. I create a Linked Server with a name Access_DB4 and can then use it in any query - just have to prefix the table name with the linked server name...


EXEC sp_addlinkedserver
      @server = 'Access_DB4',
      @provider = 'Microsoft.Jet.OLEDB.4.0',
      @srvproduct = 'OLE DB Provider for Jet',
      @datasrc = 'c:\access_mdb\db4.mdb'
GO

EXEC sp_addlinkedsrvlogin
      @rmtsrvname = 'Access_DB4',
      @useself = 'FALSE',
      @locallogin = NULL,
      @rmtuser = 'Admin',
      @rmtpassword = NULL
GO  -- note you will probably set up the real user name and password above. also found much more success leaving out useself and locallogin...

EXEC sp_tables_ex 'Access_DB4'
GO -- gets the list of tables...

SELECT * FROM Access_DB4...Customers

-- then remove the linked server if neede with : sp_dropserver AccessDB4, droplogins
0
 
talker2004Author Commented:
Got this to work only after i removed the database password of of the Access Database. It is a simple database password on the databsae not a workgroup password. How do i specify the password for the database so that SQL server can connect to my mdb?
0
 
Mark WillsTopic AdvisorCommented:
in  the @rmtuser and @rmtpassword = needs to be you access database user and password...
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now