Solved

Mass update Linked Tables in Access

Posted on 2014-11-12
7
542 Views
Last Modified: 2014-11-13
I have an Access database that I use to convert data from one ERP to another ERP system. The databases are Pervasive to Postgresql.

I link tables from both databases using ODBC and then have append queries to migrate the data.

My problem is that I take this database to different servers and it is very cumbersome to update the links individually in the linked table manager. How can I mass update the links? In total I link to about 40 tables.
0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40439145
One of the easiest way to do this is by using DSNs for the ODBC connections. I guess you are using DSN-less option for your linked tables. Create two DSNs: pervasiveERP and postgresERP and use them for the linked tables. If you stick to the same DSN names on different machines the linked tables will work
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40439509
You can use a function like this:
Public Function AttachSqlServer( _
    Optional ByVal Hostname As String, _
    Optional ByVal Database As String, _
    Optional ByVal Username As String, _
    Optional ByVal Password As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server.
' 2014-08-09. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"
    Const cstrConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server;" & _
        "Description=Cactus TimeSag og Finans;" & _
        "APP=Microsoft® Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim strConnect      As String
    Dim strName         As String
    
    On Error GoTo Err_AttachSqlServer
    
    Set dbs = CurrentDb
    strConnect = cstrConnect
    strConnect = Replace(strConnect, "{0}", Hostname)
    strConnect = Replace(strConnect, "{1}", Database)
    strConnect = Replace(strConnect, "{2}", Username)
    strConnect = Replace(strConnect, "{3}", Password)
    
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                tdf.RefreshLink
                Debug.Print tdf.Name, tdf.SourceTableName, tdf.Connect
            End If
        End If
    Next
    
    AttachSqlServer = True
    
Exit_AttachSqlServer:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
    
Err_AttachSqlServer:
    FejlBox (vbCritical)
    Resume Exit_AttachSqlServer
    
End Function

Open in new window

You have to modify it a little to separate the tables from the two servers in two groups.

/gustav
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40439675
@chaau
Your suggestion works for the pervasive database but not for the postgress. For the postgres database the string also includes the database name.

Like so DSN=erp;database=abc. The database name will always be the company name so that will not work.

@Gustav Brock
I'm going to try yours
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Gerhardpet
ID: 40439696
The Postgres database connection string looks like this
database connection string
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 40439713
The only thing you may need to change is SERVER=localhost where localhost should be replaced with the hostname or IP address of the other server.

The generic string is:
Driver={PostgreSQL};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

/gustav
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 40440021
Got it! That works...Thanks for your help @gustav
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40440050
You are welcome!

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question