Solved

Mass update Linked Tables in Access

Posted on 2014-11-12
7
513 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
  • 3
  • 3
7 Comments
 
LVL 24

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 49

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

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

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 49

Expert Comment

by:Gustav Brock
ID: 40440050
You are welcome!

/gustav
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

832 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