Appending linked tables

  • Thread starter Thread starter Rob Greatrex
  • Start date Start date
R

Rob Greatrex

I have a blank Front End (FE) mdb that uses a dialog box
to get the users nominated Back End (BE) mdb. What is the
code to append / link all the tables from this back end
database to this front end?
I have the dialog box in the FE collect the full path name
for the BE as a string called strBackEndLocation and it
takes the format for example
of "c:\access\data\projectA.mdb".
Is there a code version of the macro command
TransferDatabase?
 
There's an example at the following URL ...

http://www.mvps.org/access/tables/tbl0009.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan;

Thanks for the reply, but your referenced template deals
with refreshing tables when a mdb is moved and the links
to existing back end are broken whereas I am seeking in
this particular application to attach tables from scratch.
ie: the database table container is empty and with no
tables linked at all. The user then is allowed in the
Front End to pick from a list of available Back End mdb's
and this code will link all the tables in that back end.

I think that the code needed is some thing like :
DoCmd.TransferDatabase acLink, "Microsoft Access",
BEdatabase, acTable, BEtblName, FEtblName

How do you loop thru all the tables in the BEdatabase from
first to last and attach them with this command?

What are the precedent commands to set up the current FE
database and variables to make this docmd. work?

Thanks in advance.

Rob
 
Public Function LinkTables(ByVal strTheDataFile As String) As Long

Dim dbSource As DAO.Database
Dim tdfsSource As DAO.TableDefs
Dim tdfSource As DAO.TableDef

Dim lngLinks As Long

Set dbSource = DBEngine.OpenDatabase(strTheDataFile)
Set tdfsSource = dbSource.TableDefs
For Each tdfSource In tdfsSource
If UCase$(Left$(tdfSource.Name, 4)) <> "MSYS" Then
DoCmd.TransferDatabase acLink, "Microsoft Access",
strTheDataFile, acTable, tdfSource.Name, tdfSource.Name
lngLinks = lngLinks + 1
End If
Next tdfSource

Set tdfSource = Nothing
Set tdfsSource = Nothing
dbSource.Close
Set dbSource = Nothing

Application.RefreshDatabaseWindow

LinkTables = lngLinks

End Function

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan;

Thanks for the code. It works great.

As a final point, I would appreciate to also have visible
the "description" field attached to each table that is
liked.
eg: The db.tdfSource.Name might equal "T00034" and the
description / proporeties of this table in the back end
is "Customer table".

Due to the naming convention that we use for all tables,
the actual BE table name is only like "T000034" and we
rely on the description or properties part of the table
header to explain the table function.

So, when we relink the tables using the new code,
the 'description' bit is missing.

Any ideas on if it can also be linked?

Regards

Rob Greatrex
 
Public Function LinkTables(ByVal strTheDataFile As String) As Long

Dim dbSource As DAO.Database
Dim tdfsSource As DAO.TableDefs
Dim tdfSource As DAO.TableDef

Dim prp As DAO.Property

Dim lngLinks As Long

Set dbSource = DBEngine.OpenDatabase(strTheDataFile)
Set tdfsSource = dbSource.TableDefs
For Each tdfSource In tdfsSource
If UCase$(Left$(tdfSource.Name, 4)) <> "MSYS" Then
DoCmd.TransferDatabase acLink, "Microsoft Access",
strTheDataFile, acTable, tdfSource.Name, tdfSource.Name
Set prp = Nothing

'Quick'n'dirty sample code - production version needs more
robust error handling.
On Error Resume Next
Set prp = tdfSource.Properties("Description")
On Error GoTo 0
If Not prp Is Nothing Then

CurrentDb.TableDefs(tdfSource.Name).Properties("Description") = prp.Value
End If

lngLinks = lngLinks + 1
End If
Next tdfSource

Set tdfSource = Nothing
Set tdfsSource = Nothing
dbSource.Close
Set dbSource = Nothing

Application.RefreshDatabaseWindow

LinkTables = lngLinks

End Function


--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top