Using ADOX to Create/Modify Tables in SQL Server

[Steve R.]

Can it be done? I have an Access front end connected to SQL Server as the back end. I can connect to the SQL Server with ADOX and view the data, but I cannot seem to create/modify tables with an ADOX connection within SQL Server.

The code below works in the limited sense that it creates a LOCAL table within Access. However, it does not meet the objective of creating a table within SQL Server. I had thought that with an established ADOX connection that your point-of-reference would be within SQL Server, meaning that you could create/modify tables there. Apparently that is not the case.

Can an ADOX connection be used to create/modify tables to an external SQL Server?

Private Sub ADOX_ConnectToServer02()
    Dim strDBCursorType As String
    Dim strDBLockType As String
    Dim strDBOptions As String
    Dim strSQL
    Dim rst As ADODB.Recordset
    Dim CNN As ADODB.Connection
    Dim i As Integer
    Dim strTableName As String
    Rem ------------------------------------------------
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
    Dim prp As ADOX.Property
    Rem -----------------------------------------------
    
    strDBCursorType = adOpenDynamic
    strDBLockType = adLockOptimistic
    strDBOptions = adCmdText
    
    Set CNN = New ADODB.Connection
    CNN.Open ADOX_ConnectString()
    
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = New ADOX.Table
    With tbl
        .Name = "TESTX"
        Set .ParentCatalog = cat
        .Columns.Append "FamID", adInteger
    End With
    cat.Tables.Append tbl
    
    'Clean up
    Set prp = Nothing
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    Set rst = Nothing
    Set CNN = Nothing
End Sub

 

[Steve R.]

A solution found. It appears that I have located a Non-ADOX solution that would be much better. Chapter 26 of Access 2007 Inside/Out covers creating an Access Project File. I was able to bring up (view) the structure of the SQL Server tables. Fully exploring this option will have to wait till the New Year. Happy New Year.