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

    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"

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

    '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

    rsComm.ActiveConnection = rsConn

    rsConn.Execute strSql

    MsgBox "Change made"

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    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