By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,650 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,650 IT Pros & Developers. It's quick & easy.

How to programmatically refresh linked tables

P: n/a
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 programmatically via VBA?

Thanks,
Bubbles

Apr 20 '07 #1

✓ answered by MMcCarthy

We've recently put together an article on this in the Insights section. Please check it out.

Relinking odbc tables using VBA

Share this Question
Share on Google+
25 Replies


P: n/a
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 programmatically 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
Apr 20 '07 #2

P: n/a
On Apr 20, 8:21 am, "(PeteCresswell)" <x...@y.Invalidwrote:
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 programmatically 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.

Apr 20 '07 #3

P: n/a
On Apr 19, 5:40 pm, bubbles <bubbles....@hotmail.comwrote:
On Apr 20, 8:21 am, "(PeteCresswell)" <x...@y.Invalidwrote:


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 programmatically 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 "RefreshLinks". 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 programmatically 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

Apr 20 '07 #4

P: n/a
I have made a few recent modifications to this (hence the "2" in
"fRelinkMultipleBackends2") 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 "cmdRefreshLinks" 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(tdf 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 fRelinkMultipleBackends2()
'--------------------------------------------------------------------
'Name: fRelinkMultipleBackends (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(MyDB.TableDefs(strTable),
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("Please select a new datasource
for: " & strTable)
Result = LinkOneTable(MyDB.TableDefs(strTable), 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 & "successfully re-linked."
MsgBox (Msg)
Set tdf = Nothing
Set MyDB = Nothing
DoCmd.Hourglass False
End Function

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

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

"bubbles" <bu*********@hotmail.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.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 programmatically via VBA?

Thanks,
Bubbles

Apr 20 '07 #5

P: n/a
On Apr 20, 9:39 am, eng...@ridesoft.com wrote:
On Apr 19, 5:40 pm, bubbles <bubbles....@hotmail.comwrote:


On Apr 20, 8:21 am, "(PeteCresswell)" <x...@y.Invalidwrote:
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 programmatically 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 "RefreshLinks". 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 programmatically 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

Apr 20 '07 #6

P: n/a
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
"fRelinkMultipleBackends2") 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 "cmdRefreshLinks" 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(tdf 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 fRelinkMultipleBackends2()
'--------------------------------------------------------------------
'Name: fRelinkMultipleBackends (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(MyDB.TableDefs(strTable),
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("Please select a new datasource
for: " & strTable)
Result = LinkOneTable(MyDB.TableDefs(strTable), 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 & "successfully re-linked."
MsgBox (Msg)

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

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

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

"bubbles" <bubbles....@hotmail.comwrote in message

news:11*********************@q75g2000hsh.googlegro ups.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 programmatically via VBA?
Thanks,
Bubbles- Hide quoted text -

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

Bubbles

Apr 20 '07 #7

P: n/a
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
Apr 21 '07 #8

P: n/a
On Apr 21, 9:28 am, "(PeteCresswell)" <x...@y.Invalidwrote:
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

Apr 24 '07 #9

P: n/a
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
Apr 24 '07 #10

P: n/a
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" <bu*********@hotmail.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
On Apr 20, 9:39 am, eng...@ridesoft.com wrote:
>On Apr 19, 5:40 pm, bubbles <bubbles....@hotmail.comwrote:


On Apr 20, 8:21 am, "(PeteCresswell)" <x...@y.Invalidwrote:
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 programmatically 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 "RefreshLinks". 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 programmatically 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

Apr 24 '07 #11

P: n/a
On Apr 24, 9:37 am, "(PeteCresswell)" <x...@y.Invalidwrote:
>
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

Apr 26 '07 #12

P: n/a
On Apr 24, 12:33 pm, "Larry Linson" <boun...@localhost.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

Apr 26 '07 #13

P: n/a
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
Apr 26 '07 #14

P: n/a
On 23 Apr 2007 17:29:30 -0700, bubbles <bu*********@hotmail.comwrote:
>On Apr 21, 9:28 am, "(PeteCresswell)" <x...@y.Invalidwrote:
>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
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
--
Apr 26 '07 #15

P: n/a
"bubbles" <bu*********@hotmail.comwrote
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.
If new products actually require new tables, then there are sufficient
differences that they are going to require different processing, so the
application is going to have to be changed to accomodate that processing.
If the differences are so small that the application should be able to
handle the new things without processing changes, the new products should
not require new tables -- implying that the design needs to be refined, so a
new product can be accomodated just by entering data in the appropriate,
existing tables.

As to "installing the application", there are approaches that automate
replacing the user's "front-end" or "client" application without any great,
or perhaps any at all, "installation" process. MVP Tony Toews' has a (free)
"Auto FE Updater" at his website http://www.granite.ab.ca/accsmstr.htm. I
wrote an article on "Versioning" that you'll find at
http://accdevel.tripod.com describing an approach we used on a client-server
application done with Access and Informix. Neither of these, of course,
addresses automated-determination-of-new-requirements,
automated-design-of-a-solution-for-the-new-requirements, and
automated-implementation-of-the-design; I'm reasonably sure that is 'way
past the State of the Art.

Larry Linson
Microsoft Access MVP
Apr 26 '07 #16

P: n/a
On Apr 27, 2:47 am, Chuck <libb...@schoollink.netwrote:
On 23 Apr 2007 17:29:30 -0700, bubbles <bubbles....@hotmail.comwrote:


On Apr 21, 9:28 am, "(PeteCresswell)" <x...@y.Invalidwrote:
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

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
--- Hide quoted text -

- Show quoted text -
No, users will not be expected to modify the design.

The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.

That is why I am trying to find a way to link these tables
programatically, without
user intervention.
Apr 27 '07 #17

P: n/a
Per bubbles:
>The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.

That is why I am trying to find a way to link these tables
programatically, without
user intervention.
Would it be correct to assume that the format of the new tables
is identical to the format of some table that the application is
already reading?

Like maybe there's a table for each product - with a common
"product information" format?
--
PeteCresswell
Apr 27 '07 #18

P: n/a
On 26 Apr 2007 22:15:59 -0700, bubbles <bu*********@hotmail.comwrote:
Big snip
>No, users will not be expected to modify the design.

The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.

That is why I am trying to find a way to link these tables
programatically, without
user intervention.
Do the new tables have the same number of fields with the same names and in the
same sequence as the existing tables?
Are the names of the new tables predictable?

Chuck
--
Apr 27 '07 #19

P: n/a
Hi Bubbles,

I've been watching this thread and keeping my mouth shut for the most part,
because I have ZERO experience with server-based databases.

This might be a totally rediculous question, but ...
Is there some kind of "object" or "container" for this SQL Server that you
can retrieve properties from?

I'm thinking along the lines of Tabledef objects ... could you iterate
through the list of them, comparing them to your list of linked tables? If a
new (non-existant in your existing collection ) table is found, run code to
link it?

Don

"Chuck" <li*****@schoollink.netwrote in message
news:vk********************************@4ax.com...
On 26 Apr 2007 22:15:59 -0700, bubbles <bu*********@hotmail.comwrote:
Big snip
>>No, users will not be expected to modify the design.

The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.

That is why I am trying to find a way to link these tables
programatically, without
user intervention.
Do the new tables have the same number of fields with the same names and
in the
same sequence as the existing tables?
Are the names of the new tables predictable?

Chuck
--

Apr 27 '07 #20

P: n/a
On Apr 27, 9:01 pm, "(PeteCresswell)" <x...@y.Invalidwrote:
Per bubbles:
The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.
That is why I am trying to find a way to link these tables
programatically, without
user intervention.

Would it be correct to assume that the format of the new tables
is identical to the format of some table that the application is
already reading?

Like maybe there's a table for each product - with a common
"product information" format?
--
PeteCresswell
Yes, that is correct.
Each new product will cause 9 different tables to be generated via
TSQL at the server.
Tables have similar structures as the other products.

Bubbles

May 4 '07 #21

P: n/a
On Apr 27, 9:38 pm, Chuck <libb...@schoollink.netwrote:
On 26 Apr 2007 22:15:59 -0700, bubbles <bubbles....@hotmail.comwrote:
Big snip>No, users will not be expected to modify the design.
The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.
That is why I am trying to find a way to link these tables
programatically, without
user intervention.

Do the new tables have the same number of fields with the same names and in the
same sequence as the existing tables?
Are the names of the new tables predictable?

Chuck
--
Yes, 9 different tables for each new product, but their structure will
be the same as all other products.

Bubbles

May 4 '07 #22

P: n/a
On Apr 27, 10:57 pm, "Don Leverton" <My.N...@Telus.Netwrote:
Hi Bubbles,

I've been watching this thread and keeping my mouth shut for the most part,
because I have ZERO experience with server-based databases.

This might be a totally rediculous question, but ...
Is there some kind of "object" or "container" for this SQL Server that you
can retrieve properties from?

I'm thinking along the lines of Tabledef objects ... could you iterate
through the list of them, comparing them to your list of linked tables? If a
new (non-existant in your existing collection ) table is found, run code to
link it?

Don

"Chuck" <libb...@schoollink.netwrote in message

news:vk********************************@4ax.com...
On 26 Apr 2007 22:15:59 -0700, bubbles <bubbles....@hotmail.comwrote:
Big snip
>No, users will not be expected to modify the design.
>The tables will be added dynamically at the SQL Server end when new
products
comes into existance -- this product info is pulled from another
remote server
periodically. When new products come into existance in the remote
server,
a TSQL statement will create the new tables.
>That is why I am trying to find a way to link these tables
programatically, without
user intervention.
Do the new tables have the same number of fields with the same names and
in the
same sequence as the existing tables?
Are the names of the new tables predictable?
Chuck
--- Hide quoted text -

- Show quoted text -
I've been thinking along the same line, but can't find a way to do it.

Bubbles

May 4 '07 #23

P: n/a
On 3 May 2007 17:50:56 -0700, bubbles <bu*********@hotmail.comwrote:
>>
Do the new tables have the same number of fields with the same names and in the
same sequence as the existing tables?
Are the names of the new tables predictable?

Chuck
--

Yes, 9 different tables for each new product, but their structure will
be the same as all other products.

Bubbles
Wow! 10 products, 90 tables!

First method:
Consider: table A1 thru A9, B1 thru B9 ...

Consider making 9 new tables:
Add a *table name* field to each table and a short *table description* field to
each existing tables.
Make table: Incliusive1 with table A1 and B1 appended and C1 appended ...
Make table: Inclusive2 with table A2 and B2 appended and C2 appended ...

Make an Index of all the tables currently in use.
Make VB program to check daily or hourly or whatever for tables not in index
and to append new tables to *Inclusive* tables and add new table name and
description to index.

Now there will be only the same 9 tables to link to the database.
You can use a combo box based on the index with *table name* and *table
description* to sort by in the queries.

This falls apart if tables size exceeds the limits imposed by Access.
Second method:
Make an Index of all the tables currently in use.
Make VB program to check daily or hourly or whatever for tables not in index
and to append new tables to *Inclusive* tables and add new table name and
description to index.

In Access make combo box based on the index to select which 9 tables to link.
This would require unlinking and liking 9 different tables each time you wanted
to work with different product.

Both methods are ham fisted. I'll bet there are better methods are possible.

Chuck
--
May 4 '07 #24

P: n/a
Per bubbles:
>Yes, that is correct.
Each new product will cause 9 different tables to be generated via
TSQL at the server.
Tables have similar structures as the other products.
How often do they come? Do old ones go away when new ones come
through?

Sounds like trouble to me. Dunno how many how many connections
Access supports - ODBC or other wise... but there's got to be a
limit.

You're probably tired of hearing people say you should change the
design (since, reading between the lines, it seems like it's just
one of those situations that one can't change and has to adapt
to)... but I'd have to join the chorus and say it sounds pretty
grim to me too.

Are the tables read-only to your app?

If so, how about something on the server side? Maybe a stored
procedure that gathers all the product tables together, assigns a
number to each product, and concats them into a common set of
tables?

It could run every night, and just replace what's there from the
previous night with a new updated copy.
--
PeteCresswell
May 4 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
We've recently put together an article on this in the Insights section. Please check it out.

Relinking odbc tables using VBA
Nov 20 '09 #26

This discussion thread is closed

Replies have been disabled for this discussion.