ACCESS FEED: Re: [MS_AccessPros] How to change a backend table from the frontend

Selasa, 14 Juli 2015

Re: [MS_AccessPros] How to change a backend table from the frontend

ps, Onno,

you also need to appAccess.Quit before you release the object ~

if MsgBox( " Close external database?", vbyesno, "Close Database?") = vbno then
stop
end if
appAccess.Quit
Set appAccess = Nothing

it would not be a good idea, however, not to close the external database ... which is why I put a STOP in so you can do whatever than set the code to quit and release to run

~ crystal

On 7/14/2015 7:43 AM, crystal 8 wrote:

Hi Onno,

thanks for sharing. Never have anything open in the front-end that uses the table you are modifying in the back-end. I normally close everything in the FE before modifying structure in the back-end.

That said, if you want to take it a step further, you could modify the code here to pick the table you want.

Data Dictionary, Display Control
by Crystal on RogersAccessLibrary
http://www.rogersaccesslibrary.com/forum/data-dictionary-display-control_topic610.html

Warm Regards,
crystal

~ be awesome today ~

On 7/14/2015 6:36 AM, onno.knol@pbl.nl [MS_Access_Professionals] wrote:

I figured it out, this is how it works for me. I call this function wit as parameter the linked tablename:

Public Sub EditExternalTable(tablename As String)
    Dim BeName As String
    Dim appAccess As Object
    Dim db As Database
    Dim RemoteTabName As String

    Set appAccess = CreateObject("Access.Application")
    BeName = BackendNaam(tablename)
    RemoteTabName = Nz(DLookup("foreignname", "msysobjects", "name='" & tablename & "'"))

    If BeName <> "None" Then
        appAccess.OpenCurrentDatabase (BeName)
        appAccess.DoCmd.OpenTable RemoteTabName, acDesign
        appAccess.Visible = True
        MsgBox " Close external database?"
    End If
    Set appAccess = Nothing
End Sub

Function BackendNaam(tabel As String) As String
    Dim conm As String
    On Error GoTo no_Table
    conm = CurrentDb.TableDefs(tabel).Connect
    If Len(conm) > 10 Then
        BackendNaam = Right(conm, Len(conm) - 10)
    Else
        BackendNaam = "None"
    End If
    Exit Function
no_Table:
    BackendNaam = "None"
End Function