Solved

Mass update Linked Tables in Access

Posted on 2014-11-12
7
472 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now