Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Hide Nvigation Pane Part II

Posted on 2012-03-14
5
Medium Priority
?
269 Views
Last Modified: 2012-06-22
In an earlier question I asked about hiding the Navigation Pane - and I used a sub-routing provided to me by fyed. He also provided some code which suggested using tabledef object instead of the transfer database method. My question pertains to how I would actually use this code (immediately below - and incorporate it into the code below it. I am still relatively new in VBA - and any help is appreciated.



     If TableExists(TableName) Then
           Set tdfDest = dbDest.TableDefs(Tablename)
           tdfDest.Connect = ";DATABASE=" & Backend
           tdfDest.RefreshLink
      Else
           Set tdfDest = dbDest.CreateTableDef(TableName)
           tdfDest.Connect = ";DATABASE=" & Backend
           tdfDest.SourceTableName = TableName
           dbDest.TableDefs.Append tdfDest
      End If

********************************************************************


Function SetArchiveMode(SetActive)
' Called from Main Menu
Const PROCNAME = "SetArchiveMode"
Dim db As Database, rs As Recordset, Msg As String
On Error GoTo SetArchiveModeErr
   Msg = ""
   Msg = Msg & "Warning: Make sure all Tables, Queries, Forms and Reports are closed" & vbCrLf
   Msg = Msg & "before changing Archive mode." & vbCrLf & vbCrLf
   Msg = Msg & "Continue?"
   If Not Confirm(Msg) Then Exit Function

   DoCmd.Hourglass True
   If Not FormIsOpen("Settings") Then
      DoCmd.OpenForm "Settings", acNormal, , , , acHidden
     
   End If
   Set db = CurrentDb
   Set rs = db.OpenRecordset(ATTACHTABLENAME)
   rs.MoveFirst
   Do Until rs.EOF
     'Debug.Print Rs.TableName
      db.TableDefs.Delete rs!TableName
      If TableExists("Arc" & rs!TableName) Then
         db.TableDefs.Delete "Arc" & rs!TableName
      End If
      If SetActive Then  ' Set Archive mode
         DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, rs!TableName
       
      Else               'Set Normal mode
         DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!TblDbName, acTable, rs!TableName, rs!TableName
         DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, "Arc" & rs!TableName
      End If
      rs.MoveNext
   Loop
   Forms!Settings!ArchiveActive = SetActive
   db.TableDefs.Refresh
   DoEvents
   Forms("_Menu").Recalc
SetArchiveModeExit:
   DoCmd.Hourglass False
   rs.Close
   db.Close
  'Debug.Print "Exit function."
   Exit Function
SetArchiveModeErr:
   If AppError(Err.Number, Err.Description, Err.Source, PROCNAME) Then
      Resume Next
   Else
      Resume SetArchiveModeExit
   End If

End Function
0
Comment
Question by:dawber39
[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
  • 4
5 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 2000 total points
ID: 37725557
dawber39,

I changed the code a little and wrapped it in a Sub:
Sub LinkOrRelinkAccessTable(strBackend as String, strRemoteTableName As String, strLocalTableName As String)
Dim db As DAO.Database
Dim tdfLocal As DAO.TableDef

Set db = CurrentDb()

If TableExists(strLocalTableName) Then
    'point to existing tabledef
    Set tdfLocal = db.TableDefs(strLocalTableName)
    'change tabledef properties
    tdfLocal.Connect = ";DATABASE=" & strBackend
    tdfLocal.SourceTableName = strRemoteTableName
    'refresh the link so new properties are reflected
    tdfLocal.RefreshLink
Else
    'create a new tabledef object
    Set tdfLocal = db.CreateTableDef(strLocalTableName)
    'set tabledef properties
    tdfLocal.Connect = ";DATABASE=" & strBackend
    tdfLocal.SourceTableName = strRemoteTableName
   'append tabledef to collection
    db.TableDefs.Append tdfLocal
End If
End Sub

Open in new window


Once this code is saved in a standard module, you can replace each of your DoCmd.TransferDatabase lines with LinkOrRelinkAccessTable calls, dropping the first, second, and fourth arguments. For example:

DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable,"Arc" & rs!TableName, rs!TableName

becomes:

LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName

where
-first parameter is the backend path
-second is the remote table name to be linked
-third is the name of the local link

Hope this helps,
pT72
0
 

Author Comment

by:dawber39
ID: 37726324
I am getting errors switching back and forth  I also get an error when opening the front end too.

Here is the way the lines are coded now
     
 If SetActive Then  ' Set Archive mode
         LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName
      Else               'Set Normal mode
         LinkOrRelinkAccessTable Forms!Settings!TblDbName, "Tbl" & rs!TableName, rs!TableName
         LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName

I noticed by the dates - that even though it says NORMAL mode - it is in ARCHIVE

Screen shots of the errors with coding (above) as it is now:
Opening-App.png
Switching-to-Archive.png
SwitchingBack.png
0
 

Author Comment

by:dawber39
ID: 37726434
I think the problem is in linking back to the original (Normal) side. Becasue after trying again - I was able to switch to archive easily - then I encountred error when switching back

Original line:

DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!TblDbName, acTable, rs!TableName, rs!TableName

which I changed to this:

 LinkOrRelinkAccessTable Forms!Settings!TblDbName, "Tbl" & rs!TableName, rs!TableName

and that is probably wrong. I appreciate your help with this
0
 

Author Comment

by:dawber39
ID: 37727409
With your help - I figured out the rest- and I thank you for your direction.

I had to change this line -   LinkOrRelinkAccessTable Forms!Settings!TblDbName, "Tbl" & rs!TableName, rs!TableName


to this:   LinkOrRelinkAccessTable Forms!Settings!TblDbName, rs!TableName, rs!TableName
 and remove the third line - and it worked fine.
0
 

Author Closing Comment

by:dawber39
ID: 37727411
Big help putting this in sub routine - thank you much
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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