CODEWITHSUNDEEP
ms-accessvbams-access-2010
Kaw4Life
Kaw4Life

Reputation: 209

Cannot execute data definition statements on linked data sources. (Error 3611)

DoCmd.RunSQL ("ALTER TABLE table_name DROP COLUMN table_name_id;")

Is there a way around the limitation of this code not working on a linked table?

Upvotes: 1

Views: 1507

Answers (2)

HackSlash
HackSlash

Reputation: 5803

If you get this error when running the CurrentDB based code then you have to get a reference handle on the backend so you can run the command against it, and not the front end.

I do that with a property so that I only get the handle once, and reuse that handle during the life of the application.

Private thisBEDB As DAO.Database

'@Description("Get BE DB in a reliable manner")
Public Property Get thisBackend() As DAO.Database
    If (thisBEDB Is Nothing) Then
        ' Get link to back-end file and create reference table if missing
        Set thisBEDB = OpenDatabase(GetDBPath("table_name"))
    End If
    
    Set thisBackend = thisBEDB
End Property

For this to work you need the full path to the linked table. If you have more than one linked table you can look it up dynamically by table name using this GetDBPath Function:

'@Description("Returns full path to the backend. Optionally will use the table cited to pick a specific back end by link. When no table is specified it uses the first linked table it finds.")
Public Function GetDBPath(Optional ByVal knownTable As String = vbNullString) As String

    On Error GoTo ErrorCode
    
    Dim vPathname As String
    
    If knownTable = vbNullString Then
        Dim singleTable As TableDef
        For Each singleTable In thisDatabase.TableDefs          'loop thru all tables
            If (singleTable.Attributes And dbSystemObject) = 0 Then 'skip system tables
                If Nz(singleTable.Connect) <> vbNullString Then 'if table is linked then
                    vPathname = singleTable.Connect          'fetch connection string
                    Exit For                         'short circuit
                End If
            End If
        Next singleTable
    Else
        vPathname = thisDatabase.TableDefs(knownTable).Connect
    End If
    
    ' remove everything before ';DATABASE='
    GetDBPath = Mid$(vPathname, InStrRev(vPathname, "=") + 1)
    
ErrorCode:
    If Err.Number > 0 Then
        MsgBox "ERROR: " & Err.Number & vbCrLf & Err.Description, vbCritical, "Unhandled exception"
    End If
End Function

Finally, we can use thisBackend in place of CurrentDB like so:

thisBackend.Execute "ALTER TABLE [" & strImportHoldingTable & "] DROP COLUMN [field name];"

Upvotes: 0

Kaw4Life
Kaw4Life

Reputation: 209

CurrentDb.Execute "ALTER TABLE [" & CurrentDb.TableDefs(strImportHoldingTable).Connect & "].[" & strImportHoldingTable & "] DROP COLUMN [field name];"

Upvotes: 1

Related Questions