Hide Nvigation Pane Part II

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
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pteranodon72Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
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
dawber39Database Analyst / Application DeveloperAuthor Commented:
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
dawber39Database Analyst / Application DeveloperAuthor Commented:
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
dawber39Database Analyst / Application DeveloperAuthor Commented:
Big help putting this in sub routine - thank you much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.