Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

CreateLinkedTable Script - Microsoft Access

RSS
Modified on Tue, Sep 27, 2011, 9:53 AM by Administrator Categorized as Microsoft Office
The following script will programmatically create a linked table in Microsoft Access. No check is made to see if the table already exists.

'==================================================================================================
Sub CreateLinkedTable(serverName As String, dbName As String, remoteTableName As String, _
trustedConnection As Boolean, userName As String, password As String)

    Dim localTableName As String
    Dim tdf As TableDef
    Dim fld As Field

    localTableName = Replace$(remoteTableName, ".", "_")
    Set tdf = CurrentDb.CreateTableDef(localTableName)
    tdf.Connect = SqlServerConnection(serverName, dbName, remoteTableName, trustedConnection, _
        userName, password)
    tdf.SourceTableName = remoteTableName
    CurrentDb.TableDefs.append tdf
    CurrentDb.TableDefs.Refresh

End Sub
'==================================================================================================
Private Function SqlServerConnection(serverName As String, dbName As String, targetTable As _
String, Optional trustedConnection As Boolean = True, Optional userName As String = "", Optional _
password As String = "") As String

    Dim result As String
    result = "ODBC;DRIVER=SQL Server;SERVER={server};DATABASE={db};"
    result = result & "PACKET SIZE=4096;PERSIST SECURITY INFO=False"
    result = result & ";TABLE={table}"
    
    If trustedConnection Then
        result = result & "Trusted_Connection=Yes;"
    Else
        result = result & "UID={username};PWD={password}"
    End If
    
    result = Replace$(result, "{server}", serverName)
    result = Replace$(result, "{db}", dbName)
    result = Replace$(result, "{table}", targetTable)
    result = Replace$(result, "{username}", userName)
    result = Replace$(result, "{password}", password)

    SqlServerConnection = result

End Function

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.