Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    56

    Unanswered: ALTER TABLE syntax issue

    I'm having trouble running code from EXCEL (2007) to alter a query in our backend DB file. I can add (create) a query and delete (drop) it, but not alter it.

    Can anyone fix the following code, which won't work for the ALTER statement.
    Thanks in advance!
    =========================================

    Dim rsConn As ADODB.Connection
    Dim rsComm As ADODB.Command
    Dim strQueryName As String

    Set rsConn = New ADODB.Connection
    Set rsComm = New ADODB.Command

    strQueryName = "aaa_Query4"

    'ADD: THIS WORKS!
    ' strSql = "CREATE VIEW " & strQueryName & " AS SELECT Table1.ID, Table1.Field1 FROM Table1"
    '(NOTE: New query is visible after running Compact of db file)

    'DELETE: THIS WORKS!
    'strSql = "DROP VIEW " & strQueryName
    '(NOTE: Deleted query immediately disappears from db file)

    'EDIT: NONE OF THESE WORK!!! (Error: "Syntax error in ALTER TABLE statement")
    'strSql = "ALTER VIEW " & strQueryName & "AS SELECT Table1.ID FROM Table1"
    strSql = "ALTER TABLE " & strQueryName & "AS SELECT Table1.ID FROM Table1"
    'strSql = "ALTER TABLE " & strQueryName & "DROP COLUMN Table1.Field1"

    rsConn.ConnectionString = gMDBconnect
    '(gMDBconnect = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joe\Documents\MyDatabase.mdb;Jet OLEDBatabase Password=xxxx

    rsConn.Open
    rsComm.ActiveConnection = rsConn

    rsConn.Execute strSql

    MsgBox "Change made"
    ===============================================

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Firstly, this is really an Access question rather than an Excel one.

    Anyway - when debugging dynamic SQL errors the very first step must be to look at what is being executed. If you stick in a Debug.Print strSql I think you will see the error immediately and kick yourself up the bum for missing it. You also might spot the error a little easier in fixed width font (e.g. courier). If you wrapped the above in [ code][/ code] tags it would make it more obvious.

    Anyhoo - print out the contents of strSql and let us know if you spot the error. If you don't then post the contents here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •