Refresh link to table

[Guest]

I have three tables that are linked to excel spreadsheets. For each month
there will be three spreadsheets and I want the user to click on a button to
relink to the appropriate one. What is the best way to accomplish this?

I tried this, which will create a new table. However, I need to keep the
tables linked because of their relationship.

Dim strFilter As String
Dim strInputFileName As String
Dim strDefaultDir As String
Dim strTableName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
strTableName = Forms!frmMain!cboTablesImport
'Get the File Name
strDefaultDir = "C:/"
Me.Repaint
If strInputFileName <> "" Then 'User Clicked CANCEL
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
strTableName, strInputFileName, True
End If

 

[Michel Walsh]

Hi,


CurrentDb.TableDefs("tableName").Connect

is a read/write string that defines the "link". See help file for details.

Writing in it is not enough to "shake" the system up to date, you need to
explicitly use a

CurrentDb.TableDefs("tableName").RefreshLink

to force the new connection described in the Connect property, to
re-establish the link. You should try to trap errors, in case the refresh
would fail.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

This gave me a compile error of invalid use of property for .Connect. Will
these two lines of code you gave me be put in the current code under "if
strInputname..."? Should "tablename" be switched out with strTableName that
refers to the name found in the combobox?

 

[Michel Walsh]

Hi,


What is your "connect" string, something like:


CurrentDb.TableDefs("MyExcel1").Connect = "Excel
5.0;HDR=NO;IMEX=2;DATABASE=C:\My Documents\MyExcelFieldName.xls"



And about where to do it, clearly, you have to do it after the excel file,
here MyExcelFieldName.xls, exists in the said folder; and after the Access
link-table, here MyExcel1, exists too in the database.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Here is exactly how I got it so far:

Private Sub cmdImportFromExcel_Click()

Dim strFilter As String
Dim strInputFileName As String
Dim strDefaultDir As String
Dim strTableName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
strTableName = Forms!frmMain!cboTablesImport
'Get the File Name
strDefaultDir = "C:/"
Me.Repaint
If strInputFileName <> "" Then 'User Clicked CANCEL
CurrentDb.TableDefs(strTableName).Connect
CurrentDb.TableDefs(strTableName).RefreshLink
End If

End Sub

When you talk about "connect" string I do not quite understand.

 

[Michel Walsh]

Hi,


You have a LINKED table? From the menu File | Get External Data |
Link Tables...

Any such table remembers where is the data, since it is not in Access,
through its Connect string. IF you already have such a table, say TableXL,
then, in the immediate debug window, you can type:


? CurrentDb.TableDefs("TableXL").Connect


You can CHANGE the said source of data changing that string
appropriately. If only the file name change (it is still Excel you use as
data source), then change whatever come after the DATABASE=



Dim i As long
Dim strConnect As String

strConnect=CurrentDb.TableDefs(strTableName)
i = InStr(strConnect, "DATABASE=")
strConnect= Left(strConnect, i+8) & strFullPathFileName

On Error Resume Next
CurrentDb.TableDefs(strTableName)=strConnect
CurrentDb.TableDefs(strTableName).RefreshLink

If 0 <> Err.Number then
... refresh didn't work
End if
On Error Goto 0



Here I assumed DATABASE= ... is the last piece of information in the
Connect string.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Yes, the table was originally linked by File | Get External Data | Link
Tables. Now I need to update the links for three tables when the user clicks
on a button "Link". Some users are not that familiar with Access and I want
to make this as easy as possible for them by automating the process. Is this
the easiest way to do this by the code you gave me? By the way I got some
error messages when I ran the following code:

Private Sub cmdImportFromExcel_Click()

Dim i As Long
Dim strConnect As String
Dim strTableName As String
Dim strFilter As String
Dim strInputFileName As String

'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

'Pick from combobox selected
strTableName = Forms!frmMain!cboTablesImport

strConnect = CurrentDb.TableDefs(strTableName)
i = InStr(strConnect, "DATABASE=")
strConnect = Left(strConnect, i + 8) & strTableName

On Error Resume Next
CurrentDb.TableDefs(strTableName) = strConnect
CurrentDb.TableDefs(strTableName).RefreshLink

If 0 <> Err.Number Then
'exit
End If
On Error GoTo 0
End sub

Since the code does not working I am wondering if I am completely
misunderstanding your prior comments or am I on the right track?

 

[Michel Walsh]

Hi,


What error you got?


Vanderghast, Access MVP

 

[Guest]

Thanks for stay with me here. However, I got what I was looking for now by
this one line of code:

RunCommand acCmdLinkedTableManager