Hello all and thanks in advance for looking at my problem. I have an access
application which grabs a text file, does some modifications to the data and
then is supposed to append the "new" data int a SQL db table which exists in
the Access database as a linked table. The first time I run it all is good,
the data appends into the table successfully. It then appears that the link
becomes "stale" because I have to use Linked table manager to update the
link. My problem is that I do not want to have to teach the end users how to
do this. I want the link to remain valid regardless of the number of times I
run the macro. Any suggestions? Thx again!

Walt

Walt

What 'symptoms' are you seeing? How are you determining that the link has
grown 'stale'?

Regards

Jeff Boyce
Microsoft Access MVP

"Walt Herman" <(E-Mail Removed)> wrote in message
news:AE754DF0-7C6C-4FED-BB6C-(E-Mail Removed)...
> Hello all and thanks in advance for looking at my problem. I have an
> access
> application which grabs a text file, does some modifications to the data
> and
> then is supposed to append the "new" data int a SQL db table which exists
> in
> the Access database as a linked table. The first time I run it all is
> good,
> the data appends into the table successfully. It then appears that the
> link
> becomes "stale" because I have to use Linked table manager to update the
> link. My problem is that I do not want to have to teach the end users how
> to
> do this. I want the link to remain valid regardless of the number of times
> I
> run the macro. Any suggestions? Thx again!
>
> Walt

When I double click on the linked table I get #name# in every field of every
record of the linked table and the append query fails. However, if I do not
try and open the linked table the append is successful so long as I never
open the linked table from within Access. The short of it Jeff is your
question kind of provides a solution because if I just don't ever open the
linked table I'll avoid the problem. That said, it is an annooying problem as
I have come across it in the past and this time I was compelled to raise it
in the discussion group...Any ideas, not so much for now but long term???

"Jeff Boyce" wrote:

> Walt
>
> What 'symptoms' are you seeing? How are you determining that the link has
> grown 'stale'?
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> "Walt Herman" <(E-Mail Removed)> wrote in message
> news:AE754DF0-7C6C-4FED-BB6C-(E-Mail Removed)...
> > Hello all and thanks in advance for looking at my problem. I have an
> > access
> > application which grabs a text file, does some modifications to the data
> > and
> > then is supposed to append the "new" data int a SQL db table which exists
> > in
> > the Access database as a linked table. The first time I run it all is
> > good,
> > the data appends into the table successfully. It then appears that the
> > link
> > becomes "stale" because I have to use Linked table manager to update the
> > link. My problem is that I do not want to have to teach the end users how
> > to
> > do this. I want the link to remain valid regardless of the number of times
> > I
> > run the macro. Any suggestions? Thx again!
> >
> > Walt
>
>
>

So, you're saying that you can repeatedly run the query and that every
append works, but you can't open the table to confirm that it worked? Hmmm?

How is your SQL-Server table 'linked'? I'm assuming you used a DSN, but
....?

Regards

Jeff Boyce
Microsoft Access MVP

"Walt Herman" <(E-Mail Removed)> wrote in message
news:43EEDA69-2A13-4688-B2DE-(E-Mail Removed)...
> When I double click on the linked table I get #name# in every field of
> every
> record of the linked table and the append query fails. However, if I do
> not
> try and open the linked table the append is successful so long as I never
> open the linked table from within Access. The short of it Jeff is your
> question kind of provides a solution because if I just don't ever open the
> linked table I'll avoid the problem. That said, it is an annooying problem
> as
> I have come across it in the past and this time I was compelled to raise
> it
> in the discussion group...Any ideas, not so much for now but long term???
>
> "Jeff Boyce" wrote:
>
>> Walt
>>
>> What 'symptoms' are you seeing? How are you determining that the link
>> has
>> grown 'stale'?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Access MVP
>>
>> "Walt Herman" <(E-Mail Removed)> wrote in message
>> news:AE754DF0-7C6C-4FED-BB6C-(E-Mail Removed)...
>> > Hello all and thanks in advance for looking at my problem. I have an
>> > access
>> > application which grabs a text file, does some modifications to the
>> > data
>> > and
>> > then is supposed to append the "new" data int a SQL db table which
>> > exists
>> > in
>> > the Access database as a linked table. The first time I run it all is
>> > good,
>> > the data appends into the table successfully. It then appears that the
>> > link
>> > becomes "stale" because I have to use Linked table manager to update
>> > the
>> > link. My problem is that I do not want to have to teach the end users
>> > how
>> > to
>> > do this. I want the link to remain valid regardless of the number of
>> > times
>> > I
>> > run the macro. Any suggestions? Thx again!
>> >
>> > Walt
>>
>>
>>

I'd recommend putting a Profiler trace on the calls so you can debug
the conversation Access is having with SQL Server. See
http://msdn.microsoft.com/en-us/library/ms979207.aspx for a how-to and
http://msdn.microsoft.com/en-us/library/ms181091.aspx for the
documentation in SQL BOL.

--Mary

On Fri, 2 Oct 2009 08:37:02 -0700, Walt Herman
<(E-Mail Removed)> wrote:

>Hello all and thanks in advance for looking at my problem. I have an access
>application which grabs a text file, does some modifications to the data and
>then is supposed to append the "new" data int a SQL db table which exists in
>the Access database as a linked table. The first time I run it all is good,
>the data appends into the table successfully. It then appears that the link
>becomes "stale" because I have to use Linked table manager to update the
>link. My problem is that I do not want to have to teach the end users how to
>do this. I want the link to remain valid regardless of the number of times I
>run the macro. Any suggestions? Thx again!
>
>Walt