By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,731 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,731 IT Pros & Developers. It's quick & easy.

Another post on Automating Linked Table Manager - sorry!

JodiPhillips
P: 26
Hi,

My first post! I've basically taught myself Access and the little I know about VBA through reading these forums and a couple of books.

I'm in the middle of a project at work - to put it mildly I've been thrown in at the deep end, in the past I've just done simple databases and that's been fine. Now, however I have to "roll out" various front ends to a single back end for initially 10 users (and the bosses have plans for this to go to all 100 users - I think this is a little ambitious for me and they may have to get a professional to do it). We don't have developers or systems admins that I can go to for help, I'm it basically. Each user has the host drive (where both the front end and a hidden back end lives) mapped to different drive letters (not sure if I can convince them to remap to a constant - but it may come down to me doing it, or if anyone knows of .bat that I can script, borrow, beg, pay a pittance for that can automagically shift an existing mapped drive to some other drive letter and map my shared drive to a constant letter?).

The training for our office is in a training db, and managers get a snapshot (query based for their staff) on a form (which serves as their front end). The manager's front end does not have every table that exists in the backend, only those needed for any form/query ie. just the participation table, course details table, and a few others for some other queries. (these tables are the same as those in the backend re structure and data and are linked on my drive).

One thread in particular I've been reading with interest lately http://www.thescripts.com/forum/thre...matically.html. I apologise for yet another post on this topic, but didn't want to hijack Bubbles thread.

My problem is similar to that in the above post - except I want the user to run an automated link table manager procedure after clicking a button.

At the moment I have a form with a macro attached to a custom command button. The macro is a TransferDatabase action. On the click event the macro runs BUT I get an error no matter what I do that says the object isnt found, or the file doesn't exist, or the path is wrong. (I've triple checked spellings, UNC's and am happy that there are no typo's).

My action arguments are:
Transfer Type: Link
Database Type: Microsoft Access
Database Name:\\JodiShare\Training\manager.mdb
Object Type: Table (left this as default it's ignored anyway with Transfer Type = Link
Source:\\Jodi\Training\Training_be.mdb
Destination:\\Jodi\Training\manager.mdb (same as Database name)

Can anyone see anything wrong with what I have?

Any help at all is really appreciated - even if its just pointing me to a book or reference that I can research for this question.

Cheers

Jodi
May 7 '07 #1
Share this Question
Share on Google+
7 Replies


JodiPhillips
P: 26
Oops forgot to say MS Access 2003. =(
May 7 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
This is a simple routine that will allow you to select the backend manually.

Expand|Select|Wrap|Line Numbers
  1. Dim td As DAO.TableDef
  2. Dim strFileName As String
  3. Dim dlgPick As FileDialog
  4.  
  5. Set dlgPick = Application.FileDialog(msoFileDialogFilePicker)
  6.  
  7. dlgPick.AllowMultiSelect = False
  8. dlgPick.Title = "Browse for the JobCardXP_be.mdb files please."
  9.  
  10. If dlgPick.Show = -1 Then
  11.     strFileName = dlgPick.SelectedItems(1)
  12. End If
  13.  
  14. For Each td In CurrentDb.TableDefs
  15.   If Len(td.Connect) > 0 Then
  16.      td.Connect = ";DATABASE=" & strFileName
  17.      td.RefreshLink
  18.   End If
  19. Next
  20.  
May 7 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
An this is a more complicated routine written by Dev Ashish.

Expand|Select|Wrap|Line Numbers
  1. ' This code was originally written by Dev Ashish.
  2. ' It is not to be altered or distributed,
  3. ' except as part of an application.
  4. ' You are free to use it in any application,
  5. ' provided the copyright notice is left unchanged.
  6. '
  7. ' Code Courtesy of
  8. ' Dev Ashish
  9. '
  10. Function fRefreshLinks() As Boolean
  11. Dim strMsg As String, collTbls As Collection
  12. Dim i As Integer, strDBPath As String, strTbl As String
  13. Dim dbCurr As DATABASE, dbLink As DATABASE
  14. Dim tdf As TableDef
  15. Dim tdfLocal As TableDef
  16. Dim varRet As Variant
  17. Dim strNewPath As String
  18.  
  19. Const cERR_USERCANCEL = vbObjectError + 1000
  20. Const cERR_NOREMOTETABLE = vbObjectError + 2000
  21.  
  22.     On Local Error GoTo fRefreshLinks_Err
  23.  
  24.     Set dbCurr = CurrentDb
  25.  
  26.     'First get all linked tables in a collection
  27.  
  28.     dbCurr.TableDefs.Refresh
  29.     For Each tdf In dbCurr.TableDefs
  30.         With tdf
  31.             If Len(.Connect) > 0 Then
  32.                     collTables.Add Item:=.Name & .Connect, Key:=.Name
  33.             End If
  34.         End With
  35.     Next
  36.  
  37.     Set tdf = Nothing
  38.  
  39.     strNewPath = "Data.mdb"
  40.     For i = collTbls.Count To 1 Step -1
  41.         strDBPath = Right((collTbls(i)), Len((collTbls(i))) - (InStr(1, (collTbls(i)), "DATABASE=") + 8))
  42.         strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  43.         varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
  44.  
  45.         strDBPath = strNewPath
  46.         Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
  47.  
  48.         'check to see if the table is present in dbLink
  49.         strTbl = Left$(collTbls(i), InStr(1, collTbls(i), ";") - 1)
  50.         If fIsRemoteTable(dbLink, strTbl) Then
  51.             'everything's ok, reconnect
  52.             Set tdfLocal = dbCurr.TableDefs(strTbl)
  53.             With tdfLocal
  54.                 .Connect = ";Database=" & strDBPath
  55.                 .RefreshLink
  56.                 collTbls.Remove (.Name)
  57.             End With
  58.         Else
  59.             Err.Raise cERR_NOREMOTETABLE
  60.         End If
  61.     Next
  62.  
  63.     fRefreshLinks = True
  64.     varRet = SysCmd(acSysCmdClearStatus)
  65.     MsgBox "All Access tables were successfully reconnected.", _
  66.             vbInformation + vbOKOnly, _
  67.             "Success"
  68.  
  69. fRefreshLinks_End:
  70.     Set collTbls = Nothing
  71.     Set tdfLocal = Nothing
  72.     Set dbLink = Nothing
  73.     Set dbCurr = Nothing
  74.     Exit Function
  75. fRefreshLinks_Err:
  76.     fRefreshLinks = False
  77.     Select Case Err
  78.         Case 3059:
  79.  
  80.         Case cERR_USERCANCEL:
  81.             MsgBox "No Database was specified, couldn't link tables.", _
  82.                     vbCritical + vbOKOnly, _
  83.                     "Error in refreshing links."
  84.             Resume fRefreshLinks_End
  85.         Case cERR_NOREMOTETABLE:
  86.             MsgBox "Table '" & strTbl & "' was not found in the database" & _
  87.                     vbCrLf & dbLink.Name & ". Couldn't refresh links", _
  88.                     vbCritical + vbOKOnly, _
  89.                     "Error in refreshing links."
  90.             Resume fRefreshLinks_End
  91.         Case Else:
  92.             strMsg = "Error Information..." & vbCrLf & vbCrLf
  93.             strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
  94.             strMsg = strMsg & "Description: " & Err.Description & vbCrLf
  95.             strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
  96.             MsgBox strMsg, vbOKOnly + vbCritical, "Error"
  97.             Resume fRefreshLinks_End
  98.     End Select
  99. End Function
  100.  
  101. Function fIsRemoteTable(dbRemote As DATABASE, strTbl As String) As Boolean
  102. Dim tdf As TableDef
  103.     On Error Resume Next
  104.     Set tdf = dbRemote.TableDefs(strTbl)
  105.     fIsRemoteTable = (Err = 0)
  106.     Set tdf = Nothing
  107. End Function
  108.  
May 7 '07 #4

JodiPhillips
P: 26
Wow! Thank you very, very much mmccarthy! I'll give both a go, though I think the first one should be sufficient. Will let you know how I go.

BTW was this code already on the board somewhere?

One day I hope to be able to write code like this =) .... one day.
May 8 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Wow! Thank you very, very much mmccarthy! I'll give both a go, though I think the first one should be sufficient. Will let you know how I go.

BTW was this code already on the board somewhere?

One day I hope to be able to write code like this =) .... one day.
You're welcome.

This code is probably here somewhere. Unfortunately, the forum specific search is not working at the moment. Hopefully it will be fixed by the end of the week.
May 8 '07 #6

JodiPhillips
P: 26
Quick update:

mmccarthy, I went with the first (smaller) code and everyone is happy with that!! Though I didn't end up using Dev Ashish's script I am working my way through it line by line and learning a LOT!

Thanks again.

Jodi
May 23 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Quick update:

mmccarthy, I went with the first (smaller) code and everyone is happy with that!! Though I didn't end up using Dev Ashish's script I am working my way through it line by line and learning a LOT!

Thanks again.

Jodi
Glad to hear it.

Mary :)
May 23 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.