Refresh table links

[ScrmingWhisprs]

I found this code online from http://www.mvps.org/access/tables/tbl0009.htm. However, I'm not exactly sure how to implement it in my Front End. I copied the code into a new module, then put this in the OnLoad Event of my startup form:

Call fRefreshLinks()

But for some reason, the dialog box asking if I want to refresh the links everytime I open the database, even when they didn't need refreshing.

When I tested it by moving my backend, an error came up saying Sub or Function not defined which then opened the VBA window highlighting this part of one of the Functions (highlighted in red):

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

    [COLOR="Red"]strFilter = ahtAddFilterItem(strFilter, _[/COLOR]
                    "Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
                    "*.mdb; *.mda; *.mde; *.mdw")
    strFilter = ahtAddFilterItem(strFilter, _
                    "All Files (*.*)", _
                    "*.*")

    fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
                                OpenFile:=True, _
                                DialogTitle:=strIn, _
                                Flags:=ahtOFN_HIDEREADONLY)
End Function

So I'm guessing there's more that I have to do than just putting the Call fRefreshlinks() in the OnOpen even of the startup form. I just need a little guidance.

Thanks so much!!
SW

 

[wazz]

maybe this will help. test the link first. you might need to loop through the tables and/or do other tests as well.

Public Function CheckLinks() As Boolean
' Check links to the Northwind database; returns True if links are OK.
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    
    Set dbs = CurrentDb

    ' Open linked table to see if connection information is correct.
    On Error Resume Next
    Set rst = dbs.OpenRecordset("Products")     

    ' If there's no error, return True.
    If Err = 0 Then
        CheckLinks = True
    Else
        CheckLinks = False
    End If
    
    Debug.Print CheckLinks
    
End Function

 

[ScrmingWhisprs]

I don't have my database with me right now to test it, but just looking at this code, I have a couple of questions...

1.) Where would I Call the function? The OnOpen Event of my startup form?

2.) Do I have to replace "Products" with my table names? (I have a lot of tables) lol

Set rst = dbs.OpenRecordset("[COLOR="Red"]Products[/COLOR]")

Thanks
SW

 

[wazz]

hi i was just looking at dev's code. it looks like you probably forgot to include some code that he has a link to near the top of the page. in the intro he mentions the GetOpenFileName function - you have to go to another page to get that.

...um, i just looked at it and it's pretty massive code. i think it might be a bit old too (because they mention access 97). i *think* you might be able to substitute it by using a filedialog object (look up filedialog) which is newer and pretty straightforward.

test the code you have again (with the be in a different place) with a filedialog (to select the new location of your be when you move it).

the simple answer to your first question is: the first thing dev's code does is ask if you want to reconnect. then it asks if you want to specify a different path.

i guess the thing is, if you don't check on start up the app won't know if you've moved the back end. if you don't want to check i guess you don't need the code at all.

oh, and with the other snippet i posted, yes you'd have to change the table name which is why i mentioned looping through all the tables (where you'd take the name of the table you're on in the loop and put it where "products" is now). i don't know how efficient that code is, opening a recordset every time. i just happened to come across it. could be a good quick and dirty way of testing if the be is still in the same place ... i suppose if all the tables are in one place, if one test fails then they would all fail and you would know right away. maybe you only have to test one table and if it works you're good to go.

 

[ScrmingWhisprs]

Wazz--

I just wanted to let you know I got it all working! Your Checklinks function did the trick. This is how I solved it:

I created a new startup form and placed this in the OnLoad event:

Private Sub Form_Load()

    If CheckLinks() = True Then
        DoCmd.OpenForm "frmLogin"
        DoCmd.Close acForm, Me.Name
    Else
        MsgBox "Could not find database tables." & vbCrLf & _
        "Please select the database to relink tables." _
        , vbExclamation + vbOKOnly _
        , "Connection Error"
        Call fRefreshLinks
    End If
    
End Sub

So if the function returns true the table links are all set, and the database opens as normal. If it doesn't, I call the fRefreshLinks function.

I added the GetFileName module with no problem, and it works great.

Thank you for your guidance! This was a very important piece of my project as I will be using Access 2007 runtime for the frontends on some computers, so I needed an easy way to locate the backend.

 

[wazz]

thanks for posting back and also for letting me know the getopenfilename module works with a newer access version. are just checking one table with checklinks or looping through all?

 

[ScrmingWhisprs]

I am just checking one table, tblLogins, which is needed to actually get into the database. I'm not exactly sure how to loop through the tables.