Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    19

    Unanswered: Linked Tables ODBC

    Hi Forum, although I have a fair bit of experience with Access & VBA on a stand-alone workstation I have run into an area which finds me a bit lacking, namely linking tables with an OBBC Data Source/SQL server in a network environment. I have developed a database which I want to pull data into from our ERP App(Sage Office Line) which is Access based. I want to install this when finished onto 5 work stations. It calculates dealerships within a given radius of a given ZIP/Postcode and displays customer names and account info in an easy to read format and other functions that the ERP App is sadly lacking. I don't want the users to be able to change any of the information in the source database via the forms in my database, just view it. How can I ensure that this is the case? (I don't want to cause any problems with the ERP App, locking records etc, or my name will star with an "A" and end with "hole" ;-) Linking and copying the tables at run time is one way I suppose, but makes the start-up time too long. Can ODBC data sources be opened "read-only" how can I best achieve my aim?

    Regards...... Nick Ellis

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How often do you need your data updating?
    I suggest you just have a scheduled DTS running on your SQLServer which drops the table data and re-imports the data every day or so.

    Simple solution if you don't need the data to be 100% live.
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could create a SQL Server user with read only rights, and link your tables with that user. I've done that with a couple of apps.
    Paul

  4. #4
    Join Date
    Oct 2004
    Posts
    19

    Thanks!!

    Hey guys, thanks for the quick replies, as I said I am moving into new ground with SQL Server environment and ODBC sources here! Both replies seem to do the deal but if you could point me towards suitable articles / documentation / examples I can perhaps teach myself further. (I don't want to die stupid, and I don't want you to do all my work for me either ;-)

    Regards Nick

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    BOL (Books Online) is the first stop on any journey into SQL Server. It should have been installed with the program.
    Paul

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, the guys at my work unticked that when they installed 2005 for me :s

    In SS2K5, DTS packages are fairly well hidden away under "legacy".

    I don't think you'll have much trouble learning how they work, they're fairly simple. You pretty much tell it what to do in simple steps, then join the steps together.
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2004
    Posts
    19

    Thanks again

    Ok, thanks again, now I know where to look!!

    Regards Nick

Posting Permissions

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