AFTableConnections Class

Public NotInheritable Class AFTableConnections
	Inherits AFNamedCollection(Of AFTableConnection)

Dim instance As AFTableConnections

public ref class AFTableConnections sealed : public AFNamedCollection<AFTableConnection^>

[<SealedAttribute>]
type AFTableConnections =  
    class
        inherit AFNamedCollection<AFTableConnection>
    end

' This example demonstrates how to link an existing external table to an AFTable using an ADODB.Recordset
' AFTableConnection configured to use an ADODB.Recordset and three .NET data adapters (ODBC,
' OLEDB, and SQL). The link to the SQL Server table is configured to use parameters.
' Note: This sample uses a sample MS Access 2010 database that can be downloaded from Microsoft at:
' http://office.microsoft.com/en-us/templates/northwind-sales-web-database-TC101114818.aspx

' This example will work with 64 bit Microsoft Office 2010 and SQL Server 2012 - some 
' modifications to connection strings will be necessary to work with other versions
' of Microsoft Office and Microsoft SQL Server or on 32 bit systems.

' Get the Database 
Dim myPISystems As New PISystems
Dim myDB As AFDatabase = myPISystems.DefaultPISystem.Databases.DefaultDatabase

' Create the parameters used in the table query
Dim myParameters As New Dictionary(Of String, Object)()
myParameters.Add("@City", "London")

' Create the password used when connecting to the external table
Dim password As System.Security.SecureString = New System.Security.SecureString
For Each c As Char In SqlPassword
    password.AppendChar(c)
Next

password.MakeReadOnly()

' ****************************************************
' Create a Table Connection using an OLEDB data adapter
' ****************************************************
Dim myConnection As AFTableConnection = myDB.TableConnections.Add("Employees OLEDB")
myConnection.Description = "This is my employee table connection using OLEDB data adapter."
myConnection.ImpersonateUser = True
myConnection.ExternalConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Northwind.accdb"
myConnection.ExternalType = GetType(OleDbDataAdapter).FullName
myConnection.CheckIn()

' Create a Table and Link to the External Table using the OLEDB data adapter Table Connection
Dim myTable As AFTable = myDB.Tables.Add("Employees OLEDB")
myTable.Description = "This is my employee table using OLEDB data adapter."
myTable.CacheInterval = TimeSpan.FromMinutes(30)
myTable.LinkExternal(myConnection, "SELECT * FROM Employees", Nothing)
myTable.CheckIn()

' ***************************************************
' Create a Table Connection from Excel using an ODBC data adapter
' ***************************************************
myConnection = myDB.TableConnections.Add("Employees ODBC")
myConnection.Description = "This is my employee table connection using ODBC data adapter."
myConnection.ImpersonateUser = True
myConnection.ExternalConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\Employees.xlsx"
myConnection.ExternalType = GetType(OdbcDataAdapter).FullName

' Create a Table and Link to the External Table using the ODBC data adapter Table Connection
myTable = myDB.Tables.Add("Employees ODBC")
myTable.Description = "This is my employee table from Excel using ODBC data adapter."
myTable.CacheInterval = TimeSpan.FromMinutes(30)
myTable.LinkExternal(myConnection, "SELECT * FROM A1:F10", Nothing)
myTable.CheckIn()

' *************************************************
' Create a Table Connection using a SQL data adapter
' *************************************************
myConnection = myDB.TableConnections.Add("Employees SQL")
myConnection.Description = "This is my employee table connection using SQL data adapter."
myConnection.ExternalConnection = $"Server={SqlServerMachineName};Database=Northwind;User Id={SqlUserId};Password=<PASSWORD>;Trusted_Connection=False;"
myConnection.ExternalType = GetType(SqlDataAdapter).FullName
myConnection.SecurePassword = password

' Create a Table and Link to the External Table using the SQL data adapter Table Connection
myTable = myDB.Tables.Add("Employees SQL")
myTable.Description = "This is my employee table using SQL data adapter."
myTable.CacheInterval = TimeSpan.FromMinutes(30)
myTable.LinkExternal(myConnection, "SELECT * FROM Employees WHERE [City] = @City", myParameters)
myTable.CheckIn()

' Display the DataTable using Parameters
myParameters = New Dictionary(Of String, Object)()
myParameters.Add("@City", "Seattle")
Dim table As DataTable = myTable.GetTableWithParameters(myParameters)
Dim line As New StringBuilder()
For Each col As DataColumn In table.Columns
    line.AppendFormat("{0}, ", col.ColumnName)
Next
Console.WriteLine(line.ToString())
line.Clear()
For Each row As DataRow In table.Rows
    For Each col As DataColumn In table.Columns
        line.AppendFormat("{0}, ", row(col.ColumnName))
    Next
    Console.WriteLine(line.ToString())
    line.Clear()
Next

' The Table can be Refreshed Manually using AFTable.Refresh
myTable.Refresh()

No code example is currently available or this language may not be supported.

No code example is currently available or this language may not be supported.