How to loop through SQL Server tables

[Kandar7272]

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).

[Lyle Fairfield]

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

[Tom van Stiphout]

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).

[Kandar7272]

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 <[e-mail]:

[e-mail] 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

[Kandar7272]

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 <[e-mail]:

On 27 Dec 2006 16:51:42 -0800, [e-mail] 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 -

[Lyle Fairfield]

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

[Kandar7272]

Thanks - very interesting and useful. chrs

On Dec 28, 1:18 pm, Lyle Fairfield <[e-mail]:

[e-mail] 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

[Chuck Grimsby]

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

[Tom van Stiphout]

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 <[e-mail]:

>On 27 Dec 2006 16:51:42 -0800, [e-mail] 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 -