Solved

Mass update Linked Tables in Access

Posted on 2014-11-12
7
604 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 51

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

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

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 51

Expert Comment

by:Gustav Brock
ID: 40440050
You are welcome!

/gustav
0

Featured Post

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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

628 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