By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

How to loop through SQL Server tables

P: n/a
Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?

(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).

Dec 28 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Ka********@gmail.com wrote in news:1167267102.836756.85820
@f1g2000cwa.googlegroups.com:
Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table
<snips>
I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).
Public Sub GetTableNames()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Set c = New ADODB.Connection
With c
..Provider = "sqloledb.1"
With .Properties
..Item("Data Source") = "server"
..Item("Initial Catalog") = "database"
..Item("PassWord") = "password"
..Item("User ID") = "userid"
End With
..Open
Set r = .OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
With r
' show the field names
' information available
For Each f In .Fields
Debug.Print f.Name
Next f
Debug.Print
' show the table names
While Not .EOF
Debug.Print .Fields("TABLE_NAME"), .Fields("TABLE_TYPE")
.MoveNext
Wend
End With
End With
End Sub
--
Lyle Fairfield
Dec 28 '06 #2

P: n/a
On 27 Dec 2006 16:51:42 -0800, Ka********@gmail.com wrote:

I think Lyle's solution is more elegant than mine, but you could
certainly link sysobjects table to your MDB, and then select from it.
I believe tables have a Type='U'.

-Tom.

>Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?

(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).
Dec 28 '06 #3

P: n/a
Great - thx! - works perfectly. I am a little unclear on how one would
determine what arguments are available in the Array( ) portion of the
OpenSchema line. I will continue to look into it, but if you feel like
providing additional info, great! Thanks again though.

On Dec 27, 8:24 pm, Lyle Fairfield <lylefairfi...@aim.comwrote:
Kandar7...@gmail.com wrote in news:1167267102.836756.85820
@f1g2000cwa.googlegroups.com:
Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table<snips>
I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).Public Sub GetTableNames()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Set c = New ADODB.Connection
With c
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "server"
.Item("Initial Catalog") = "database"
.Item("PassWord") = "password"
.Item("User ID") = "userid"
End With
.Open
Set r = .OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "TABLE"))
With r
' show the field names
' information available
For Each f In .Fields
Debug.Print f.Name
Next f
Debug.Print
' show the table names
While Not .EOF
Debug.Print .Fields("TABLE_NAME"), .Fields("TABLE_TYPE")
.MoveNext
Wend
End With
End With
End Sub

--
Lyle Fairfield
Dec 28 '06 #4

P: n/a
Thanks! - this options works as well. I will explore this table
further...seems like it could be useful in many ways.

On Dec 27, 9:33 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On 27 Dec 2006 16:51:42 -0800, Kandar7...@gmail.com wrote:

I think Lyle's solution is more elegant than mine, but you could
certainly link sysobjects table to your MDB, and then select from it.
I believe tables have a Type='U'.

-Tom.
Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?
(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).- Hide quoted text -- Show quoted text -
Dec 28 '06 #5

P: n/a
Ka********@gmail.com wrote in
news:11**********************@a3g2000cwd.googlegro ups.com:
Great - thx! - works perfectly. I am a little unclear on how one
would determine what arguments are available in the Array( ) portion
of the OpenSchema line. I will continue to look into it, but if you
feel like providing additional info, great! Thanks again though.
The fields were:

1. TABLE_CATALOG
2. TABLE_SCHEMA
3. TABLE_NAME
4. TABLE_TYPE
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED

The qualifier was

Array(Empty, Empty, Empty, "TABLE")

which specified

1. no criterion for TABLE_CATALOG
2. no criterion for TABLE_SCHEMA
3. no criterion for TABLE_NAME

4. TABLE_TYPE="Table" (which excluded the System Tables); We might have
specified "ALIAS", "TABLE", "SYNONYM","SYSTEM TABLE","VIEW","GLOBAL
TEMPORARY", "LOCAL TEMPORARY" or "SYSTEM VIEW"

We could have included more Emptys for
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED
but that was unnecessary as ADO OpenSchema defaults to Empty after the last
Field referenced (so no Array will return all information available).

We could replace any Empty with an Equal Criterion for that Field.

If we specify
adSchemaProviderSpecific
and a
SchemaID
we may get other fields; I have not explored this.

--
Lyle Fairfield
Dec 28 '06 #6

P: n/a
Thanks - very interesting and useful. chrs

On Dec 28, 1:18 pm, Lyle Fairfield <lylefairfi...@aim.comwrote:
Kandar7...@gmail.com wrote innews:11**********************@a3g2000cwd.googleg roups.com:
Great - thx! - works perfectly. I am a little unclear on how one
would determine what arguments are available in the Array( ) portion
of the OpenSchema line. I will continue to look into it, but if you
feel like providing additional info, great! Thanks again though.The fields were:

1. TABLE_CATALOG
2. TABLE_SCHEMA
3. TABLE_NAME
4. TABLE_TYPE
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED

The qualifier was

Array(Empty, Empty, Empty, "TABLE")

which specified

1. no criterion for TABLE_CATALOG
2. no criterion for TABLE_SCHEMA
3. no criterion for TABLE_NAME

4. TABLE_TYPE="Table" (which excluded the System Tables); We might have
specified "ALIAS", "TABLE", "SYNONYM","SYSTEM TABLE","VIEW","GLOBAL
TEMPORARY", "LOCAL TEMPORARY" or "SYSTEM VIEW"

We could have included more Emptys for
5. TABLE_GUID
6. DESCRIPTION
7. TABLE_PROPID
8. DATE_CREATED
9. DATE_MODIFIED
but that was unnecessary as ADO OpenSchema defaults to Empty after the last
Field referenced (so no Array will return all information available).

We could replace any Empty with an Equal Criterion for that Field.

If we specify
adSchemaProviderSpecific
and a
SchemaID
we may get other fields; I have not explored this.

--
Lyle Fairfield
Dec 28 '06 #7

P: n/a

Make a Pass-Thru query to your database and use:

Exec SP_Tables

As your query.

(If you don't already know this, be sure to set the ODBC connection
string in the Properties of the query.)

See the SQL BOL for more information on SP_Tables, and you might also
want to look at SP_Columns for a list of the columns in a table.
On 27 Dec 2006 16:51:42 -0800, Ka********@gmail.com wrote:
>Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?

(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Dec 28 '06 #8

P: n/a
On 28 Dec 2006 08:45:27 -0800, Ka********@gmail.com wrote:

sysobjects and the many other system tables and the several catelog
views (don't forget these; handy summary views!) are well documented
in SQL Server Books Online.

-Tom.

>Thanks! - this options works as well. I will explore this table
further...seems like it could be useful in many ways.

On Dec 27, 9:33 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On 27 Dec 2006 16:51:42 -0800, Kandar7...@gmail.com wrote:

I think Lyle's solution is more elegant than mine, but you could
certainly link sysobjects table to your MDB, and then select from it.
I believe tables have a Type='U'.

-Tom.
>Hi - I'm trying to work out a way to loop through (or just get a list
of) all of the tables in an SQL Server (2005) database from Access.
Based on the name of the table, I will then proceed to do some action
with it. I want to use a DSNless connection. I have searched but
haven't been able to sort it out just yet. Any ideas?
>(Note: I tried with DAO but my OpenDatabase kept failing - I think it
prefers a DSN and I'm trying DSNless. I also can connect with ADO but
then can't seem to figure out how to get a list of available tables).- Hide quoted text -- Show quoted text -
Dec 29 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.