permissions - Linked SQL table in Access 2003 (!) not updatable -
i'm working in legacy app moment, upgrading access 2003 link sql server tables (2008 r2 or later). tables linked code, can insert, not update or delete. i've tried on web, no dice. details below.
being terse not tl;dr.
tables first created using upsizing wizard. in use, app has connect different ones in same schema, can't set , forget. can't local dsn's, many installs, though dsn file possible. problems there too, dsn not found. details later.
before rest: i'm further updating app access 2016 or so. if different enough / easier there, i'll wait few days. maybe suggest best refsite that.
* problem details follow *
using dsn , ui link table, editable table. hurray.
but when use code below (found on every refsite), link made selecting , inserting work. else fails fails fails, no matter what.
public function linktosqltable(sqlinstance string, sqldb string, sqltablename string, localtablename string) dim linked new tabledef ' ***factored-out functionality, known work: reader can ignore*** ' deletetable localtablename ' connection-string steps, placeholders replaced args ' dim scnx string scnx = "odbc;driver=sql server;server=_instance_;" & _ "database=_db_;integrated security=sspi" scnx = replace(scnx, "_instance_", sqlinstance) scnx = replace(scnx, "_db_", sqldb) ' linked-table steps ' set linked = currentdb.createtabledef(localtablename) linked.connect = scnx linked.sourcetablename = sqltablename currentdb.tabledefs.append linked ' ui ' refreshdatabasewindow end function
* id column or permissions? *
i thought problem lack of identity column originally, added one, no change. @ least have pk field should. ;-)
when manually link table, ui demands know id column. still it? fine, how set in code? searches revealed nothing.
i assume it's permissions sites etc. say. took steps think of fix that. no dice.
* things i've tried *
aside id-column stuff said before, these things (not in order):
- since dsn saved file, tried using exampled, in cnx string. fail.
- used dsn contents, winnowed & translated, in cnx string. fail.
- used connection string table had connected manually dsn. fail.
- changed driver in cnx string across major options, omitted it. fail.
- changed security in cnx integrated security=sspi , other options, , omitted entirely. fail.
- i added actual local user exampled, , without password. fail.
(previous few options tried across earlier options, though not 100% coverage.)
- in sql server, using ssms, tried security power:
- added sqs-authentication login instance
- matching user default db seen here
- gave login-user read , write permissions in db here (plus others, sometimes)
- added matching id & pw cnx string. fail.
i tried setting db in sqs have let-everyone-do-everything "security" temporarily. fail.
this, that, , other thing. fail!!
so permissions issue? way use dsn file after all? mismatched permission settings in cnx string? boneheaded oversight? else i've missed? i'm pretty @ both sql server , access, @ basic level in security stuff , connection strings devil.
* retrieved table properties *
just in case help, retrieved these (after objects added tabledefs collection).
** one, done in ui , dsn , this-is-id-field, worked editing: **
name = dbo_tblsendto updatable = false datecreated = 4/19/2016 11:11:40 lastupdated = 4/19/2016 11:11:42 connect = odbc;description=sql server tables telesales 5;driver=sql server native client 10.0;server=(local)\sqlexpress;trusted_connection=yes;app=microsoft office 2003;wsid=cmserver;database=ts5_general; attributes = 536870912 sourcetablename = dbo.tblsendto recordcount = -1 validationrule = validationtext = conflicttable = replicafilter =
** , one, table linked via code, didn't: **
name = tblsendto updatable = false datecreated = 4/19/2016 11:17:51 lastupdated = 4/19/2016 11:17:51 connect = odbc;description=sql server tables telesales 5;driver=sql server native client > 10.0;server=(local)\sqlexpress;trusted_connection=yes;app=microsoft office 2003;wsid=cmserver;database=ts5_general; attributes = 536870912 sourcetablename = dbo.tblsendto recordcount = -1 validationrule = validationtext = conflicttable = replicafilter =
* plea *
so..... please me out. don't feeling stupid this, , regrettably need instead of replacing .net code or similar.
thanks, can... ed.
alas, able answer own question.
edited little since first posted in reply hansup's comments
i had added identity column table couldn't edit. however, had not set as primary key. turns out using identity
doesn't make primary key automatically.
but latter, making primary key using either of 2 possible ddl syntaxes, crucial. since thought had dealt no edits without unique key problem, focused on permissions.
all of permissions things here, then, sideshow.
the upshot of sure add identity column and make primary key if reason original table schema didn't have that.
if have time, trimming question reflect i've discovered.
Comments
Post a Comment