update multiple access front ends when sql tables are changed?

[Lost500]

Hey everybody,
My question is that when i make changes/addtions/deletions to sql tables how can i update multiple access db's automatically?

I have a sql database with many access frontends. the access db's have odbc linked tables to the sql db. it gets pretty bad having to go to everyone's desk each time i make a change.

Thanks for the help in advance!

 

[dhookom]

What type of changes are you making? If the changes are to records there should be no issues. If you are changing table structures, you will need to re-link the tables. This can be done with code. I believe Doug Steele has code for re-linking Using DSN-Less Connections.

You may need to keep a local table with a version number and a SQL server table with a version number. If they don't match, run the code to re-link. Then update the local version number.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks duane
I am talking about updating changes to table stuctures and such. the idea of having a version number is great the article you pointed me too i think goes way too indepth then what i am looking for or maybe not... so is there a shorter way of, after checking the version number and it not matching, to just delete the tables out of the database and re-link them?

Thanks for the advice!

 

[dhookom]

You may be able to get by with code that simply refreshes the existing link. You would need to change the strConnect to meet your needs.

Function RelinkODBC()
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Dim strConnect As String
    strConnect = "ODBC;Driver={SQL Server};Server=SERVERNAME;Database=DBNAME;Uid=USERNAME;Pwd=PWD"
    Set db = CurrentDb
    For Each td In db.TableDefs
        If Left(td.Connect, 4) = "ODBC" Then
            Debug.Print td.Name
            td.Connect = strConnect
            td.Attributes = DB_ATTACHSAVEPWD
            td.RefreshLink
        End If
    Next
    For Each qd In db.QueryDefs
        If Left(qd.Connect, 4) = "ODBC" Then
            Debug.Print qd.Name
            qd.Connect = strConnect
        End If
    Next
    Set td = Nothing
    Set qd = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP