Connect and RefreshLink in TableDefs

[lss]

My .mdb has 2 Excel table links to spreadsheets that are running DDE links.
The data in the Excel tables update automatically and I would like to link
this into Acess tables.
I am using the following scripts..

CurrentDB.TableDefs("sometable").Connect
CurrentDB.TableDefs("sometable").RefreshLink

The script runs without failure but my table does not refresh.
Is there something I am missing in the VB script to make the refresh work?

 

[Alex Dybenko]

Hi,
perhaps you passing wrong connection string to connect property
look at this sample for more info:
http://www.mvps.org/access/tables/tbl0009.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[lss]

negative.
I have reviewed this program and it creates table for linking.
I have 2 tables already linked and my code verifies the names.
(I just sent the 2 lines for reference)
Furthermore,
When I open the tables in Access and manually move/click the cursor around
the screen, the tables update but I cannot get this to work through my code..?

Any help?

 

[lss]

I am assuming that the connection string should match the Table Name..?

 

[Dirk Goldgar]

My .mdb has 2 Excel table links to spreadsheets that are running DDE
links. The data in the Excel tables update automatically and I would
like to link this into Acess tables.
I am using the following scripts..

CurrentDB.TableDefs("sometable").Connect
CurrentDB.TableDefs("sometable").RefreshLink

The script runs without failure but my table does not refresh.
Is there something I am missing in the VB script to make the refresh
work?

The line

CurrentDB.TableDefs("sometable").Connect

, if that's really all there is to it, won't do anything. The Connect
property just sets or returns the connection string for the linked
table; it doesn't force a reconnect.

I'm not sure I understand exactly what the situation is, but your
mention of DDE links makes me think these spreadsheets are themselves
using DDE to collect data. Is that right? I have no experience with
linking to spreadsheets that do that, and no idea exactly how that works
in Access.

One thing you might try, instead of just refreshing the link, is
deleting it and recreating it. You might use a function similar to
this:

'----- start of code ------
Function RecreateLink(sTableName) As Boolean

' Returns True if the table link was successfully recreated,
' False if not.

' Note: this function will delete the linked table, then
' recreate it. If the link can't be recreated, the previous
' linked table is gone. Probably it would be better
' to rename the old linked table, then either delete it
' (if recreated successfully) or rename it back to the
' original name (if not).

On Error GoTo Err_Handler

Dim sConnect As String
Dim sTable As String

With CurrentDb

With .TableDefs(sTableName)
sConnect = .Connect
sTable = .SourceTableName
End With

.TableDefs.Delete sTableName

DoCmd.TransferSpreadsheet _
acLink, acSpreadsheetTypeExcel9, _
sTableName, _
Mid(sConnect, InStr(sConnect, "DATABASE=") + 9), _
IIf(InStr(sConnect, "HDR=YES") > 0, True, False), _
sTable

End With

RecreateLink = True

Exit Function

Err_Handler:
Debug.Print Err.Number, Err.Description
Exit Function

End Function
'----- end of code ------

I don't know if that will work or not, under the circumstances, but it
may be worth a try.