A trick for setting a primary key on a linked sql server view in microsoft access

[nIGHTmAYOR]

ok this isnt actually a question but a trick i'd like to share , if by any chance you have a better way feel free to share
now here goes the problem first :

you have an updatable view on sql server that is linked to your project , the view shows as a linked table with only one draw back .. no primary key is set. this could be annoying as for further trials to use such view in joined queries it would output an un-updatable query. what is more interesting is that on linking to other non microsoft databases views ex: adaptive sql server anywhere ms access manages to pop up a wizard dialogue that guides you on the process of setting certain fields as primary keys !

now the solution to that :
rename your view temporarily (ex : myview_bak) now create a table with the same structure of view in sql server and set your key as fit. link the table in ms access and notice how access stores table structure including key . now simply delete the sql server table , rename view back to its default name . notice anything ? yes , access didnt update the table structure and your view now have a set key for it !

Important Notice :
Never refresh table useing linked table manager as it will restore origional structure.

Now my questions :
1 - why would microsoft have it easier for other non microsoft databases ?
2 - is there a way to trigger the key selector dialogue for linked tables programaticaly ?

 

[Banana]

Hmmm... interesting.

I had some misconceptions as I thought that manually setting primary key was only necessary for DSN-less connection with SQL Server and necessary for any other RDBMS where Access may not fully understand the structure (at least, that was the case with MySQL as the backend). I did got that dialog once but I think that was only if I used DSN.

In my case, I run a routine to create the indexes by calling a stored procedure which supply the complete SQL statement for Access to execute in creating the necessary index, so it's fully automated even for refreshing the links. It works because I can execute SQL statement against MySQL to query what index are within a given table with name "PRIMARY", getting only one row which is the column name that is the primary key, then I drop in that name and the table name in the SQL statement CREATE INDEX, then run it in Access.


As for the question about getting that dialog pop up programmatically, my first place to look would be under RunCommand constants. Terry has a great compilation of all constants. Unfortunately, it's not searchable, so you have to browse each letter to see if there is such constant that will display the key selector dialog. I tried few letters where I think it would be under (L, P, K, and T), and I don't see anything leaping out as apropos to me, expect possibly for acCmdLinkTables or acCmdLinkedTableManager (which I don't think will give you the dialog you wanted, but see see).

HTH.

 

[nIGHTmAYOR]

this trick also applies for setting a bogus primary key for sql server tables other than the one defined.

 

[LPurvis]

Creating a primary key on linked Views to establish uniqueness and hence updatability... (For the indexes are needed by Jet and are created on the local table definition).

I dare say the linking UI dialog is good for non-coders but for developers I don't see why we wouldn't just create them in code.
DAO isn't interested in assigning indexes to liked tables - but DDL statements are more obliging.

e.g.
CurrentDb.Execute "CREATE INDEX PrimaryKeyIdx ON TableName (YourPKField) WITH PRIMARY"

 

[datAdrenaline]

I personally employ the DDL technique Leigh has shown ... Plus, since Leigh did not mention it, but no doubt knows this, the DDL statement technique allows you to refresh your links with the linked table manager while maintaining the PK indexes on the linked tabledef.

But, besides that, I was unaware of the technique presented to accomplish this task.... as Bannana says .. "interesting" .. I would bet a Compact & Repair between naming would prevent this from working ... another question, does the Name AutoCorrect effect this at all? ...

 

[Banana]

So Leigh and Brent has replied, but didn't say anything about whether it's expected behavior for Access to fail to pick up the primary key when linking to a backend, even SQL Server? (Not that their posts weren't helpful- it was great food for thought as always. It's just me who's perpetually confused.)

As I said, I thought it was just a DSN-less connection thingy, because my memory tells me that if I use DSN, it'd work and get the PK automatically (but that was against MySQL, not MS SQL)....

So.. confused.

 

[nIGHTmAYOR]

CurrentDb.Execute "CREATE INDEX PrimaryKeyIdx ON TableName (YourPKField) WITH PRIMARY"

Thanks LPurvis for cutting the chase , you see the above methode occured to me whilest under pressure (Most my techniques are ) and was found pretty effective and no it's not affected by nothing but refreshing the table through table manager . now i believe i should furtherly analyse above technique posted by you throughly as i realy wonder how would ms access deny changing structure of a linked table visualy (using table design returns error "This table is a linked table , changes in design can not be saved") yet allow it by code. (realy not wondering its not like first time ms access does that kind of stuff )

 

[Banana]

Actually, if my memory serves (which means I need to go and test it), you can do some stuff in design view even for linked tables. You certainly can't rename a column, change a data type, or add a new column, but I'm sure you can add description property, create index... this sort of stuff.

It's kind of confusing because you get that dialog "you can't make changes" but doesn't actually say that there are some things you can do in the view.

 

[nIGHTmAYOR]

banana no need to test it , it wont work

 

[Banana]

Really?

(testing anyway)

Ahh, it does work- but only for description, Required, Validation Rule/Text, Input Mask, Caption properties. Just not indexes, which makes no sense at all since a DDL statement can be executed.

Microsoft guys sure are arbitrary...

 

[nIGHTmAYOR]

they are my favorit too

 

[datAdrenaline]

Working with a SQL Server linked TableDef this is what I experienced ....

{Bannana's items are in italics}

Properties that I was able to change (save) through the TableDef UI ...
-------------------
Description
Format
Input Mask
Caption
Decimal Places
Unicode Compression
IME Mode
IME Sentence Mode
Smart Tags
All the properties on the Lookup Tab

Properties that Don't stick when changed ... despite the fact that when click on the "Save" button the TableDef UI does not raise an error, thus making one THINK the setting was changed.
-------------------
Default Value
Required {different finding than Bannana}
Validation Rule/Text {different finding than Bannana}
Allow Zero Length

These don't stick either ... but an error is raised (not a good description though, IMO) when you click on the "Save" button the TableDef UI.
----------
FieldSize
Indexed
.....

Despite what seems to be inconsistency, it is apparent that you can not change properties that are schema bound. If the property is merely used by the Access Application, then you can change/save it.

It also makes sense that you CAN create an local index via DDL, but not the TableDef UI. I say this because with the TableDef UI, Access knows its visualizing the properties of a LINKED table, which means the JET does NOT control/manipulate the Schema of the Source, and since an Index is part of the Schema the TableDef UI knows it can't (won't) do anything with it.

When we execute a DDL statement to create and Index on the LINKED TABLE DEF, we are telling JET to create a LOCAL index which is a different animal than a schema bound index on the source. And, as noted, that "different animal" is NOT managed by the TableDef UI (...hmm... there is a wish list item eh? ... give the Access TableDef UI the ability to manage some schema properties of the linked source!). But, amazingly enough, a LOCAL index on the linked tabledef can (and is) visualized through the TableDef UI, but as mentioned, it just can be managed there.

Now ... regarding Bannana's comment:

>> but didn't say anything about whether it's expected behavior for Access to fail to pick up the primary key when linking to a backend, even SQL Server? <<

I am not sure I follow, since Access DOES pick up and visualize all schema bound indexes defined on a source table, AS WELL AS a View! (speaking from a SQL Server BE point of view). The kicker is, have you ever created an Indexed View? ... Its not something you can do through the Enterprize Manager's View Design Tool, any time I have created an Indexed View, I have used a Stored Procedure to perform the task ... Read the restrictions for creating an Indexed view on this link:

http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx

The restrictions are laced through out (many are in the arguments section) and they are summarized in the Remarks section in a sub-section label "Restrictions on indexed views" ... along with that. The example labeled "G. Create an index on a view" shows the technique for creating a View with an index. And again, once View has an bonified schema bound Index, access will pick it up as such and visualize it through the TableDef UI. As a matter of fact, when linking to an Indexed View, you will NOT be prompted to identify the Unique Fields which are ultimately used to create a LOCAL index.

...

Hope this sheds some light on the topic! ...

 

[Banana]

Brent,

My test was against a Access backend, so that may account for different finding. Also, I didn't bother with more obscure properties. (Too lazy. )

I will see if I have a test database I can test against MySQL and see whether it acts same as SQL Server (I'm more curious if the restrictions is common to all non-Access backend or is dependent on ODBC drivers)

As for my poorly-worded question- Sometime when you link a table (using interface), Access pops up a dialog asking you to select a column which is the key. I'm not exactly sure what conditions causes Access to fail to pick up the index and thus require additional prompt from the users to identify the given column as a key. I had assumed it was usually true of DSN-less connection because Doug Steele calls a function to create local indexes when we refresh the DSN-less links using his code and was piqued when NightMayor described his experience in his OP. Did that make sense.

As for setting indexes in UI, you're probably right that Access's Table Designer view it more like this:

ALTER TABLE whatever ADD INDEX ON acolumn UNIQUE;

rather than

CREATE INDEX ...

Still, one could at least hope that they would give a pop up prompting something like "Access cannot alter the schema, but can create a local index. Do you want a local index created?" Yes/No. Would be more consistent, I suppose.

 

[datAdrenaline]

Hello Bannana ...

>> Too lazy <<
..Ummm .... I ONLY tested a SQL linked table ... so ... count me in with the "too lazy" crew! ...

>> Sometime when you link a table (using interface), Access pops up a dialog asking you to select a column which is the key. I'm not exactly sure what conditions causes Access to fail to pick up the index and thus require additional prompt from the users to identify the given column as a key. <<

When there is not a UNIQUE index on source, Access will prompt you to identify field(s) that can be used to create a UNIQUE local index. What Access will do when creating the Linked table is first search for a PrimaryKey, if one is found, then great its identified in the TableDef UI as the PrimaryKey. If NO PrimaryKey index is found, Access will then start searching for a UNIQUE index, the first one it finds is identified by the TabelDef UI as the "Primary Key", but the identified index is merely UNIQUE on the schema level and not a true PK, but it IS the LOCAL PK. So you can have several indexes on the source, but if NONE are Unique, then you will get prompted by Access to identify a unique set of fields to create a Key. But even when you are prompted, Access DOES identify all the remaining non-unique schema bound indexes on the source as evidenced by looking at the Indexes dialog.

>> I had assumed it was usually true of DSN-less connection because Doug Steele calls a function to create local indexes when we refresh the DSN-less links using his code and was piqued when NightMayor described his experience in his OP. Did that make sense <<

Yep ... but a DSN is merely a mechanism to store connection information and should not have any effect on any of this.

>> one could at least hope that they would give a pop up prompting something like "Access cannot alter the schema, but can create a local index. Do you want a local index created?" <<

That would be AWESOME ... but obviously is not case at this point! ... We better find a "What do we want in Access v28..." thread! ...

 

[Banana]

Brent,

Thanks for some additional information.

I just dug up my old, old, ancient MySQL test application and did found that it behaves pretty much same as what you reported with SQL Server (e.g. The Validation Rule/Text won't stick, though there is no error) and it refuses to create an index, so that proves that I have Alzheimzer's Amensia.

But yes, it will identify other indexes just fine.

As for DSN thingy, you're right that it's just mechanism, and I am probably groping in dark, but I was under the impression that when using UI, Access is doing much more in background than if a DSN-less connection was created?

Would have to test that idea, but not right now. So much tests, so little time!

 

[isladogs]

Actually, if my memory serves (which means I need to go and test it), you can do some stuff in design view even for linked tables. You certainly can't rename a column, change a data type, or add a new column, but I'm sure you can add description property, create index... this sort of stuff.

I realise this is a thread from ten years ago which was primarily about setting a primary key in sql server views. However, I just want to pick up on the above point by Banana that wasn't really followed up at the time.

You can change almost every feature of a linked Access table using code. For example you can create a new linked table or delete an existing linked table, add, rename or delete fields, change datatypes and field sizes, add a PK field etc.

You can also do all the above for linked SQL Server tables by running a script file with a .sql suffix.