We help IT Professionals succeed at work.

SQL 2005 VIEWS Dont Allow Update in Access 2003

Medium Priority
269 Views
Last Modified: 2008-01-27
I have a SQL Server 2005 database which has some views (each view is one table only).  I want the users to be able to link to the views in Access 2003 and update the data.  I get the views to link fine, but they are all read only.  

The users have all permissions on the views but none against the tables.  Is this the problem?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you sure the view is updateable? Most views are readonly ...
CERTIFIED EXPERT

Commented:
I assume you are linked via an ODBC connection. When you link to a view, you must tell the system what fields constitute the primary key as the view does not provide this (even when based on one table). Until you do this and ODBC link to a view is read only


Kelvin

Author

Commented:
Kelvin,

Yes I am using ODBC and I am linking the views programatically - with a DNS Less Connection

Set tdf = CurrentDb.CreateTableDef(vwName, dbAttachSavePWD, rsVW![Name], connODBC)
CurrentDb.TableDefs.Append tdf

How can I set the key? It is always the first column in my view.

Kim
CERTIFIED EXPERT
Commented:
OK, I'm a little unsure of the syntax, but once you've created the table try

CREATE UNIQUE INDEX idx ON tdf.name(column 1) WITH IGNORE NULL

Author

Commented:
the Table def object has a way to create an index but you need a specific field name.  So I now need to find some way to link by the ordinal position rather than the name.  

Thanks, I am now on my way.

Kim
CERTIFIED EXPERT

Commented:
After creating the table, you could make a string equal to tdf.Fields(0).Name or somehting like that and then use that in the Create Index

Author

Commented:
I get the error 3265 item not found in this collection
CERTIFIED EXPERT

Commented:
I have just tried str = tdf.Fields(0).Name which set str = the column 1 column name
Dim str as string

Then try CREATE UNIQUE INDEX Idx ON abc(str) WITH IGNORE NULL

You'll need a bit of code to return the table name where I have abc - I suspect tdf.name in this line will play up.

Author

Commented:
I'll Try that

Author

Commented:
where did you have the str = tdf.fields(0).name?  I put it after the set and befreo the append and I still get the error
Debug.Print vwName
                                                                    'Local Table Name, ,Remote Table Name
            Set tdf = CurrentDb.CreateTableDef(vwName, dbAttachSavePWD, rsVW![Name], connODBC)
            
            CurrentDb.TableDefs.Append tdf

Open in new window

CERTIFIED EXPERT

Commented:
It would have to be after the append row
I'd have something like

Dim strTbl as String
Dim strFld as String

With tdf
    strTbl = .Name
    strFld = tdf.Fields(0).Name
end with
CREATE UNIQUEW INDEX Idx ON strTbl(strFld) WITH IGNORE NULL



Kelvin

Author

Commented:
I can get the field name only after the append with the following

Dim ntdf As TableDef
    Dim strIDX As String
    Dim strfieldName As String
    Dim strTablename As String
   
    For Each ntdf In CurrentDb.TableDefs
        strfieldName = ntdf.Fields(0).Name
        strTablename = ntdf.Name
        Debug.Print strfieldName
        Debug.Print strTablename
       
'        strIDX = strfieldName & "_idx"
'        Dim idxPK As Index
'        Set idxPK = ntdf.CreateIndex(strIDX)
''        With idxPK
''            .Fields.Append = .CreateField(strfieldName)
''        End With
'        ntdf.Indexes.Append (idxPK)
    Next
I cant seem to add the index = how would I call the create unique code you mention?

Author

Commented:
sorry I missed you comment when I posted mine
CERTIFIED EXPERT

Commented:
Instead of the commented code just use the line

strIDX = strfieldName & "_idx"

CREATE UNIQUE INDEX strIdx ON strtableName(strfieldName) WITH IGNORE NULL

Problem is I've never worked with a DSN Less connection. With a dsn you are prompted for the unique field as you create the link.

Author

Commented:
Yes - the DNS Less connection - I am using it for a number of reasons - but the main reason being is that i dont want the users to be tempted to connect via another access application, by having our db pop up.  Also at the start up I delete all table defs and I loop through the SQL system tables to get the names of the views I want them to be connected to.  So I can add new views and they just get them when they open the app.  I didnt want the users to be prompted since they will have no idea what the key should be.

As for the Create Unique Index it just comes up in RED - so I will work on that tomorrow if you would like to see how the DNS Less connection works I will be happy to share it with you - its very cool.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.