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
Solved

Hide Nvigation Pane Part II

Posted on 2012-03-14
5
263 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

792 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