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

 

[MErnst]

I just posted code in another post similar to this. Sounds like you need to
refresh your links to tables, easily done via VBA and your users will never
have to do a thing. I have a table created in my Access DB to record
connection info, user ID, and password for getting to my SQL DB, for every
linked table that I use. Here's the code used to refresh links:

<begin code>
' This function refreshes the DAO ODBC table connections
' listed in the tblODBCRefreshList table
' which lists the tables to be connected
'
' This function is used to automate the initial refresh of the ODBC
table connection
' so that updates etc can run without user intervention to supply the
UID and PWD components
' of the DB connection.
'
' 23/4/09
'
On Error GoTo refresh_err
Dim daoDB As DAO.Database
Dim tdef As DAO.TableDef
Dim RS As DAO.Recordset
Dim strConString As String
Set daoDB = CurrentDb
Set RS = CurrentDb.OpenRecordset("tblODBCRefreshList")
Do While Not RS.EOF
Set tdef = daoDB.TableDefs(RS![tablename])
tdef.Connect = "ODBC; DSN=" & RS![DSN] & "; DATABASE=" & RS!
[LinkedDB] & "; UID=" & RS![USER ID] & "; PWD=" & RS![CONNECTION PASSWORD]
tdef.RefreshLink
RS.MoveNext
Loop
RS.Close
fODBCTablesRefresh = True
Exit Function
refresh_err:
fODBCTablesRefresh = False
End Function
<end code>

Just call that when you need to refresh your table links. I do it with an
autoexec macro so they're refreshed every time the DB is launched.

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???

Walt

[quoted text clipped - 24 lines]