How to programmatically refresh linked tables

[bubbles]

Using Access 2003 front-end, with SQL Server 2005 backend.

I need to make the front-end application automatically refresh the
linked
SQL Server tables.

New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).

Anyone knows of a way to refresh the links programmaticall y via VBA?

Thanks,
Bubbles

[(PeteCresswell)]

Re: How to programmaticall y refresh linked tables

Per bubbles:

>New tables will be added dynamically in the future, so the front-end
>application
>must have a way to keep up with this (instead of manually linking
>them).
>
>Anyone knows of a way to refresh the links programmaticall y via VBA?

Sure. It's bread-and-butter stuff.

Do you mean links will come and go from hour-to-hour and/or user-to-user
depending on what's going on in the code?

Or will links just be added incrementally over time?
--
PeteCresswell

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 20, 8:21 am, "(PeteCresswell )" <x...@y.Invalid wrote:

Per bubbles:
>

New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).

>

Anyone knows of a way to refresh the links programmaticall y via VBA?

>
Sure. It's bread-and-butter stuff.
>
Do you mean links will come and go from hour-to-hour and/or user-to-user
depending on what's going on in the code?
>
Or will links just be added incrementally over time?
--
PeteCresswell

Links will be added over time.

[engles@ridesoft.com]

Re: How to programmaticall y refresh linked tables

On Apr 19, 5:40 pm, bubbles <bubbles....@ho tmail.comwrote:

On Apr 20, 8:21 am, "(PeteCresswell )" <x...@y.Invalid wrote:
>
>
>
>
>

Per bubbles:

>

>New tables will be added dynamically in the future, so the front-end
>application
>must have a way to keep up with this (instead of manually linking
>them).

>

>Anyone knows of a way to refresh the links programmaticall y via VBA?

>

Sure. It's bread-and-butter stuff.

>

Do you mean links will come and go from hour-to-hour and/or user-to-user
depending on what's going on in the code?

>

Or will links just be added incrementally over time?
--
PeteCresswell

>
Links will be added over time.- Hide quoted text -
>
- Show quoted text -

Look up help for "RefreshLin ks". That should get you started for
refreshing the already linked tables. It is important to keep the
definitions synchronized. I normally put this in the startup each
time the application opens. You can get much more sophisticated that
just that call, but if you are not switching the linked tables, the
help info should be all you need.

As for programmaticall y adding links, that gets pretty complicated.
It can be done tho, at least to an Access backend. My preferred
method has been to just add the link manually when new tables occur
and distribute the new front-end. I've not explored connecting to new
tables in SQL Server via code, so someone else will have to advise on
that aspect.

-- Larry Engles
Access developer since day 1 of Access 1.0

[Don Leverton]

Re: How to programmaticall y refresh linked tables

I have made a few recent modifications to this (hence the "2" in
"fRelinkMultipl eBackends2") and have not finished "cleaning up" the code.
Hopefully you won't find it TOO confusing.

The intended purpose of this app is to switch between about 10
identically-structured "back-ends" (one for each customer's parts inventory
control) as it was getting very difficult to maintain form and report
updates for each customer.

When you click on the "cmdRefreshLink s" button on the switchboard, a
BrowseFile dialog box pops up and allows you to choose a file for use as the
"back-end". Once the file is selected, the code continues and re-attaches
about 13 (linked) tables from the customer's specific file. I also had one
"pricing" file that never changes ... so I excluded it from having to be
refreshed for no reason. The code now runs continuously until completion ...
which takes about 10 seconds. :)

'--------------------------------------------------------------------
Function LinkOneTable(td f As TableDef, MyPath As String) As Boolean
'Debug.Print "Attempting to re-link " & tdf.Name
On Error Resume Next
' If the Connect property is non-empty, the table is linked
If Len(tdf.Connect ) 0 Then
tdf.Connect = ";DATABASE= " & MyPath
Err.Clear
tdf.RefreshLink ' Re-link the table.
If Err Then
LinkOneTable = False ' This attempt to re-link has failed.
Exit Function
End If
End If
Set tdf = Nothing
LinkOneTable = True ' This link has been succesfully refreshed.
End Function


Public Function fRelinkMultiple Backends2()
'--------------------------------------------------------------------
'Name: fRelinkMultiple Backends (Function)
'Purpose: Re-links attached tables on a
' one-by-one basis, deals with locating
' 'lost' MDB file links.
'Author: Don Leverton
'Date: July 31, 2004, 09:46:28 PM
'Called by: cmdRefreshLinks _Click() on Switchboard form
'Calls: LinkOneTable function
'Inputs: None
'Output: Message that confirms / informs
'Requires: Dev's fGetMDBName() function and GetOpenFileName API from:
' http://www.mvps.org/access/tables/tbl0009.htm
'Thanks to: Tom van Stiphout, Douglas J. Steele and Dev Ashish
'-------------------------------------------------


Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim tdf As DAO.TableDef


Dim intLinkedCount As Integer
Dim intSuccessCount As Integer


Dim strNewPath As String
Dim strTable As String
Dim Result As Boolean


Dim Msg As String
Dim CR As String
CR = vbCrLf
DoCmd.Hourglass True
On Error Resume Next


' Loop through all tables in database.
For Each tdf In MyDB.TableDefs


If InStr(1, tdf.Name, "tblPricing ") 0 Then
'"tblPricing " is from a data path that never changes.
' This code excludes it from being processed.
intSuccessCount = intSuccessCount + 1
intLinkedCount = intLinkedCount + 1
GoTo GetNext
End If


If Len(tdf.Connect ) 0 Then ' If the Connect property is non-empty,
the Table Is linked
intLinkedCount = intLinkedCount + 1 'Get a count of linked tables
strTable = tdf.Name 'Get the linked table name
' On Error Resume Next


' tdf.RefreshLink 'Attempt to relink table using existing .Connect
property


' If Err.Number <0 Then 'If RefreshLink fails...
If Len(strNewPath) 0 Then
'Try to re-use the existing string if it has already
been Found
Result = LinkOneTable(My DB.TableDefs(st rTable),
strNewPath)
If Result = True Then 'The re-linking of the table was
successful
intSuccessCount = intSuccessCount + 1
GoTo GetNext
Else
GoTo GetPath
End If '(for Result = True)


End If '(for Len(strNewPath) 0)
GetPath:
Msg = ""
Msg = Chr(39) & strTable & Chr(39)
Msg = Msg & " needs to re-linked " & CR
Msg = Msg & "to it's 'back-end' MDB file" & CR & CR
Msg = Msg & "Please select it's location " & CR
Msg = Msg & "from the next dialog box."
MsgBox (Msg)


strNewPath = fGetMDBName("Pl ease select a new datasource
for: " & strTable)
Result = LinkOneTable(My DB.TableDefs(st rTable), strNewPath)


' Else
intSuccessCount = intSuccessCount + 1 'RefreshLink was
successful
' End If '(for Err <>0)


End If '(for Len tdf)


GetNext:
Next tdf


MyDB.TableDefs. Refresh


Msg = ""
Msg = Msg & intSuccessCount & " of "
Msg = Msg & intLinkedCount & CR
Msg = Msg & "linked tables have been " & CR
Msg = Msg & "successful ly re-linked."
MsgBox (Msg)


Set tdf = Nothing
Set MyDB = Nothing
DoCmd.Hourglass False
End Function

=============== ==

HTH,
Don
=============== ==

"bubbles" <bubbles.one@ho tmail.comwrote in message
news:1177028023 .911526.75110@q 75g2000hsh.goog legroups.com...

Using Access 2003 front-end, with SQL Server 2005 backend.
>
I need to make the front-end application automatically refresh the
linked
SQL Server tables.
>
New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).
>
Anyone knows of a way to refresh the links programmaticall y via VBA?
>
Thanks,
Bubbles
>

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 20, 9:39 am, eng...@ridesoft .com wrote:

On Apr 19, 5:40 pm, bubbles <bubbles....@ho tmail.comwrote:
>
>
>
>
>

On Apr 20, 8:21 am, "(PeteCresswell )" <x...@y.Invalid wrote:

>

Per bubbles:

>

New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).

>

Anyone knows of a way to refresh the links programmaticall y via VBA?

>

Sure. It's bread-and-butter stuff.

>

Do you mean links will come and go from hour-to-hour and/or user-to-user
depending on what's going on in the code?

>

Or will links just be added incrementally over time?
--
PeteCresswell

>

Links will be added over time.- Hide quoted text -

>

- Show quoted text -

>
Look up help for "RefreshLin ks". That should get you started for
refreshing the already linked tables. It is important to keep the
definitions synchronized. I normally put this in the startup each
time the application opens. You can get much more sophisticated that
just that call, but if you are not switching the linked tables, the
help info should be all you need.
>
As for programmaticall y adding links, that gets pretty complicated.
It can be done tho, at least to an Access backend. My preferred
method has been to just add the link manually when new tables occur
and distribute the new front-end. I've not explored connecting to new
tables in SQL Server via code, so someone else will have to advise on
that aspect.
>
-- Larry Engles
Access developer since day 1 of Access 1.0- Hide quoted text -
>
- Show quoted text -

Thanks.
The Access Help and VBA Help aren't quite that helpful on this
subject.
I could not find anything on linking new tables... only refreshing the
link
to existing linked tables.

Bubbles

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 20, 10:33 am, "Don Leverton" <My.N...@Telus. Netwrote:

I have made a few recent modifications to this (hence the "2" in
"fRelinkMultipl eBackends2") and have not finished "cleaning up" the code.
Hopefully you won't find it TOO confusing.
>
The intended purpose of this app is to switch between about 10
identically-structured "back-ends" (one for each customer's parts inventory
control) as it was getting very difficult to maintain form and report
updates for each customer.
>
When you click on the "cmdRefreshLink s" button on the switchboard, a
BrowseFile dialog box pops up and allows you to choose a file for use as the
"back-end". Once the file is selected, the code continues and re-attaches
about 13 (linked) tables from the customer's specific file. I also had one
"pricing" file that never changes ... so I excluded it from having to be
refreshed for no reason. The code now runs continuously until completion ...
which takes about 10 seconds. :)
>
'--------------------------------------------------------------------
Function LinkOneTable(td f As TableDef, MyPath As String) As Boolean
'Debug.Print "Attempting to re-link " & tdf.Name
On Error Resume Next
' If the Connect property is non-empty, the table is linked
If Len(tdf.Connect ) 0 Then
tdf.Connect = ";DATABASE= " & MyPath
Err.Clear
tdf.RefreshLink ' Re-link the table.
If Err Then
LinkOneTable = False ' This attempt to re-link has failed.
Exit Function
End If
End If
Set tdf = Nothing
LinkOneTable = True ' This link has been succesfully refreshed.
End Function
>
Public Function fRelinkMultiple Backends2()
'--------------------------------------------------------------------
'Name: fRelinkMultiple Backends (Function)
'Purpose: Re-links attached tables on a
' one-by-one basis, deals with locating
' 'lost' MDB file links.
'Author: Don Leverton
'Date: July 31, 2004, 09:46:28 PM
'Called by: cmdRefreshLinks _Click() on Switchboard form
'Calls: LinkOneTable function
'Inputs: None
'Output: Message that confirms / informs
'Requires: Dev's fGetMDBName() function and GetOpenFileName API from:
' http://www.mvps.org/access/tables/tbl0009.htm
'Thanks to: Tom van Stiphout, Douglas J. Steele and Dev Ashish
'-------------------------------------------------
>
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim tdf As DAO.TableDef
>
Dim intLinkedCount As Integer
Dim intSuccessCount As Integer
>
Dim strNewPath As String
Dim strTable As String
Dim Result As Boolean
>
Dim Msg As String
Dim CR As String
CR = vbCrLf
DoCmd.Hourglass True
On Error Resume Next
>
' Loop through all tables in database.
For Each tdf In MyDB.TableDefs
>
If InStr(1, tdf.Name, "tblPricing ") 0 Then
'"tblPricing " is from a data path that never changes.
' This code excludes it from being processed.
intSuccessCount = intSuccessCount + 1
intLinkedCount = intLinkedCount + 1
GoTo GetNext
End If
>
If Len(tdf.Connect ) 0 Then ' If the Connect property is non-empty,
the Table Is linked
intLinkedCount = intLinkedCount + 1 'Get a count of linked tables
strTable = tdf.Name 'Get the linked table name
' On Error Resume Next
>
' tdf.RefreshLink 'Attempt to relink table using existing .Connect
property
>
' If Err.Number <0 Then 'If RefreshLink fails...
If Len(strNewPath) 0 Then
'Try to re-use the existing string if it has already
been Found
Result = LinkOneTable(My DB.TableDefs(st rTable),
strNewPath)
If Result = True Then 'The re-linking of the table was
successful
intSuccessCount = intSuccessCount + 1
GoTo GetNext
Else
GoTo GetPath
End If '(for Result = True)
>
End If '(for Len(strNewPath) 0)
GetPath:
Msg = ""
Msg = Chr(39) & strTable & Chr(39)
Msg = Msg & " needs to re-linked " & CR
Msg = Msg & "to it's 'back-end' MDB file" & CR & CR
Msg = Msg & "Please select it's location " & CR
Msg = Msg & "from the next dialog box."
MsgBox (Msg)
>
strNewPath = fGetMDBName("Pl ease select a new datasource
for: " & strTable)
Result = LinkOneTable(My DB.TableDefs(st rTable), strNewPath)
>
' Else
intSuccessCount = intSuccessCount + 1 'RefreshLink was
successful
' End If '(for Err <>0)
>
End If '(for Len tdf)
>
GetNext:
Next tdf
>
MyDB.TableDefs. Refresh
>
Msg = ""
Msg = Msg & intSuccessCount & " of "
Msg = Msg & intLinkedCount & CR
Msg = Msg & "linked tables have been " & CR
Msg = Msg & "successful ly re-linked."
MsgBox (Msg)
>
Set tdf = Nothing
Set MyDB = Nothing
DoCmd.Hourglass False
End Function
>
=============== ==
>
HTH,
Don
=============== ==
>
"bubbles" <bubbles....@ho tmail.comwrote in message
>
news:1177028023 .911526.75110@q 75g2000hsh.goog legroups.com...
>
>
>

Using Access 2003 front-end, with SQL Server 2005 backend.

>

I need to make the front-end application automatically refresh the
linked
SQL Server tables.

>

New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).

>

Anyone knows of a way to refresh the links programmaticall y via VBA?

>

Thanks,
Bubbles- Hide quoted text -

>
- Show quoted text -

Thanks.
I'll study the above, then <attemptto try it out.
:-)

Bubbles

[(PeteCresswell)]

Re: How to programmaticall y refresh linked tables

Per bubbles:

>
>Links will be added over time.

Then your original statement of the situation raises a flag with me.

How are the users executing the application in question?

Does each user execute their own copy of the app on their own PC? Or are they
hitting the same copy concurrently over the LAN?
--
PeteCresswell

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 21, 9:28 am, "(PeteCresswell )" <x...@y.Invalid wrote:

Per bubbles:
>
>
>

Links will be added over time.

>
Then your original statement of the situation raises a flag with me.
>
How are the users executing the application in question?
>
Does each user execute their own copy of the app on their own PC? Or are they
hitting the same copy concurrently over the LAN?
--
PeteCresswell

1. There will be new tables to be linked over time.
2. Each user has their own copy of the app on their PC.

The app need to be able to add new links when the new tables appear
(due to the launching of new products).

I need to find a way to automatically link these new tables as they
come into existance.

Bubbles

[(PeteCresswell)]

Re: How to programmaticall y refresh linked tables

Per bubbles:

>
>I need to find a way to automatically link these new tables as they
>come into existance.

Another approach would be to automatically download the latest
version of the app if/when changes are made to it.

In that case, the icon on the user's desktop would point to a
..BAT file or a little shell of a VB app that does the deed if it
needs doing - and then starts MS Access and the app on the user's
PC.
--
PeteCresswell

[Larry Linson]

Re: How to programmaticall y refresh linked tables

In Access 2.0, using ODBC drivers to the then-current release of Informix,
we found that RefreshLink did not work with the server, so when we changed
our linked tables from the Development DB to the Production DB, we had to
delete the old TableDef and create a new one. That same code worked in
Access 97 with a then-current version of Microsoft SQL Server, so we used it
there, as well. I really don't remember if anyone tried "Refresh Links"
with the ODBC-MSSQL configuration, or if we just used what we knew had
worked.

Larry Linson
Microsoft Access MVP


"bubbles" <bubbles.one@ho tmail.comwrote in message
news:1177047118 .076560.112840@ o5g2000hsb.goog legroups.com...

On Apr 20, 9:39 am, eng...@ridesoft .com wrote:

>On Apr 19, 5:40 pm, bubbles <bubbles....@ho tmail.comwrote:
>>
>>
>>
>>
>>

On Apr 20, 8:21 am, "(PeteCresswell )" <x...@y.Invalid wrote:

>>

Per bubbles:

>>

New tables will be added dynamically in the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).

>>

Anyone knows of a way to refresh the links programmaticall y via VBA?

>>

Sure. It's bread-and-butter stuff.

>>

Do you mean links will come and go from hour-to-hour and/or
user-to-user
depending on what's going on in the code?

>>

Or will links just be added incrementally over time?
--
PeteCresswell

>>

Links will be added over time.- Hide quoted text -

>>

- Show quoted text -

>>
>Look up help for "RefreshLin ks". That should get you started for
>refreshing the already linked tables. It is important to keep the
>definitions synchronized. I normally put this in the startup each
>time the application opens. You can get much more sophisticated that
>just that call, but if you are not switching the linked tables, the
>help info should be all you need.
>>
>As for programmaticall y adding links, that gets pretty complicated.
>It can be done tho, at least to an Access backend. My preferred
>method has been to just add the link manually when new tables occur
>and distribute the new front-end. I've not explored connecting to new
>tables in SQL Server via code, so someone else will have to advise on
>that aspect.
>>
>-- Larry Engles
> Access developer since day 1 of Access 1.0- Hide quoted text -
>>
>- Show quoted text -

>
Thanks.
The Access Help and VBA Help aren't quite that helpful on this
subject.
I could not find anything on linking new tables... only refreshing the
link
to existing linked tables.
>
Bubbles
>

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 24, 9:37 am, "(PeteCresswell )" <x...@y.Invalid wrote:

>
Another approach would be to automatically download the latest
version of the app if/when changes are made to it.
>
In that case, the icon on the user's desktop would point to a
.BAT file or a little shell of a VB app that does the deed if it
needs doing - and then starts MS Access and the app on the user's
PC.
--
PeteCresswell

That would be fine if I am working in-house.

Problem is, I'm a contractor and will be gone after the app is
installed.
Also, no one knows when new products (and hence new tables) come
into existance.

And - more importantly - the client does not want to have to re-
install
the app. Too many users.

Bubbles

[bubbles]

Re: How to programmaticall y refresh linked tables

On Apr 24, 12:33 pm, "Larry Linson" <boun...@localh ost.notwrote:

In Access 2.0, using ODBC drivers to the then-current release of Informix,
we found that RefreshLink did not work with the server, so when we changed
our linked tables from the Development DB to the Production DB, we had to
delete the old TableDef and create a new one. That same code worked in
Access 97 with a then-current version of Microsoft SQL Server, so we used it
there, as well. I really don't remember if anyone tried "Refresh Links"
with the ODBC-MSSQL configuration, or if we just used what we knew had
worked.
>
Larry Linson
Microsoft Access MVP
>

I found that RefreshLinks only refreshes those table that are already
linked.
New tables in the server are ignored.

Bubbles

[(PeteCresswell)]

Re: How to programmaticall y refresh linked tables

Per bubbles:

>And - more importantly - the client does not want to have to re-
>install
>the app. Too many users.

That's the advantage of a .BAT file or a shell program: No manual
installs. The flip side - that you probably sense - is an
additional point of possible failure/layer of maintenance.
--
PeteCresswell

[Chuck]

Re: How to programmaticall y refresh linked tables

On 23 Apr 2007 17:29:30 -0700, bubbles <bubbles.one@ho tmail.comwrote:

>On Apr 21, 9:28 am, "(PeteCresswell )" <x...@y.Invalid wrote:

>Per bubbles:
>>
>>
>>

>Links will be added over time.

>>
>Then your original statement of the situation raises a flag with me.
>>
>How are the users executing the application in question?
>>
>Does each user execute their own copy of the app on their own PC? Or are they
>hitting the same copy concurrently over the LAN?
>--
>PeteCresswel l

>
>
>1. There will be new tables to be linked over time.
>2. Each user has their own copy of the app on their PC.
>
>The app need to be able to add new links when the new tables appear
>(due to the launching of new products).
>
>I need to find a way to automatically link these new tables as they
>come into existance.
>
>Bubbles

Do I understand correctly? A customer wants you to design a database program
such that after the design of the program is completed, the customer can make
changes that neither of you know about at this time, and have the original
program find the changes, make necessary modifications or add new:
relationships, queries and reports as may be required - all automatically. I
hope the customer is paying you a kings ransom for this job.

If I understang correctly, in order to make the sort of change the costumer
wants, the 20 or so users must all be running mdb programs. I would be
concerned about the users *tweaking* their individual copies of the program and
possibly doing damage to the back end.

Chuck
--