[dba-VB] Build table

John,

You will need an ADO Connection object and an ADO Command object.  

You don't need to include "GO()" in the SQL Statement.  You only use that
when you are working in Query Analyzer.

Doris Manning
Database Administrator
Hargrove Inc.

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, July 10, 2007 11:25 PM
To: dba-vb at databaseadvisors.com
Subject: [dba-VB] Build table

I need to build a table in SQL Server given a server name, database name,
table name and a list of fields.  I have a rough draft as follows:

    Public Function mCreateTbl() As Boolean
        Dim strSQL As String
        With csvData.Columns
            Dim strName As String
            strSQL = "CREATE TABLE [" & mstrDatabaseName & "].[dbo].[" &
mstrTblName & "] (" & _
                     "[PKID] [int] IDENTITY (1, 1) NOT NULL "
            For Each strName In .Names
                strSQL = strSQL & ",[" & strName & "] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL "
            Next strName
            strSQL = strSQL & ") ON [PRIMARY] " & _
                              "GO()"
            Dim strSQLAddKey As String
            strSQLAddKey = "ALTER TABLE [dbo].[" & mstrTblName & "] WITH
NOCHECK ADD " & _
                         "CONSTRAINT [PKID] PRIMARY KEY  CLUSTERED (" & _
                            "[PKID]() " & _
                         ")  ON [PRIMARY] " & _
                         "GO()"
        End With
    End Function

I have not yet even started testing it to get the strings compilable in SQL
Server but if and when it comes together, what would the database objects
(ADO?) look like to execute such a beast?  Is anyone doing such a thing?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com