Solved

how to prevent viewing of Nav Window  when using MDE file

Posted on 2012-04-11
13
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 48

Expert Comment

by:Dale Fye
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
 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

624 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