Earlier in this chapter, linking to tables from external data sources such as FoxPro or Microsoft SQL Server to a Microsoft Jet database file was discussed. To link to external tables, you specify the original source of the table in the Connect property of a new TableDef object and then append the TableDef object to the TableDefs collection of the database.
In addition to linking to tables in external databases, you can also link to views in SQL data sources, such as Microsoft SQL Server or Oracle. A view is a named SQL SELECT statement executed on the server that joins one or more tables and selects one or more fields.
Normally, a linked view is not updatable even if it’s based on an updatable table. For a linked table to be updatable, it must contain a primary key or a unique index.
When you link to a view rather than to a table, there is no index available and Microsoft Jet doesn’t have enough information to treat the view as updatable. To make the linked view updatable, you must create a pseudo index for the TableDef object of the linked view. The pseudo index tells Microsoft Jet which of the fields in the linked view comprise the primary key or unique index in the table underlying the view. The index exists within the Microsoft Jet database only. Nothing is created on the server. In previous versions of DAO, a pseudo index was referred to as an index specification.
The following code creates a view named USCustomers in a SQL Server database. In this case, the SQL Server database is the Microsoft Access Northwind database that’s been upsized to SQL Server by using the Microsoft Access 97 Upsizing Tools:
Dim wrk As Workspace, con As Connection
Dim strSQL As String, strConnect As String
' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
' Initialize connection string.
strConnect = "ODBC;DATABASE=NorthwindSQL;UID=sa;" _
& "DSN=NorthwindSQLDSN"
' Open connection.
Set con = wrk.OpenConnection("NorthwindConnection", , , strConnect)
strSQL = "CREATE VIEW USCustomers AS " _
& "(SELECT * FROM Customers WHERE Country = 'USA')"
' Execute SQL statement.
con.Execute strSQL
Notes
The following code links to a view on the server and then creates an index on the view to make it updatable. In this example, strDbPath is the path to the database:
Dim dbs As Database
Dim tdf As TableDef
Set dbs = OpenDatabase(strDBPath)
' Create linked table based on SQL view.
Set tdf = dbs.CreateTableDef("USCustomersLinked")
' Specify SQL view as source table.
tdf.SourceTableName = "USCustomers"
' Connect to SQL database.
tdf.Connect = "ODBC;DATABASE=NorthwindSQL;UID=sa;" _
& "DSN=NorthwindSQLDSN"
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
' Create primary key on linked table.
dbs.Execute "CREATE INDEX PrimaryKey " & _
"ON USCustomersLinked (CustomerID) WITH PRIMARY"
The newly linked view, USCustomersLinked, can now be updated as though it were a regular table.
See Also For more information about the updatability of external data sources, see Chapter 8, “Accessing External Data.” For more information about working with Microsoft SQL Server databases, see Chapter 9, “Developing Client/Server Applications.”