none
VBA ODBC TO SQL SERVER CREATE TABLE ERROR 3290 Syntax error in CREATE TABLE statement.

    Question

  • My example Code as follows returns 3290 Syntax error in CREATE TABLE statement. I Have linked to my SQL Server 2008 Database using the ODBC String Below. I need to create tables from VBA. Please assist - Thanks and Regards Rhett.



    Function CreateTableDDL()
    On Error GoTo ErrorCom: InitGlobals
    Dim strSql As String
       
        'Initialize
        SntSQLConStr = "ODBC;Driver={SQL Server Native Client 10.0};Server=tcp:new.insuria.co.za,1433;Database=SNetData;Uid=Rhett;Pwd=Ibc270469;Encrypt=no;Connection Timeout=30;"
        Set SDB = DBEngine.Workspaces(0).OpenDatabase("", True, False, SntSQLConStr) 'dbDriverNoPrompt
        If Err.Number <> 0 Or SDB Is Nothing Then
            MsgBox "Insuria Cannot Connect to the SQL SNetData Database." & vbNewLine & "Contact Insuria for support...", vbOKOnly, "SQL SNetData Database Connection Failure"
            Err.Clear: GoTo ErrorCom
        End If
        On Error GoTo ErrorCom: Err.Clear

        'Create the Contractor table.
        strSql = "CREATE TABLE dbo.ATest "
        strSql = strSql & "(ConID INT CONSTRAINT ConID_PK PRIMARY KEY (ConID), "
        strSql = strSql & "Surname NVARCHAR(MAX) NOT NULL, "
        strSql = strSql & "FirstName NVARCHAR(MAX) NOT NULL);"
        SDB.Execute strSql, dbFailOnError
       
    ErrorCom:   Debug.Print Err.Number, Err.Description: Stop: Err.Clear: Resume
                If ErrCon(Err.Description, Err.Number, "ImportSNet: SNetTblChk") Then Resume
    End Function


    Rhett Cawood Insuria CRM Financial Planning Access Developer


    Monday, November 11, 2013 6:53 AM

Answers

  • I don't think you can use (ACE/JET) WorkSpace/Database object to run a T-SQL String.

    You can either create an ADO Connection directly to the SQL Server database and then use the Connection Object to execute the CREATE TABLE T-SQL String directly on the SQL Server.

    See the following MSDN articles:

    Connection Object (ADO)

    Open Method (ADO Connection)

    Execute Method (ADO Connection)


    Van Dinh


    Monday, November 11, 2013 1:32 PM
  • Build + Setup a pass though query in the query builder.

    Then use this code:
        strSql = your sql create command as you have

       Set qdfPass = CurrentDb.QueryDefs("MyPass")
       qdfPass.SQL = strSql
       qdfPass.Execute

    So once you have a pass-through query built then it rather easy to send commands to the server as per above, and even better is the above is min amount of code.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada 

    Monday, November 11, 2013 5:19 PM
  • Rhett

    You're mixing apples and oranges. Thois example uses either user/pwd or trusted connection.

        Dim cnn As ADODB.Connection
        Dim strSQL As String
        Dim cmd As ADODB.Command
        
        Set cnn = New ADODB.Connection
    '    'OLEDB:
    '    'Standard:
    '    strConnect = "Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=MyDatabase;" _
    '        & "User Id=sa;Password=asdasd;"
        
        'Trusted:
        cnn.ConnectionString = "Provider=sqloledb;Data Source=MySQLServer;" _
                & "Initial Catalog=MyDatabase;Integrated Security=SSPI;"
        cnn.ConnectionTimeout = 30
        cnn.Open
        
        strSQL = "CREATE TABLE zzzzTest(ConID int IDENTITY(1,1) PRIMARY KEY, " _
            & "LastName nvarchar(50), FirstName nvarchar(50))"
        
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        Debug.Print cmd.CommandText
        cmd.Execute
        cnn.Close
        Set cmd = Nothing
        Set cnn = Nothing
        


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, November 11, 2013 6:35 PM

All replies

  • I don't think you can use (ACE/JET) WorkSpace/Database object to run a T-SQL String.

    You can either create an ADO Connection directly to the SQL Server database and then use the Connection Object to execute the CREATE TABLE T-SQL String directly on the SQL Server.

    See the following MSDN articles:

    Connection Object (ADO)

    Open Method (ADO Connection)

    Execute Method (ADO Connection)


    Van Dinh


    Monday, November 11, 2013 1:32 PM
  • Another option would be to use a pass-through query.
    Monday, November 11, 2013 2:14 PM
  • Build + Setup a pass though query in the query builder.

    Then use this code:
        strSql = your sql create command as you have

       Set qdfPass = CurrentDb.QueryDefs("MyPass")
       qdfPass.SQL = strSql
       qdfPass.Execute

    So once you have a pass-through query built then it rather easy to send commands to the server as per above, and even better is the above is min amount of code.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada 

    Monday, November 11, 2013 5:19 PM
  • Rhett

    You're mixing apples and oranges. Thois example uses either user/pwd or trusted connection.

        Dim cnn As ADODB.Connection
        Dim strSQL As String
        Dim cmd As ADODB.Command
        
        Set cnn = New ADODB.Connection
    '    'OLEDB:
    '    'Standard:
    '    strConnect = "Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=MyDatabase;" _
    '        & "User Id=sa;Password=asdasd;"
        
        'Trusted:
        cnn.ConnectionString = "Provider=sqloledb;Data Source=MySQLServer;" _
                & "Initial Catalog=MyDatabase;Integrated Security=SSPI;"
        cnn.ConnectionTimeout = 30
        cnn.Open
        
        strSQL = "CREATE TABLE zzzzTest(ConID int IDENTITY(1,1) PRIMARY KEY, " _
            & "LastName nvarchar(50), FirstName nvarchar(50))"
        
        
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        Debug.Print cmd.CommandText
        cmd.Execute
        cnn.Close
        Set cmd = Nothing
        Set cnn = Nothing
        


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, November 11, 2013 6:35 PM