Solved

how to prevent viewing of Nav Window  when using MDE file

Posted on 2012-04-11
13
265 Views
Last Modified: 2012-04-18
I am generating a MDE file type of my development versions.  I have the Nav window turned off, however, I do have a few  linked tables that are refresh on demand from the user.  the Nav window becomes accessible when these l inked tables are being refreshed.  How do I prevent this?

thanks,
Karen
0
Comment
Question by:Karen Schaefer
  • 7
  • 5
13 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 37833903
You cannot prevent that behavior.
You can refresh the linked tables in code, and with some API code, the application can detect the workstation name and the login name of the user.
I have an app that will relink tables based on the pathname where the app is located, too.

If you can keep the user out of the loop or do so without needing the linked table manager, the Nav Pain won't open--but if the user has to do it from the linked table manager, you are out of luck.
The behavior is by MS design and cannot be over-ridden.
0
 

Author Comment

by:Karen Schaefer
ID: 37833904
NavPane
I am using 2007 here is a picture of my current settings, prior to creating the MDE file.

K
0
 

Author Comment

by:Karen Schaefer
ID: 37833914
I am using code to refresh the linked tables, is there code to prevent the Nav pane from being displayed - or hiding it again after the tables are refreshed?

K
0
 

Author Comment

by:Karen Schaefer
ID: 37833915
"You can refresh the linked tables in code, and with some API code, the application can detect the workstation name and the login name of the user.
I have an app that will relink tables based on the pathname where the app is located, too."

I believe we are using the same code.

K
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37833952
:)
You may have gotten it from me!


    If Application.Version > 11 Then
        CurrentDb.Properties("StartUpShowDBWindow").Value = False
    Else
        CurrentDb.Properties("StartUpShowDBWindow").Value = True
    End If

Should hide it off the beginning

DoCmd.RunCommand acCmdWindowHide
should hide it at anytime

Good links to creating a locked-down Access Ribbon app are here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27394605.html
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37833977
Yes, you can prevent that behavior.  But you will have to replace all calls to the TransferDatabase or TransferSpreadsheet method with code that uses the TableDef object instead of those two methods.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Karen Schaefer
ID: 37834714
Here is my current transferdatabase code - How would you recommend I change the code?

Public Function RecreateTable(ByVal stable As String) As Boolean

   On Error GoTo RecreateTable_Error

    RecreateTable = False
        If stable = "TA_FTIR" Or stable = "TA_MeasFltr" Then
            If IsTableExist(stable) = False Then
                GoTo ResumeNext
            Else
                DoCmd.DeleteObject acTable, stable
            End If
            If Err.Number <> 0 Then
                If Err.Number <> 7874 Then
                      MsgBox "Error deleting old table" & vbCrLf & Err.Description
                      Exit Function
                 End If
            End If
            Err.Clear
ResumeNext:
            DoCmd.TransferDatabase acLink, "Microsoft Access", gAPFilePath, acTable, stable, stable
            If Err.Number <> 0 Then
                MsgBox Err.Description
            Else
                RecreateTable = True
            End If
'        ElseIf stable = "tblProjectTask" Then
'            DoCmd.TransferDatabase acLink, "Microsoft Access", gAPFilePath, acTable, stable, stable
'            RecreateTable = True
'            Exit Function
        End If
   On Error GoTo 0
   Exit Function
RecreateTable_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RecreateTable of Module Functions"

End Function

Open in new window


Public Function Install_RefreshLinks(ByVal sDB As String, ByVal sPassword As _
    String, ByVal bCreateLinks As Boolean) As Boolean
    
    Dim dbData As Database
    Dim tdData As TableDef
    'Dim sTmp As String
    'Dim i As Integer
    Dim bLoop As Boolean
    Dim iAbortLoop As Integer
    Dim bDone As Boolean
    Dim iCnt As Integer
 
 
    On Error GoTo ins_err
    
    Install_RefreshLinks = False
    
    Err.Clear
    iAbortLoop = 0
    
    If bCreateLinks = False Then
        Set dbData = CurrentDb
        bLoop = True
        iCnt = 0
        While bLoop = True
            bDone = False
            For Each tdData In dbData.TableDefs
                If tdData.Attributes = dbAttachedTable Then
                      dbData.TableDefs.Delete tdData.Name
                      iCnt = iCnt + 1
                    bDone = True
                End If
            Next tdData
            iAbortLoop = iAbortLoop + 1
            If bDone = False Or iAbortLoop = 500 Then
                bLoop = False
                MsgBox iCnt & " linked tables were removed.", vbInformation, APP_TITLE
            End If
        Wend
        'Now recreate links
        'If Err.Number = 0 Then bCreateLinks = True
    End If
 
    If bCreateLinks = True Then
        iCnt = 0
        If sPassword <> "" Then
            Set dbData = DBEngine.Workspaces(0).OpenDatabase(sDB, False, False, ";PWD=" & sPassword)
        Else
            Set dbData = DBEngine.Workspaces(0).OpenDatabase(sDB)
        End If
    
        For Each tdData In dbData.TableDefs
            If tdData.Attributes = 0 Then
                If tdData.Name <> "RPT_Temp" And tdData.Name <> "MainMenu" Then
                    'Cater for temp tables used by Dev but not for release
                    If Left$(tdData.Name, 3) <> "xx_" Then
                        DoCmd.TransferDatabase acLink, "Microsoft Access", sDB, acTable, tdData.Name, tdData.Name, False
                        iCnt = iCnt + 1
                    End If
                End If
            End If
        Next
    
        MsgBox iCnt & " linked tables were created.", vbInformation, APP_TITLE
    End If

    fCaller.stbEMEA.Panels(1).Text = ""
    Install_RefreshLinks = True
    GoTo ins_ok
    
ins_err:
    MsgBox "Error " & Err.Number & " trapped. " & vbCrLf & Err.Description, vbCritical, APP_TITLE

ins_ok:
    Set dbData = Nothing
End Function

Open in new window

0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 37843022
Found solution on Access.programmers.ro.uk forum  

create a shortcut to run the app and use the /Runtime switch which should have the same effect.

Thanks for the input
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37843084
:)

That was part and parcel of the discussion here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27394605.html

@fyed suggested it was this code that caused the problem
DoCmd.TransferDatabase acLink, "Microsoft Access", gAPFilePath, acTable, stable, stable

and if you could rework it to something like

        For Each tdData In dbData.TableDefs
            If tdData.Attributes = 0 Then
                If tdData.Name <> "RPT_Temp" And tdData.Name <> "MainMenu" Then
                    'Cater for temp tables used by Dev but not for release
                    If Left$(tdData.Name, 3) <> "xx_" Then
                        TD.Connect = "the correct string for linking to that particular table"
                        iCnt = iCnt + 1
                    End If
                End If
            End If
        Next

you could be around the corner on your problem, too
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37843243
This will refresh linked tables to an Access backend

Function RefreshConnectString()
'On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "DATABASE=M:\Dev") 'replace m:\Dev with whatever test makes sense for you
        If intSubStringLoc > 0 Then
            'MsgBox TD.Connect
            TD.Connect = ";Database=M:\dev\cleandata.mdb" 'replace M:\dev\cleandata.mdb with whatever test makes sense for you
            'note that the string is very picky about spaces Database = M:\...doesn't work
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next
End Function
0
 

Author Comment

by:Karen Schaefer
ID: 37843356
the problem is that I am not refreshing all linked tables just a couple based on the selection of the user from a drop down.  Thanks for all you comments.

Karen
0
 
LVL 26

Expert Comment

by:Nick67
ID: 37843632
The guts of it are still the same then
You'd be looking in the TableDefs collection for the table that matched what was selected in the dropdown and then executing
            TD.Connect = ";Database=M:\dev\cleandata.mdb" 'replace M:\dev\cleandata.mdb with whatever test makes sense for you
            'note that the string is very picky about spaces Database = M:\...doesn't work
            TD.RefreshLink
based on the results.

The principal remains the same.  Construct a test.  Loop through the TableDefs collection looking for a match.  Alter the Connect string as necessary.  Refresh the link.

The /runtime switch is okay BUT:

1. will the users alter their shortcut?
2. will they open the app through the filesystem?
3. will they open the app through Access's MRU list?
4. will they pin the app to the Windows 7 taskbar?
5. will they pin the app to the Windows 7 Start Menu?

All of these will not activate the /runtime switch of the shortcut, so it may not be an ideal fix in the long run
0
 

Author Closing Comment

by:Karen Schaefer
ID: 37859692
found solution elsewhere
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now