Access mdb as linked server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Access mdb as linked server

  1. #1
    Werner Zoller Guest

    Access mdb as linked server


    - During a transition phase (from Access 2.0 to SQL Server 2000) I am looking
    for a way, to easily access an Access 2 *.mdb from SQLServer 2000. Actually
    I want to use the data, which are originally changed in Access in SQL Server,
    kind of a one-way synchronization.

    - I thought, using the Access .mdb as a linked server would do the job.

    - I tested this on my notebook, and it to worked fine. I used the following
    sp's, the linked server worked fine:

    sp_addlinkedserver 'GrpDataMDB', 'Access 2',
    'Microsoft.Jet.OLEDB.4.0', 'R:\MyPath\GRPDATA.MDB'

    sp_addlinkedsrvlogin 'GrpDataMDB', false, 'sa', 'Admin', NULL

    - When I wanted to do the same thing on an SQL Server in the office, I got
    the errormessage: "OLE DB Provider 'Microsoft.Jet.OLEDB.4.4' reported an
    error. Authentication failed"

    - I assume, the reason is not authentication but the fact, that on my notebook
    SQLServer and .mdb where on ONE machine, in the other case they are on TWO
    machines and SQL Server cannot access the .mdb across the network. This however
    will cause a problem for whatever solution I can think of (a scheduled copy
    evtl. could also do the job).

    Have you got an idea, what I can do?

    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: Access mdb as linked server

    The issues that I can potentially see here are either a problem with the
    default workgroup information file (system.mdw) for Access, or a rights
    issue between SQL Server and the remote location on the network.

    The more likely is as rights or mapping issue on the network. Remember that
    you're mapped drive is just that - YOUR mapping of the drive letter. SQL
    Server doesn't operate under the same context that you do, so it's doesn't
    see an "R". You will need to use a full UNC path to get to the file in
    question. For example,

    EXEC sp_addlinkedserver 'MDBTest', 'Access 2',
    'Microsoft.Jet.OLEDB.4.0', '\\MyComputer\c$\Program Files\Microsoft
    Office\Office\Samples\INVENTRY.MDB'

    EXEC sp_addlinkedsrvlogin 'MDBTest', false, 'sa', 'Admin', NULL

    SELECT * FROM MDBTest...categories

    Yielded me a perfect set going from one system to another across a network.

    HTH,


    --
    Rob Vieira
    Visit www.ProfessionalSQL.com Today. It's new and improved!


    "Werner Zoller" <wzoller@mbbm.de> wrote in message
    news:3a6da258$1@news.devx.com...
    >
    > - During a transition phase (from Access 2.0 to SQL Server 2000) I am

    looking
    > for a way, to easily access an Access 2 *.mdb from SQLServer 2000.

    Actually
    > I want to use the data, which are originally changed in Access in SQL

    Server,
    > kind of a one-way synchronization.
    >
    > - I thought, using the Access .mdb as a linked server would do the job.
    >
    > - I tested this on my notebook, and it to worked fine. I used the

    following
    > sp's, the linked server worked fine:
    >
    > sp_addlinkedserver 'GrpDataMDB', 'Access 2',
    > 'Microsoft.Jet.OLEDB.4.0', 'R:\MyPath\GRPDATA.MDB'
    >
    > sp_addlinkedsrvlogin 'GrpDataMDB', false, 'sa', 'Admin', NULL
    >
    > - When I wanted to do the same thing on an SQL Server in the office, I got
    > the errormessage: "OLE DB Provider 'Microsoft.Jet.OLEDB.4.4' reported an
    > error. Authentication failed"
    >
    > - I assume, the reason is not authentication but the fact, that on my

    notebook
    > SQLServer and .mdb where on ONE machine, in the other case they are on TWO
    > machines and SQL Server cannot access the .mdb across the network. This

    however
    > will cause a problem for whatever solution I can think of (a scheduled

    copy
    > evtl. could also do the job).
    >
    > Have you got an idea, what I can do?
    >



    Share on Google+

  3. #3
    Werner Guest

    Re: Access mdb as linked server


    "Rob Vieira" <robv@nospam.removethis.ProfessionalSQL.com> wrote:
    >The issues that I can potentially see here are either a problem with the
    >default workgroup information file (system.mdw) for Access, or a rights


    No, this was not the case (maybe because I never used any security in Access)

    >EXEC sp_addlinkedserver 'MDBTest', 'Access 2',
    > 'Microsoft.Jet.OLEDB.4.0', '\\MyComputer\c$\Program Files\Microsoft
    >Office\Office\Samples\INVENTRY.MDB'


    Exactly this was the point!

    Just a remark:

    - I had to make sure, that the SQLServer-Service runs under a domain account,
    which has access to the file ..\INVENTRY.MDB, which is on a different server.

    - I first was surprised about the long filename together with Access 2 (16bit),
    but I assume, it's the OLE DB Provider (32bit), who has no problem to accesses
    the file and no 16bit-stuff is involved.

    Thanks a lot, I have ordered the 2000-edition of you book right away (I already
    had the one withou glasses) ;-).



    Werner Zoller

    BTW: Concerning the book: I am very happy about the PDF-Version of Jennings'
    Access 2000 book for lookups (not reading, it's not printable). Maybe this
    would be a suggestion for your editor!?

    Share on Google+

  4. #4
    Rob Vieira Guest

    Re: Access mdb as linked server


    >
    > BTW: Concerning the book: I am very happy about the PDF-Version of

    Jennings'
    > Access 2000 book for lookups (not reading, it's not printable). Maybe this
    > would be a suggestion for your editor!?


    Believe me - this suggestion has been made MANY times, but since Wrox is
    headed into the e-books market, they are less than motivated to get an
    electronic version away "free". I'm on your side, but I don't make the call
    on this one - sorry.


    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com


    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center