Listing and Creating tables with an ADODB.Connection 
Author Message
 Listing and Creating tables with an ADODB.Connection

Is there anyway to retrieve a list of the tables in a database? and after
I've done that, how can I create a new one?

Thanks.
Kelly Cromwell



Sun, 27 Nov 2005 05:11:54 GMT  
 Listing and Creating tables with an ADODB.Connection
use ADOX ... like this...

'------------
dim myCnn as New ADODB.Connection
myCnn.Open ...

dim cat as new ADOX.Catalog
cat.ActiveConnection= myCnn

dim table as new ADOX.Table
for each table in cat.tables
    debug.print table.name
next
'------------

and if you wanna create a table...
(continue using previous code)
set table=new ADOX.Table
table.Columns.Append "MyTxtFieldName", adVarChar, 255
cat.Tables.Append oldTbl

good luck
Sguindaw

Quote:

> Is there anyway to retrieve a list of the tables in a database? and after
> I've done that, how can I create a new one?

> Thanks.
> Kelly Cromwell

--
Sguindaw -> "gesteo onis: hacer que las cosas sean"


Sun, 27 Nov 2005 10:55:13 GMT  
 Listing and Creating tables with an ADODB.Connection
Hi Kelly,

You could get list of tables and other database objects, using OpenSchema
method of ADO connection. Another way is to use ADOX library. Both methods
have some limitations, since information, which those methods return,
depends on OLEDB provider's capabilities to get that info from the database.
Usually there is no problem to get information about tables, but could some
problems to get more extensive information, like indexes, relations etc.
To create tables there are also several ways.
1. Use DDL statements like, CREATE TABLE.... to do that. It usually works
with any database
2. Use ADOX, but it has a lot of limitations, since, primarily, it was
designed to work with Access database and usually you cannot create object
in other types of database
2. Use specific to database library, if it exists. For example, in case of
SQL Server there is a SQL DMO COM library, which is similar to ADOX, but was
designed specifically for SQL Server and uses all its capabilities to manage
database objects

--
Val Mazur
Microsoft MVP


Quote:
> Is there anyway to retrieve a list of the tables in a database? and after
> I've done that, how can I create a new one?

> Thanks.
> Kelly Cromwell



Sun, 27 Nov 2005 20:29:51 GMT  
 Listing and Creating tables with an ADODB.Connection

Is there anyway to retrieve a list of the tables in a database? and after
I've done that, how can I create a new one?

Here is another way to list the tables (using Access):

Sub OpenReadOnlyMDB()
   Dim conn As ADODB.Connection
   Dim rstSchema As ADODB.Recordset
   Dim strCnn As String

   Set conn = New ADODB.Connection
        strCnn = "Provider=Microsoft.Jet.oledb.4.0;" & _
        "Data Source=E:\My Documents\db1.mdb"
   conn.CursorLocation = adUseServer
   conn.Open strCnn

  'Open the tables schema rowset
   Set rstSchema = conn.OpenSchema(adSchemaTables)

  'Loop through the results and print the names in the Immediate window
    While Not rstSchema.EOF

        If rstSchema.Fields("TABLE_TYPE") = "TABLE" Then _
                Debug.Print rstSchema.Fields("TABLE_NAME")

        rstSchema.MoveNext
    Wend
    rstSchema.Close
    conn.Close
End Sub

Create/manipulate a Jet database:

HOWTO: Create a Table with Primary Key Through ADOX
http://support.microsoft.com/default.aspx?scid=kb;EN-US;252908

HOWTO: Use ADO and ADOX to Modify the Base Query of an Access QueryDef Object in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;255782

HOWTO: Use ADOX to Create an OLE Object Field in an Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;296173

ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;275249

ACC2000: How to Create a Table with Jet Data Types via ADOX
http://support.microsoft.com/default.aspx?scid=kb;en-us;275252


Microsoft MVP (Visual Basic)



Sun, 27 Nov 2005 21:34:32 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Visual Basic ADODB Open Connection Make Table Query

2. ADODB.Connection and ADODB.Recordset question

3. ADODB.Connection.Execute VS ADODB.Recordset.Update

4. ADODB.connection & ADODB.Recordset

5. Creating adodb.record from Adodb.recordset

6. Main program clogs when adodb.connection object can't make connection

7. listing tables using connection.openschema

8. How do you create a Library List for AS/400 ADO Connection

9. Create Comma-Separated List from A Table

10. Dial Up Connections - Listing Available Connections

11. ADODB connection

12. ADODB.Connection error 800a01ad

 

 
Powered by phpBB® Forum Software