Results 1 to 7 of 7
  1. #1
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295

    How to update all links for all link tables in all databases in all subfolders

    Hi, all



    Recently our server name changed, all links for tables are broken, there hundreds of database in hundreds folders.

    How can I update all links for all link tables in all databases in all subfolders?

    I know how to use VBA, but I only worked on one database (with link tables to other database), I never open another database in VBA code and change anythings, I don't know how to scan subfolders for all .mdb files either.

    Would somebody give me a flow chart(of course the more details the better) with key function names to accomplish this?

    Any help is very appreciated.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    alright weekend...you finally asked a question. Usually you're the contributor, no?

    At any rate though, what exactly is the problem with the link? You say server, but do you really mean that? what links are broken? the paths to the backends? You also say that there are hundreds of dbs in all of your folders (server folders?) ... what are all of these database used for? are they all Access dbs used by your one company?

    I'd be interested to find out ... but back on topic, is the primary issue that the pointer for the table links now points to the wrong backend(s) for all front ends?

  3. #3
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi weekend,

    maybe the following bits of code might help:

    first snippet shows how to use the FilseSystemObject to read the filenames in a folder.

    ====================================
    Public Sub ReadFolderFiles(strFolder As String)
    On Error GoTo Err_ReadFolderFiles

    Dim objFS As Object
    Dim fSuccess As Boolean
    Dim strFile As String
    Dim objFolder As Object
    Dim objFile As Object
    Dim objFiles As Object


    Set objFS = CreateObject("Scripting.FileSystemObject")
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    Set objFolder = objFS.GetFolder(strFolder)

    Set objFiles = objFolder.Files 'get the collection of files

    For Each objFile In objFiles
    Debug.Print objFile.Name
    Next

    Exit_ReadFolderFiles:
    Exit Sub

    Err_ReadFolderFiles:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ReadFolderFiles

    End Sub
    ========================================

    next piece of code I use in an app to relink the lookup tables. The info of how this relinking has to be done is stored in the table sysparameters. I won't explain here the whole application, but maybe this code can show you some directions.


    ==============================================
    Public Function RefreshLookupTables(strFile As String) As Integer
    On Error GoTo Err_RefreshLookupTables

    Dim cnn As ADODB.Connection
    Dim cnnRemote As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cat As New ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim intCount As Integer

    Set cnn = CurrentProject.Connection
    cnnRemote.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile
    cnnRemote.Open
    Set cat.ActiveConnection = cnnRemote
    rst.Open "select parTabel from tsysParameters where parType = 'LookupTable'", cnn, adOpenKeyset, adLockReadOnly
    With rst
    If Not (.BOF And .EOF) Then
    .MoveFirst
    While Not .EOF
    For Each tdf In cat.Tables
    If !parTabel = tdf.Name Then
    'Debug.Print "deleted: " & tdf.Name
    cat.Tables.Delete (tdf.Name)
    DoCmd.TransferDatabase acLink, "Microsoft Access", strFile, acTable, !parTabel, !parTabel
    intCount = intCount + 1
    End If
    Next tdf
    .MoveNext
    Wend
    End If
    .Close
    End With
    RefreshLookupTables = intCount

    Exit_RefreshLookupTables:
    Set cnn = Nothing
    Set cnnRemote = Nothing
    Set cat = Nothing
    Set rst = Nothing
    Set tdf = Nothing
    Exit Function

    Err_RefreshLookupTables:
    RefreshLookupTables = -1
    Resume Exit_RefreshLookupTables

    End Function
    ==================================

    be sure to have the "Microsoft ADO Ext. X.X for DDL and Security" reference checked before using ADOX.

    Hope this helped a little bit
    NG

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Hi Ajetrumpy,

    Usually when I encounter some questions, I try to figure out by myself or search from internet, and I can find answers for most of those questions. So, I merely have a chance to post a question in the forum.

    But this time, I was too lazy to figure it myself, and the forum is not that busy, so I post it in the forum, hoping someone can give me a solution which I can just copy and paste and then the problem solved.

    All my databases are stored in share driver, unfortunly, the links were using \\servername\folder\... format instead of F:\folder. When the server changed, all links were broken.

    My databases are not seperated into front end and back end, only some common thing stored in one database, and the monthly data in others. every month we create new databases while leaving the old ones stay. That's why there are hundreds of DBs. We need to check back previous years data sometimes. I need to fix all the links in those hundreds of DBs.

    By now, my most outstanding issue is if there is any way to update the databases inside a ZIP file without unzip them. ( I can unzip them by program, but have some problem to zip them back, sometime winzip aborts saying file size is too big when I replacing MDB files in the ZIP file - only 1G after zip and about 10 G before zip. If I delete the zip file and create a new one, no problem at all. But I can not delete the zip file, because there are other files inside.) I must zip them back manually!



    Hi, Noellag:

    I am working on it using a way similiar with yours, hoping I can get it done soon. Thank you for your every effort.

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    weekend,

    If I was in your shoes I would seriously look at the opportunity you have to address upper management in your company about reducing the amount of memory that is being used on your server. One database per month is not really logical, IMO. But at any rate, I have never done what you need to do here because I've never had the need but it does sound like you're working diligently on a solution.

    Noella,

    I do have to say something here as well. I like a lot of your contributions as I think your expertise in Access is evident. Is there a specific reason why a lot of the code that you provide is not properly formatted so it is readable to a user? I like reading some of the code examples you post (like this one), but some of them seem to indicate that you only have 5-10 seconds to respond. Would it be possible for you to provide responses that are slightly more readable?

    Not criticising at all of course (obviously I can't spell too good!), but I think your contributions would be more valuable if they were a little more pleasing to the eye. (IMO).

    Good luck with it weekend.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Our work is to do some monitoring or reconciliation things, we keep snapshot of the data at the beginning of every month. and we need to keep the data as "evidence".

    That's why there are so many databases.

    I almost get this work done. The most time consuming part is ZIPPING and UNZIPPING. I did the zip work manually.

    Thank you for your advice.

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    As I said, I'm sure you can get it done. Congrats on the ability to stick with it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. One-way link between tables
    By Mikele di Sagitter in forum Database Design
    Replies: 1
    Last Post: 07-23-2010, 08:26 AM
  2. How do I link existing tables?
    By jsbdiver in forum Programming
    Replies: 0
    Last Post: 05-24-2010, 02:53 PM
  3. Replies: 1
    Last Post: 03-28-2010, 08:06 AM
  4. Update query for ID #s to link records
    By fspswen in forum Queries
    Replies: 0
    Last Post: 11-20-2009, 01:52 PM
  5. Best way to link tables together?
    By marleyuk in forum Access
    Replies: 1
    Last Post: 06-07-2009, 08:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums