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

actually, instead of Stop ... set the database object at the top of the module so it is available to all procedures behind the form. when you close the form, Quit and set db to nothing. You could also have a button to release the database.

~crystal

On 7/14/2015 7:49 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

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