Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    I'm working on a strange little system that gathers info from a form and stores such on SQL Server. I decided to use Access with linked tables to support further processing on the info for our people here. Now I am looking at how to do the same thing in reverse: set up a link from an Access table into SQL Server. I find that linked tables work tolerably well in ensuring that info in SQL Server appears in 'real time' while mucking about in Access.

    So, I started in on doing the same thing in SQL Server. What I have so far works as follows:

    1. Set up a DTS package to create a pipe into Access
    2. Running the package pops said data into the table
    ...

    aaaand, that's all -- sort of. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

    The data isn't really 'linked' yet. You have to re-run the DTS package to grab new data. I started to put together a stored proc that will do a truncate to wipe out the data in the table and thought to myself, 'Self, now how about we also run the DTS package in the stored proc?' -- with the scheme being, on the webpage to come, there will be a button saying 'refresh data' that triggers the stored proc., which means, essentially, deleting the data in the table and doing a re-load via the stored proc. Now I've hit a small snag: I don't see a way to run a DTS package from a stored proc. After looking at Books Online it seems that may be a bit difficult at best.

    Now, you will notice this is a work-around to emulate what Access does automatically when linking a table. Perhaps there's some more elegant way to do this that I am not aware of?

    Any input is greatly appreciated.

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  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: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    Steve,
    You can link the Access database as a linked server using the following:
    EXEC sp_addlinkedserver 'servername', 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'PathAndFilenameHere.mdb'
    EXEC sp_addlinkedsrvlogin 'servername', 'False'

    just replace servername with whatever you want to call it.

    then in procedures you can use something like:
    SELECT * FROM servername...tablename
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    Rory has given you the straight answer, but why not just store the data in SQL Server to begin with - it would solve a number of potential problems with multi-user Access databases.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    thanks for the suggestion. i actually did setup a linked server to an access db using the wizard but i am not sure how I am supposed to access the tables in a view/stored proc. Your example was a little too pithy. Once I get a linked server setup, do I need to create a reference to the db in the Database folder? Any suggestions on that?

  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: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    If you're using Enterprise Manager, you will see the linked server under Security - Linked Servers.
    In views or SPs you use the tables the same way you would any local table except in the FROM clause you refer to the table in the form:
    <code>servername...tablename</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    hmmm...ok... but the linked server is named as the IP address of my local machine, so dot notation confuses SQL server. or do you actually use three dots?

    anyways, i think it's cool that i can even setup a linked server. my original scheme, to run a stored proc that re-loads the table from a DTS would work too but I haven't figured out how to invoke a DTS pack from a stored proc. perhaps using the sp_cmd to run DTS in the command prompt, but that's a BIG security risk...

    Also, a general question: how do you specify the location of a source file for DTS to use in a dataload using UNC?

  7. #7
    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: Using Access as a 'back end' to SQL Server (SQL Server 2K, Access 2K)

    Personally, I'd give it a different name rather than using the IP address. Yes you use the three dots (servername.databasename.owner.tablename but you only need to specify the first and last for this).
    I'm afraid I know almost nothing about DTS as I leave all that stuff up to the DBA!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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