Access to SQL

[Walt Herman]

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

 

[Jeff Boyce]

Walt

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

Regards

Jeff Boyce
Microsoft Access MVP

 

[Walt Herman]

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]

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

 

[Mary Chipman [MSFT]]

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