[dba-SQLServer] ADOX help

Thanks Marty -- I know the cat statement's needed -- I hate those kinds of
mistakes. :( I'll take a look at that site -- might help me a lot today. 

Susan H.  

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
MartyConnelly
Sent: Wednesday, March 08, 2006 11:54 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] ADOX help

A couple of examples, what you were missing was a Set cat.ActiveConnection =
con statement.
most of the info on setting field types is in "C:\Program Files\Microsoft
Data Access SDK\Docs\ado260.chm"

Sub ADOX_CreateSQLTable()
'from
'http://support.microsoft.com/?kbid=275252
' reference Microsoft ADO Ext. 2.x for DDL and Security ADO 2.8

'This example demonstrates creating a  table with 'Autoincrement, Decimal,
and Memo columns.

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog

'Return Reference to current database.
'Set cat.ActiveConnection = CurrentProject.Connection Dim con As
ADODB.Connection Dim strConnection As String

Set con = New ADODB.Connection

strConnection = "Provider=SQLOLEDB.1;" & _ "Data Source=.;" & _ "Initial
Catalog = pubs;" & _ "Trusted_Connection=Yes"

Debug.Print (strConnection)
con.Open (strConnection)
Set cat.ActiveConnection = con
'Assign the new table name.

With tbl
  .Name = "Department"

    ' Append new columns to the table.
    With .Columns
     
      .Append "DepartmentHead", adWChar, 20
      .Append "MyDecimal", adNumeric

            'After appending columns, set
            'provider specific properties.
            With !MyDecimal
                Set .ParentCatalog = cat
                    .Precision = 2
                    .NumericScale = 1
            End With

            With !MyVarChar
                Set .ParentCatalog = cat
            End With

    End With
End With

' Append new table to the provider catalog and clean up.
cat.Tables.Append tbl

Dim col As ADOX.Column
For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
'If tbl.Name = tblName.Name Then
For Each col In tbl.Columns
Debug.Print (col.Name)
Next
'End If
End If
Next
Set cat = Nothing
End Sub


Sub ADOX_CreateSQLTableAlt()
'from
'http://support.microsoft.com/?kbid=275252
' reference Microsoft ADO Ext. 2.x for DDL and Security ADO 2.8

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog

'Return Reference to current database.
'Set cat.ActiveConnection = CurrentProject.Connection Dim con As
ADODB.Connection Dim strConnection As String

Set con = New ADODB.Connection

strConnection = "Provider=SQLOLEDB.1;" & _ "Data Source=.;" & _ "Initial
Catalog = pubs;" & _ "Trusted_Connection=Yes"

Debug.Print (strConnection)
con.Open (strConnection)
Set cat.ActiveConnection = con
'Assign the new table name.

With tbl
  .Name = "Department"

    ' Append new columns to the table.
    Dim col As ADOX.Column
    Set col = New ADOX.Column
    With col
      .Name = "DepartmentHead"
      .Type = ADOX.DataTypeEnum.adChar
      .DefinedSize = 20
      .Attributes = ADOX.ColumnAttributesEnum.adColNullable
    End With
      .Columns.Append (col)
      .Columns.Refresh
End With

' Append new table to the provider catalog and clean up.
cat.Tables.Append tbl

Dim colp As ADOX.Column
For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
'If tbl.Name = tblName.Name Then
For Each colp In tbl.Columns
Debug.Print (colp.Name)
Next
'End If
End If
Next


Set cat = Nothing

End Sub


Susan Harkins wrote:

>Probably -- just need examples of ADOX though. 
>
>Susan H. 
>
>Can't you just submit an 'ALTER TABLE' statement rather than going 
>through ADOX?
>
>
>Here's what I'm trying to do and can't -- I want to append a column to 
>an existing table, using ADOX. The code seems unnecessarily complex, 
>and it doesn't work. :( I don't get an error, it just doesn't work. The 
>FOR loop just runs through the Columns collection after the append, to 
>see if it worked. It really isn't part of the failing technique, 
>because that part works fine. The connection is fine.
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>  
>

--
Marty Connelly
Victoria, B.C.
Canada



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/276 - Release Date: 3/7/2006