Solved

Hide Nvigation Pane Part II

Posted on 2012-03-14
5
251 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
  • 4
5 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 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

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

26 Experts available now in Live!

Get 1:1 Help Now