SQL 2005 VIEWS Dont Allow Update in Access 2003

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?
KimberleyYAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you sure the view is updateable? Most views are readonly ...
0
Kelvin SparksCommented:
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
0
KimberleyYAuthor 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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Kelvin SparksCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KimberleyYAuthor 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
0
Kelvin SparksCommented:
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
0
KimberleyYAuthor Commented:
I get the error 3265 item not found in this collection
0
Kelvin SparksCommented:
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.
0
KimberleyYAuthor Commented:
I'll Try that
0
KimberleyYAuthor 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

0
Kelvin SparksCommented:
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
0
KimberleyYAuthor 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?
0
KimberleyYAuthor Commented:
sorry I missed you comment when I posted mine
0
Kelvin SparksCommented:
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.

0
KimberleyYAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.