Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Access database to SQL database connectivity (Access 2000/SQL)

    Hi All

    I have had one of those horrible Friday afternoon meetings with the boss!!

    My company is currently procuring a new intergrated solution to report business information to our customers via the web and the project has been pulled back by 6 months due to various implementation and legacy system problems. The Big Cheese has made some promises that have to held in regards to performance reports that he wanted published on the web.

    Some of our reporting systems use an Access back end that collates information from numerous area offices. To pull together certain data from legacy systems we are planning to use a Staging database that will generate web reports, from the basic information I have gathered it appears to be a SQL server.

    The question is, can a SQL server database link TO an Access database to utilise the table information? This is an issue I have never come across before!
    Jerry

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access database to SQL database connectivity (

    Hi Jerry,
    Yes, you can do this. (I'm assuming you want real-time data, otherwise you could use DTS to import the relevant data from your Access db each morning and run off that.) You can establish a Linked Server which uses the Jet OLEDB provider to connect to an Access db. You can then refer to the tables in your Access db in a view/procedure/function via the linked server. I believe you need to have the OLEDB provider installed on the server that is running the SQL Server instance.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access database to SQL database connectivity (Access 2000/SQL)

    Yes it is possible if you set up a linked server in SQL Server. <!mskb=306397>Microsoft Knowledge Base Article 306397<!/mskb> deals with using Excel as a linked server in SQL Server 2000. It will give you the general idea. The work would be done by the SQL Server dba, not by the Access developer.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access database to SQL database connectivity (

    Thanks Rory/Charlotte

    I was away from my desk when you posted this. Real time would be a preference as I can see that I would be stuffed with the daily upload.

    One of the developers did mention that could be an issue by linking in this manner that if the Access database got too many hits it could go belly up as I am all too aware when developing them in the office environment, is this still the case with this method?
    Jerry

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Access database to SQL database connectivity (

    Jerry,
    To be honest I don't know as I've never tried to do this for real. However, other than a regular data load, I can't imagine another way that would not potentially have the same issues. Without knowing your setup and what sort of hits you are expecting to get from this reporting system, it's hard to know the effect it might have on the back-end db.
    Incidentally, you can, AFAIK, schedule a DTS package to run on a regular basis, so once it's set up, you wouldn't have to be involved in the upload at all.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access database to SQL database connectivity (

    Thank you Rory

    This has given more than enough to ponder over. I feel a bit of testing is in order as the info being shown is a interesting as mud so I wouldn't expect many hits!!!!!!
    Jerry

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access database to SQL database connectivity (

    Just one little but important detail - is the version SQL Server 2000, or is it an older one? Things like the DTS feature were much less robust in earlier versions. It should also be noted that an Access database can be used by web pages just as SQL Server can.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •