Linked SQL Server tables become read-only

[ErikSnoek]

Hello everyone,

I'm having a problem I can't seem to get around. I have an Access FE with a MSSQL 2005 BE and I'm trying to relink the tables everytime a user opens the database so they don't have to provide login details for the SQL server. I added the code shown below (found it on mcse.ms) in the On Open event of my startup form:

    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
    For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then
            Tdf.Connect = "ODBC;Driver=SQL Server;Server=IT04;APP=Microsoft Office 2003;Database=databasename;Uid=username;Pwd=password;"
            Tdf.RefreshLink
        End If
    Next

Now, the code seems to be updating the links quite well because after running the code I'm able to view the data. However, that is ALL I'm able to do with the data. The account I'm using in the connectionstring has DB_DataReader and DB_DataWriter permissions, so I should be able to write data right? I can't. For some reason all the relinked tables become read-only.
I've done some testing and linked the tables through a DSN and I have the ability to both read and write then. I want to link them DSN-less though so I don't have to create a new DSN on all client computers..

I hope someone knows what I'm doing wrong here!

Thanks in advance,
Erik

 

[Dennisk]

does the SQL table have a primary key? If not that may be your problem.

 

[ErikSnoek]

Thanks for the reply.

Yes, it has a primary key. However, when I link the tables using that whole Data Source wizard I get prompted to choose a unique identifier. So in that prompt I choose the field I set up as primary key on the SQL server. Maybe that is why I'm also able to write data then.. Would there be any way to specify this unique identifier using VBA? Or to automate that what happens when I manually choose a unique identifier in that prompt?

 

[rolaaus]

I don't have a lot of experience with linked/SQL back-end, but if you don't find any other solution, you may consider making your forms "unbound" and doing all the SQL work with ADO commands - it's a pain to code, but the up side is it runs faster.

I will try to see if I can find something about what your scenario, but I don't know if I will have the time. Have you tried searching MSDN on Microsoft? http://msdn.microsoft.ocm/

let us know if you find out anything and what it was you found.