By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

acc97, refresh link, no longer updatable

P: n/a
I've got a sql server view, that I link as a table in access97
after initial linking, I can open the table and add more records (ie.
the >* vcr button is enabled)

the connect string for the table is
ODBC;DSN=dbSql;Description=Connect to
baan;UID=someuser;PWD=somepwd;APP=Microsoft®
Access;WSID=CTX02;DATABASE=somedb

if I refresh the link with
strNewConnect = tdfTmp.connect
tdfTmp.Connect = strNewConnect
tdfTmp.RefreshLink

I then lose the ability to add more records, and the >* vcr button is
disabled

is there something I'm missing in the refreshLink statement that would
be causing this ?

the sql view has insert, delete, select, update permissions
relinking the table with the link wizard makes it updatable again
Oct 24 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<le*********@natpro.comwrote:

Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmatically specify that; not even sure it is
possible at all.
Without the PK information the view (or table for that matter) will
not be updatable. After all, Access cannot guarantee that the update
would be to the record(s) you indicated.

-Tom.
Microsoft Access MVP

>I've got a sql server view, that I link as a table in access97
after initial linking, I can open the table and add more records (ie.
the >* vcr button is enabled)

the connect string for the table is
ODBC;DSN=dbSql;Description=Connect to
baan;UID=someuser;PWD=somepwd;APP=Microsoft®
Access;WSID=CTX02;DATABASE=somedb

if I refresh the link with
strNewConnect = tdfTmp.connect
tdfTmp.Connect = strNewConnect
tdfTmp.RefreshLink

I then lose the ability to add more records, and the >* vcr button is
disabled

is there something I'm missing in the refreshLink statement that would
be causing this ?

the sql view has insert, delete, select, update permissions
relinking the table with the link wizard makes it updatable again
Oct 25 '08 #2

P: n/a
Tom van Stiphout wrote:
On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<le*********@natpro.comwrote:

Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmatically specify that; not even sure it is
possible at all.
CurrentDB.Execute "CREATE UNIQUE INDEX foo " & _
"ON TableName (FieldName(s))"

This performs the same thing the linking wizard does when you are prompted
upon creating the link and you select the field names.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 25 '08 #3

P: n/a
On Oct 25, 5:52*am, "Rick Brandt" <rickbran...@hotmail.comwrote:
Tom van Stiphout wrote:
On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<lesperan...@natpro.comwrote:
Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmatically specify that; not even sure it is
possible at all.

CurrentDB.Execute "CREATE UNIQUE INDEX foo " & _
"ON TableName (FieldName(s))"

This performs the same thing the linking wizard does when you are prompted
upon creating the link and you select the field names.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com
ok.. that works... I forgot to look at the linked table to see if it
had a PK cause the sql server table has a PK and my understanding was
that ms-access wizard, when linking will use the PK for the sql table
and not prompt for it

it does that for other tables that I've linked but not this one....no
sure why, but the CREATE INDEX statement solves my problem
Oct 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.