Refresh Link to SQLServer View through Code

Ethan

New member
Local time
Yesterday, 21:02
Joined
Jun 13, 2003
Messages
7
Hay all, this is my first post so go easy on me.

I've done several searches and have answered most of my questions over the last week or so of previous threads on creating DSN's through code and linking new tables and refreshing existing tables. Thank you Travis, the code you posted on CheckingExistence/Creating/Deleting was very helpful.

By the way I'm using Access97 and SQL2kServer.

Ok now for my current problem. Some of the links that I am connecting to are Views on SQL2kServer, now if I manual create the link to the view it is updateable but when I refresh the links to the tables/views the tables are fine but the views are no longer updatable and I very much need one of them to be updateable.

Code:
    Dim tblArray(7) As String
    tblArray(1) = "Dish"
    tblArray(2) = "DishProcess"
    tblArray(3) = "tblPrmServ"
    tblArray(4) = "vwPrmServ"
    tblArray(5) = "vwPrmServList"
    tblArray(6) = "vwSPS"
    
    Dim tdf As TableDef
    Dim i As Integer
    For i = 1 To 6
        'Set tdf = CurrentDb.TableDefs(tblArray(i))
        For Each tdf In CurrentDb.TableDefs
            If tdf.Name = tblArray(i) Then
                Debug.Print tdf.Connect
                tdf.Connect = "ODBC;DSN=" & strDSN & ";Description=" & strDSNDescription & ";APP=" & strApplication & ";WSID=" & clsCheckWS.ComputerName & ";DATABASE=" & strDatabase & ";Trusted_Connection=" & strTrustedConn & ";UID=" & strUserName & ";PWD=" & strUserPwd
                tdf.RefreshLink
                Debug.Print tdf.Connect
                Exit For
            End If
        Next
    Next i

I created a client side query that works but is slow as it has to transmit and filter over 100k records.

So what's the Deal?
 
When you manually link the view, are you asked to choose a unique identifier?
 
Yes it does, I should of mentioned that earlier, so if your thinking what I'm thinking, I need to select the unique id field in code when the link to the view is refreshed and that should solve the problem. My problem is I havn't found any examples of selecting the unique id record.
 
The problem is that Access needs a unique identifier if you want to be able to update an ODBC data source. Since you are linking to a view, it doesn't really have an index since it is not a single table but is actually the equivalent of a query. To provide a unique identifier, you can define a psuedo index using DDL (Data Definition Language). This is the part of SQL that allows us to define database objects. The SQL we use to query a data base is called DML or Data Manipulation Language.

I will post the relevent help entry because it is VERY difficult to obtain if you're not using A97.

PHP:
CREATE INDEX Statement
Creates a new index on an existing table.


--------------------------------------------------------------------------------

Note For non-Microsoft Jet databases, the Microsoft Jet database engine does not support the use of CREATE INDEX (except to create a pseudo index on an ODBC linked table) or any of the data definition language (DDL) statements. Use the DAO Create methods instead. For more information see the Remarks section.


--------------------------------------------------------------------------------

Syntax
CREATE [ UNIQUE ] INDEX index
    ON table (field [ASC|DESC][, field [ASC|DESC], ...])
    [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

The CREATE INDEX statement has these parts:

Part Description 
index The name of the index to be created. 
table The name of the existing table that will contain the index. 
field The name of the field or fields to be indexed. To create a single-field index, list the field name in parentheses following the table name. To create a multiple-field index, list the name of each field to be included in the index. To create descending indexes, use the DESC reserved word; otherwise, indexes are assumed to be ascending. 


Remarks
To prohibit duplicate values in the indexed field or fields of different records, use the UNIQUE reserved word.

In the optional WITH clause you can enforce data validation rules. You can: 

Prohibit Null entries in the indexed field or fields of new records by using the DISALLOW NULL option. 
Prevent records with Null values in the indexed field or fields from being included in the index by using the IGNORE NULL option. 
Designate the indexed field or fields as the primary key by using the PRIMARY reserved word. This implies that the key is unique, so you can omit the UNIQUE reserved word. 
You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as Microsoft® SQL Server™, that does not already have an index. You do not need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. Creating a pseudo-index on a table that would ordinarily be read-only can be especially useful.

You can also use the ALTER TABLE statement to add a single- or multiple-field index to a table, and you can use the ALTER TABLE statement or the DROP statement to remove an index created with ALTER TABLE or CREATE INDEX.


--------------------------------------------------------------------------------

Note Do not use the PRIMARY reserved word when you create a new index on a table that already has a primary key; if you do, an error occurs.


--------------------------------------------------------------------------------

See Also
ADD USER Statement CREATE USER or GROUP Statement 
ALTER USER or DATABASE Statement CREATE VIEW Statement 
ALTER TABLE Statement DROP Statement 
CONSTRAINT Clause DROP USER or GROUP Statement 
CREATE PROCEDURE Statement GRANT Statement 
CREATE TABLE Statement REVOKE Statement 


Example
CREATE INDEX Statement Example
 
Pat,
Thanks for the tip it appears to be an advanced topic that not to many people know about.

It looks promissing but a full review will have to wait for Monday.

Have a nice weekend and thanks again :cool: . I'm sure I'll have another question by this time next week.
 

Users who are viewing this thread

Back
Top Bottom