acc97, refresh link, no longer updatable

[Roger]

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=Con nect to
baan;UID=someus er;PWD=somepwd; APP=Microsoft®
Access;WSID=CTX 02;DATABASE=som edb

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

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

[Tom van Stiphout]

Re: acc97, refresh link, no longer updatable

On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<lesperancer@na tpro.comwrote:

Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmaticall y 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=Con nect to
baan;UID=someus er;PWD=somepwd; APP=Microsoft®
Access;WSID=CTX 02;DATABASE=som edb
>
>if I refresh the link with
strNewConnect = tdfTmp.connect
tdfTmp.Connect = strNewConnect
tdfTmp.RefreshL ink
>
>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

[Rick Brandt]

Re: acc97, refresh link, no longer updatable

Tom van Stiphout wrote:

On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<lesperancer@na tpro.comwrote:
>
Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmaticall y specify that; not even sure it is
possible at all.

CurrentDB.Execu te "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

[Roger]

Re: acc97, refresh link, no longer updatable

On Oct 25, 5:52 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:

Tom van Stiphout wrote:

On Fri, 24 Oct 2008 12:37:13 -0700 (PDT), Roger
<lesperan...@na tpro.comwrote:

>

Delete the link and reattach it manually. Observe that Access prompts
for the primary key.
I don't know how to programmaticall y specify that; not even sure it is
possible at all.

>
CurrentDB.Execu te "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