Susan Harkins
harkinsss at bellsouth.net
Wed Mar 8 13:05:55 CST 2006
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. Susan H. 'Append column to existing table 'using ADOX. Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim tblName As ADOX.Table Dim col As ADOX.Column Dim con As ADODB.Connection Dim strConnection As String con = New ADODB.Connection strConnection = "Provider=SQLOLEDB.1;" & _ "Data Source=.\SQLEXPRESS;" & _ "Initial Catalog = AdventureWorks;" & _ "Trusted_Connection=Yes" Debug.Print(strConnection) con.Open(strConnection) cat = New ADOX.Catalog cat.ActiveConnection = con tblName = New ADOX.Table tblName.Name = "Department" 'With tblName.Columns ' .Append("DepartmentHead", ADOX.DataTypeEnum.adChar) ' .Refresh() 'End With col = New ADOX.Column With col .Name = "DepartmentHead" .Type = ADOX.DataTypeEnum.adChar .DefinedSize = 20 .Attributes = ADOX.ColumnAttributesEnum.adColNullable End With tblName.Columns.Append(col) tblName.Columns.Refresh() tblName.ParentCatalog = cat 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 -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Wednesday, March 08, 2006 1:31 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] ADOX help It looks like the SQL Server Users and Groups collections aren't supported for ADOX -- anyone know for sure -- I may have misunderstood what I read. Susan H. Anybody here familiar with using ADOX and SQL Server and have some free time today? I need help completing an article -- all I would need is a few snippets of generic code -- but I need them today. Specifically, I'd like an example of appending a column to an existing table or appending a new table to a database. Also, I'd like a security example -- not as specific on it -- could be as simple as appending a new user to an existing group. _______________________________________________ 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