Linking SQL view as table in Access
-
I have a view from SQL gathering info from 3 tables - linked into Access as a table, rather than a view, as it needs to be editable. Everything works fine when you first link it.
HOWEVER, if I have to refresh the link using the Linked Table Manager, in order to swap between my live and test SQL tables using ODBC link set up - it becomes read only.
I have a feeling it is due to the unique reference being lost between the time you initially link the view and then relink it.
Using SQL through Access is still quite a new thing for me, so any idiot proof advice would be much appreciated.
I have read similar comments on other forums but nothing that ever seemed to get resolved!
Thanks!
-
What happens if you drop/re-link it?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
As it is a fresh link it works fine! When I attach the table using Get External Data and choosing the ODBC source, it then pops up a box asking for the unique reference field. The view then works as a table and can be edited fine.
Relinking seems to be the problem - ONLY if you use Linked Table Manager and tick Always prompt for New Location, which I need to do to swap between test and live data.
-
I think you have to specify WITH VIEW_METADATA at the end of the definition of the view. Look up CREATE VIEW in BOL:
VIEW_METADATA
Specifies that SQL Server will return to the DBLIB, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata returned by SQL Server to the client-side DB-LIB, ODBC, and OLE DB APIs, which allow the client-side APIs to implement updatable client-side cursors. Browse-mode meta data includes information about the base table that the columns in the result set belong to.
For views created with VIEW_METADATA option, the browse-mode meta data returns the view name as opposed to the base table names when describing columns from the view in the result set.
When a view is created WITH VIEW_METADATA, all its columns (except for timestamp) are updatable if the view has INSERT or UPDATE INSTEAD OF triggers. See Updatable Views later in this topic.
-------------
I don't know why your view works when you first link, and then stops working, but perhaps this will help.
--Rich
-
Nope, that didn't work either. I didn't actually notice any difference!
Initially it works perfectly within Acess - you can add and edit.
As soon as you relink it it becomes read only, which causes all sorts of problems!!
Thanks for the answers so far though...
-
OK - in answer to my own question and for the benefit of others who may have the same problem - I managed to solve it!
The problem does indeed stem from the loss of the unique index when the 'table' is relinked. To rectify this i created a DDL query using the follwing syntax
CREATE UNIQUE INDEX AppointmentIndex
ON vwAppointmentFormDetails
(
AppointmentNo ASC
)
I then added this to the end of the code that relinks the table - or I can run it manually after a relink using Linked Table Manager. This resets the index and makes the table editable!!
-
Indexes are not "Lost." There is something else going on here.
Your solution implies that you did not have a unique non-nullable primary key (preferably autoincrement) on your table. This is really a key requirement to working painlessly with Access/ADO/ODBC. This is the only way that Access can locate and refresh your record after an update, or after the normal refresh interval.
All of your tables should also have a timestamp column, or else your system could get bogged down with huge Access(ADO/ODBC)-generated column-checking SQL on every update.
-
There are unique values in all of the tables within the view. The problem stems from the view being able to distinguish which is the primary index. This is set manually if you use the Get External Data method of linking the table - but 'seems' to be 'lost' when you relink.
All the DDL query does is re-establish that unique index and the view remains editable.
They all have timestamp columns as well.
-
Do you mean that there are unique primary keys in all the tables? Because, unique values alone or unique indexes alone may not be adequate for Access and ODBC.
OTOH, if you do have primary keys on all tables, I would say this is a real ODBC bug.
Of course it may depend on the specific view fields that you are trying to edit. Generally speaking, this is Access/ODBC version dependant. (And the rules change again for each version of ADO as well.) Getting editable multi-table views has always been a bit of a pain.
-
Hi,
I use the following functions to refresh my links between Access and SQL.
The removeDBO allows my access programs to operate with either original Access tables or linked tables in SQL without any code changes.
RefreshODBC will relink all ODBC linked tables. I switch between Dev and Prod by changing my ODBC setup and running RefreshODBC will refresh the links.
Try it - maybe it will solve your problem.
I cant remember where I picked up these functions but all credit due to original authors.
Richard
Function RefreshODBC() As Collection
Dim tdf As TableDef, db As Database
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If Left$(.Connect, 4) = "ODBC" Then
tdf.RefreshLink
End If
End If
End With
Next
Set tdf = Nothing
Set db = Nothing
MsgBox "Refresh Complete"
End Function
Sub RemoveDBO()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If Len(tbl.Connect) > 0 Then
tbl.NAME = Replace(tbl.NAME, "dbo_", "")
End If
Next
Set tbl = Nothing
MsgBox "Remove Complete"
End Sub
-
There is a documented bug in Access (at least was on 97), that when Access reads a SQL Server table, it only looks for the first index available. If that index happens to be a UNIQUE index ( Primary Key, Cluster Index, or a unique constrain), then Access uses that index to enforce integrity on the table. If that first index is NOT UNIQUE, Access does not continue reading the other indexes to look for one that will guarantee consistency. So, in this case, you have to manually tell Access what's the primary key.
Btw, you can use the same code above to switch servers; just change the DataSource property on the table and voila! you're done.
But you still have to re-link those tables that Access cannot get to the proper index.
Let us know if what worked.
-
Thanks for everyone's suggestions. The DDL query run after the relink works fine and restablishes the unique index, and that fits in perfectly with how my other structure is set up - and to be honest was the most simple!!
Thanks to all!
-
C R
WHat exact changes need to be made to switch servers ?
Richard
-
The easiest way I have found to do what I think you are trying to do is to change the underlying ODBC link name.
I create two ODBC links. For example, SQLSeverProd and SQLServer. I then setup the Access table links to point to SQLServer ODBC connection I then do my development and testing, etc. When ready to deploy, I merely ‘switch’ the underlying ODBC to point to the production database. That is, I rename SQLServer to SQLServerTest and SQLServerProd to SQLServer.
You may have to perform a Link Refresh, but as long as all the linked tables have the same rows and columns, I have not had a problem.
-
You can try changing the Connect property of the tables, to change the Database name and/or the server name. But since this property is Read-only, the only way around I foud was to create a temp table, add all tables with their Connect property, and loop thru the table and change the connection string on the fly.
Please send me a note to tpasql04@yahoo.com if you want the code, since it's a bit long; not complicated nor fancy, but bit long.
ps:Sorry for the delay, but my yesterday's post did not made it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply