asked on

Access VBA SQL - Create a new table

Hi

I am using the following code to connect to a SQL 2008 database.
How do I update the following code to create a new table with three
columns: Col1, Col2, Col3.

Thanks

Sub ConnectToSQLServer()

    On Error GoTo EH
    'Assuming the SQL Express database is configured for remote connections you can. You generally do this by opening a connection to the database and then issuing your query:
    'Ref Microsoft AxtiveX Data Objects 6.0 Library
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"
     
    'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    '/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2
    
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM MyTable", con
    
    'These are the basics, but this would leave you with a fully filled Recordset.
    
    'Note that unless your online host has assigned a name for this, you'd have to use the full IP address as "Server" portion

Exit Sub

EH:
    MsgBox Err.Description
    
End Sub

Open in new window

Bitsqueezer

Hi,

you could do this with ADO by executing a T-SQL command which creates the wanted table like this:

Public Sub CreateTableADO()
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=chestnut;"

    Set cmd = New ADODB.Command
    
    With cmd
        .CommandText = "CREATE TABLE NewTableName " & _
                       "             (Col1 AS nvarchar(50), Col2 AS nvarchar(50),Col3 AS nvarchar(50))"
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
End Sub

Open in new window

This is the hard-coded variant, normally you would create a sub with parameters so this can be used for different table creations - but the disadvantage of this method is that in this case (because you are assembling a command as text) you would open the code for possible SQL injection.

Another variant which is more secure and is more object oriented is using the SQLDMO library. You must set a reference to the Microsoft SQLDMO library before you can use this, in this case you would create the table like this:

Public Sub CreateTableDMO()
    Dim objSQLServer As SQLDMO.SQLServer
    Dim objDB As SQLDMO.Database
    Dim objTbl As SQLDMO.Table
    Dim objCol As SQLDMO.Column
    Dim strColumns() As Variant
    Dim strColumn As Variant
    
    strColumns = Array("Col1", "Col2", "Col3")
    
    Set objSQLServer = New SQLDMO.SQLServer
    With objSQLServer
        .LoginSecure = True
        .Connect "196.220.43.247,1444", "murbro", "chestnut"
        
        Set objDB = New SQLDMO.Database
        Set objTbl = New SQLDMO.Table
        
        With objTbl
            .Name = "NewTableName"
        End With
        
        For Each strColumn In strColumns
            Set objCol = New SQLDMO.Column
            With objCol
                .Name = strColumn
                .DataType = "nvarchar"
                .Length = 50
            End With
            
            objTbl.Columns.Add objCol
        Next
        
        .Databases("sqlserver1").Tables.Add objTbl
    End With
End Sub

Open in new window

As there is no string which is assembled there is no possibility for SQL injection and the clear structure allows better use of possible parameters for the sub to avoid hard-coding the column names.

Cheers,

Christian

Murray Brown

ASKER

Hi

Thank you. I ran that first procedure and got an error
'nvarchar' is not a recognized built in funtion name

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

Great/. Worked well. Much appreciated!

Bitsqueezer

Thanks, you're welcome.