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

programatically linking tables

P: n/a
Tym
OK - daft question of the day time...

If I have database A which contains all the live data, and Database B
which contains linked tables to those is A (i.e. a front end) is there
a way of seeing if any tables have been addedd to A and if so,
programatically creating a linked table to the new tables?

Failing this, can I programatically delete all linked tables, then
link re-link to every table which appears in tableA?

Does that make sense??

Tym
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
To add a table to the current database, try modifying this to meet your
requirements. Look through the help file for more info

Dim tbl As TableDef
Dim db As Database

Set db = CurrentDb
Set tbl = db.CreateTableDef(tblName, dbAttachedTable, SourceName,
ConnectionString)
db.TableDefs.Append tbl

To remove one

db.TableDefs.Delete tblName

Mike Storr
www.veraccess.com
"Tym" <no*****@ictis.net> wrote in message
news:40*************@169.100.100.5...
OK - daft question of the day time...

If I have database A which contains all the live data, and Database B
which contains linked tables to those is A (i.e. a front end) is there
a way of seeing if any tables have been addedd to A and if so,
programatically creating a linked table to the new tables?

Failing this, can I programatically delete all linked tables, then
link re-link to every table which appears in tableA?

Does that make sense??

Tym



Nov 12 '05 #2

P: n/a
DFS
Here's a non-programmatic way to see the differences:

SELECT A.Name AS TableName
FROM MSYSOBJECTS A IN 'path to back-end database A'
WHERE
A.TYPE = 1
AND LEFT(A.NAME,4) <> 'MSYS'
AND A.NAME NOT IN
(SELECT B.Name AS TableName
FROM MSYSOBJECTS B IN 'path to linked database B'
WHERE
B.TYPE = 6
AND LEFT(B.NAME,4) <> 'MSYS'
);

To return valid results, this query requires/assumes your link names in
database B match the table names in A.

You can programmatically link tables - investigate the Connect and
RefreshLink methods.


"Tym" <no*****@ictis.net> wrote in message
news:40*************@169.100.100.5...
OK - daft question of the day time...

If I have database A which contains all the live data, and Database B
which contains linked tables to those is A (i.e. a front end) is there
a way of seeing if any tables have been addedd to A and if so,
programatically creating a linked table to the new tables?

Failing this, can I programatically delete all linked tables, then
link re-link to every table which appears in tableA?

Does that make sense??

Tym

Nov 12 '05 #3

P: n/a
"Tym" <no*****@ictis.net> wrote in message
news:40*************@169.100.100.5...
OK - daft question of the day time...

If I have database A which contains all the live data, and Database B
which contains linked tables to those is A (i.e. a front end) is there
a way of seeing if any tables have been addedd to A and if so,
programatically creating a linked table to the new tables?

Failing this, can I programatically delete all linked tables, then
link re-link to every table which appears in tableA?

Does that make sense??

Tym

Why would you want to do that?
Do you think users are deleting linked tables from the front end?
Are you adding tables to the back end which you want to be automatically
included as linked tables in the front end?
Normally, if there are back end changes, then there are associated front end
changes. E.g. you add a new table called tblCustomerNotes then typically
there will be new or amended forms and reports to display this data.
Getting the front end to simply generate a new linked table for this will
generally not be enough.
However, if you need some coding ideas, these functions may help:

Public Function GetBackEndPath() As String

On Error GoTo Err_Handler

' Assumes there is a single back-end .mdb
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
GetBackEndPath = Mid$(tdf.Connect, 11)
Exit For
End If
Next tdf

Exit_Handler:
On Error Resume Next
Set tdf = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:
' No error message
Resume Exit_Handler

End Function

Public Function DeleteLinks() As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim lng As Long

Set dbs = CurrentDb

With dbs.TableDefs
For lng = .Count - 1 To 0 Step -1
If (.Item(lng).Attributes And dbAttachedTable) <> 0 Then
.Delete .Item(lng).Name
End If
Next lng
End With

DeleteLinks = True

Exit_Handler:
On Error Resume Next
Set dbs = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
Public Function CreateLinks(strBEPath) As Boolean

On Error GoTo Err_Handler

Dim dbsFE As DAO.Database
Dim dbsBE As DAO.Database
Dim wksJET As DAO.Workspace
Dim strTableName As String
Dim strConnect As String
Dim tdfBE As DAO.TableDef
Dim tdfFE As DAO.TableDef

Set wksJET = DBEngine.Workspaces(0)

Set dbsBE = wksJET.OpenDatabase(strBEPath)

Set dbsFE = CurrentDb

For Each tdfBE In dbsBE.TableDefs

If Left$(tdfBE.Name, 4) <> "MSys" And _
Len(tdfBE.Connect) = 0 Then

strTableName = tdfBE.Name
strConnect = ";DATABASE=" & strBEPath
Set tdfFE = dbsFE.CreateTableDef(strTableName)
tdfFE.Connect = strConnect
tdfFE.SourceTableName = strTableName
dbsFE.TableDefs.Append tdfFE
Set tdfFE = Nothing

End If

Next tdfBE

CreateLinks = True

Exit_Handler:
On Error Resume Next
Set tdfFE = Nothing
Set tdfBE = Nothing
Set dbsFE = Nothing
dbsBE.Close
Set dbsBE = Nothing
Set wksJET = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.