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?
 
Kelvin SparksConnect With a Mentor 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
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.