SQL 2005 VIEWS Dont Allow Update in Access 2003

Are you sure the view is updateable? Most views are readonly ...

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

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

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

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

I get the error 3265 item not found in this collection

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.

I'll Try that

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

Select allOpen in new window

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

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?

sorry I missed you comment when I posted mine

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.

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.