Linking to a view - how to avoid 'Select Primary Field' 1

[JeffTullin]

When I use Access to link to a table in an Oracle db, it will work nicely.
But when I link to a View, up pops a dialog asking for which field/s should be used as the primary unique field.

In this particular instance, I neither know nor care.
But even though I surround the code that links to the tables and views with a

DoCmd.Setwarnings False
'link
DoCmd.Setwarnings True

..the dialog still appears, and I can't find anything in the help to suggest how to tell Access to just link and shut up..

Has anyone run into this, and is able to offer a solution?

 

[lameid]

Access needs a unique field so that can update the data if you make changes to it. Access uses an update statement with criteria referncing the unique key? Make sense? If you do not need to make updates, you could write an SQL pass through query that selects everything from the view. Access won't beg for a unique key then. Otherwise, you have to care what the unique key is.

 

[JeffTullin]

Well, I understand your reasoning.
I won't be updating, so I dont need to know the unique ID.
Heck, I make tables that don't even have one, sometimes.

SQL pass-through query eh?
I'll look into that route - never used it before, but I'd rather not have to code differently just because I used a view name instead of a table.

Cheers.

 

[1manedp]

I have a related problem. I have a number of views that I will be wanting to insert or update on (SQL Server 2000 back-end), so I want to link to all of these using vba by specifying the single server resource once and making the links. I don't want to have to reset all the links by picking each table or view manually upon each upgrade. As I maintain development, test, and production environments as well as multiple locations, the need to reset all the links is a common occurrence.

Although I can use vba to create the connect string and create the link to the server tables and views, I am unable to find a way to specify the unique field(s) identifier that Access 97 normally asks for when linking to a view.

Any ideas?

TIA,
-Steve

 

[lameid]

Once you set the connect string and use the refreshlink method, does it prompt you for the unique key again? I would think that it would retain that information but I work mostly with JET.

An alternative would be to add a unique index over 1 field to the table that the view is based on and add that key to the view.

 

[JeffTullin]

>>An alternative would be to add a unique index over 1 field to the table that the view is based on and add that key to the view.<<

I don't have that kind of control over the source database.

 

[1manedp]

Thanks lameid,

I haven't tried the refreshlink method yet (I've been tearing down the links and recreating them with code instead) and I will have to look into it -- it sounds as if this could make upgrades much less intense.
As far as the unique index is concerned, I found the piece in the documentation that talks about it. MS calls it a pseudo-index on the Access side, so I have to have a unique identifier on the SQL side and then index the linked view on the Access side using this 'pseudo index'.

Thanks so much for your help!


Jeff:

If you create the link from code, Access (97) won't prompt you for a unique identifier.

It's something like this:

[tt]
Dim dbs As Database, tdf As TableDef

' Return reference to current database.
Set dbs = currentdb
' Create new TableDef object.
Set tdf = dbs.CreateTableDef(&quot;MyTableName&quot
tdf.Connect = &quot;ODBC;DRIVER=SQL Server;SERVER=&quot; & _
&quot;MyServerName;APP=Microsoft Open Database Connectivity;DATABASE=&quot; & _
&quot;MyBackendDBName&quot;

' Link to the table
tdf.SourceTableName = &quot;MyTableName&quot;
dbs.TableDefs.Append tdf

[/tt]

Hth,
-Steve

 

[JeffTullin]

Now thats interesting.
I user the transferdatabase method in VBA to link the view, and it does prompt.
I'll give the tabledefs way a shot..

 

[lameid]

Just to clarify, you can set the connect property of an existing tabledef object and then use the refreshlink method to commit the change.