Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Linking to SQL database (all)

    I know how to link to tables in a SQL server database, if I know the table name. What I want to do is find out all the tablenames in a SQL server database, so I can cycle through them and establish the links. Anyone know how I can do this?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking to SQL database (all)

    Hi Mark,

    You can use the OpenSchema method of the ADO Connection with the adSchemaTables argument. Look up OpenSchema in the online help, or see How To Use the ADO OpenSchema Method in Visual Basic.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking to SQL database (all)

    Thanks Hans. I'll check it out. I suppose I'll have to learn ADO now! <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking to SQL database (all)

    <P ID="edit" class=small>(Edited by Patricia W on 07-Apr-05 23:30. )</P>There is a table called 'sysobjects' which I think contains table names as well as all sort of other items -- but it might not available in the database you are connected to depending upon your privileges -- I do not know if sysobjects goes with each database or with Master. (I decided not to test my connections right at present ...)

    I used to get information from these system tables before I heard it was not particularly kosher, or at least, there are dangers.

    A database that I worked on at one time kept a local table of all linkable table names, just as a thought, although that's kludgey.

    thx
    'Bad' Pat

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

    Re: Linking to SQL database (all)

    I believe there is a stored procedure that will return the names of all the tables in a SQL Server database. If you don't want to learn ADO you could run a pass-through query that calls that stored procedure and return the list of table names. This Database Journal article by a fellow member of the Denver Area Access User Group gives some further guidance on running sprocs from Access.
    Wendell

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Linking to SQL database (all)

    Well, this is as good a reason as any to finally bite the bullet and learn ADO! But thanks for the tip.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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