Linking to SQL database (all)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans. I’ll check it out. I suppose I’ll have to learn ADO now!

Reply | Quote

(Edited by Patricia W on 07-Apr-05 23:30. )

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

Reply | Quote

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.

Reply | Quote

Well, this is as good a reason as any to finally bite the bullet and learn ADO! But thanks for the tip.

Reply | Quote