Db Link via code

D

DDBeards

I have two environments; production and development. The production mdb has
33 linked tables from 7 different Backend Dbs located in 5 different folders
on our network. The development environment has the same number of tables
and Dbs but they are all found in the same local folder. After reading
several of the articles on how to relink via code I am still unsure of what
commands actually performs the link.

In preperation for coding that hopefully someone can help me with, I have
created a "UTbl_Link" Table that has 33 records in it with 4 fields:

qtbl contains the name of the table
qdb contains the name of the mdb
qProd contains the location of the mdb in production
qDev contains the locatio of the mdb in development

What I would like to have is a form that ask what linkage do you wish to set
the program to; "Production" or "Development". Based on the answer, I would
like the 33 tables refreshed and saved with the appropriate links as provided
in the "UTbl_Link" Table. Any ideas on how this could be accomplished?

Than k you in advance
Chris
 
K

Klatuu

All you really need in a table is a list of the connect strings for each be
database by production and development. It needs three fields:
tblBeList
TableName
ConnectString
ProdDev

Each table in your front end has a connect string. Local tables connect
strings are equal to vbNullString. Using the code below, you loop through the
TableDefs collection of the fe and pass the table name to the ReLink function
and it will undate the connect string to the correct database:

This code assumes you have a text box on your form that indicates whether
you want to link to production or development:


Private Function ReLink() As Boolean
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim intCount As Integer
strNewPath As String

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
strNewPath = DLookup("[ConnectString]", "tblBeList", _
"[TableName] = """ & tdf.Name & "" AND [ProdDev] = """ & _
Me.txtEnvironment & """")
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function
 
D

DDBeards

Thank you, I have made the change to my tbleBElist to the three fields you
listed where in the PrdDev field I put a "P" or "D". I have a form with a
text box (TxtDevProd) where I can type in the "P" or "D". But I do not see
in the code where it pulls that input or how it loops through the records in
the tbleBeList. The other two points are where is the function being called
from and second, you have a Me.cmdOK.Enabled = in your code but I cannot find
anyreference to it? Please explain.

Klatuu said:
All you really need in a table is a list of the connect strings for each be
database by production and development. It needs three fields:
tblBeList
TableName
ConnectString
ProdDev

Each table in your front end has a connect string. Local tables connect
strings are equal to vbNullString. Using the code below, you loop through the
TableDefs collection of the fe and pass the table name to the ReLink function
and it will undate the connect string to the correct database:

This code assumes you have a text box on your form that indicates whether
you want to link to production or development:


Private Function ReLink() As Boolean
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim intCount As Integer
strNewPath As String

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
strNewPath = DLookup("[ConnectString]", "tblBeList", _
"[TableName] = """ & tdf.Name & "" AND [ProdDev] = """ & _
Me.txtEnvironment & """")
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function

--
Dave Hargis, Microsoft Access MVP


DDBeards said:
I have two environments; production and development. The production mdb has
33 linked tables from 7 different Backend Dbs located in 5 different folders
on our network. The development environment has the same number of tables
and Dbs but they are all found in the same local folder. After reading
several of the articles on how to relink via code I am still unsure of what
commands actually performs the link.

In preperation for coding that hopefully someone can help me with, I have
created a "UTbl_Link" Table that has 33 records in it with 4 fields:

qtbl contains the name of the table
qdb contains the name of the mdb
qProd contains the location of the mdb in production
qDev contains the locatio of the mdb in development

What I would like to have is a form that ask what linkage do you wish to set
the program to; "Production" or "Development". Based on the answer, I would
like the 33 tables refreshed and saved with the appropriate links as provided
in the "UTbl_Link" Table. Any ideas on how this could be accomplished?

Than k you in advance
Chris
Click to expand...
Click to expand...
 
K

Klatuu

It does not loop through the table, it loops through the mdb's TableDefs
collection. It then gets the connect string from the table based on the
table's name and whether it is production or development using the DLookup.

You can remove the reference to the cmdOK. What I sent was a slightly
modified relink routine I wrote 9 years ago. I intended to remove references
specific to the app. I did leave the references to a label control because I
was too lasy to change them to msgbox statments <g>

As I said previously, it uses the tables you currently have linked to know
what to relink. If case you are not aware, the TableDefs collection contains
information on each table a mdb knows about. Notice in the code that if the
table has no connect string it means the table is not linked, but is a local
table. So this line:

If tdf.Connect <> "" Then

Causes local tables to be skipped in the relink process.
--
Dave Hargis, Microsoft Access MVP


DDBeards said:
Thank you, I have made the change to my tbleBElist to the three fields you
listed where in the PrdDev field I put a "P" or "D". I have a form with a
text box (TxtDevProd) where I can type in the "P" or "D". But I do not see
in the code where it pulls that input or how it loops through the records in
the tbleBeList. The other two points are where is the function being called
from and second, you have a Me.cmdOK.Enabled = in your code but I cannot find
anyreference to it? Please explain.

Klatuu said:
All you really need in a table is a list of the connect strings for each be
database by production and development. It needs three fields:
tblBeList
TableName
ConnectString
ProdDev

Each table in your front end has a connect string. Local tables connect
strings are equal to vbNullString. Using the code below, you loop through the
TableDefs collection of the fe and pass the table name to the ReLink function
and it will undate the connect string to the correct database:

This code assumes you have a text box on your form that indicates whether
you want to link to production or development:


Private Function ReLink() As Boolean
Dim dbs As DAO.Database
Dim tdf As TableDef
Dim intCount As Integer
strNewPath As String

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
strNewPath = DLookup("[ConnectString]", "tblBeList", _
"[TableName] = """ & tdf.Name & "" AND [ProdDev] = """ & _
Me.txtEnvironment & """")
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function

--
Dave Hargis, Microsoft Access MVP


DDBeards said:
I have two environments; production and development. The production mdb has
33 linked tables from 7 different Backend Dbs located in 5 different folders
on our network. The development environment has the same number of tables
and Dbs but they are all found in the same local folder. After reading
several of the articles on how to relink via code I am still unsure of what
commands actually performs the link.

In preperation for coding that hopefully someone can help me with, I have
created a "UTbl_Link" Table that has 33 records in it with 4 fields:

qtbl contains the name of the table
qdb contains the name of the mdb
qProd contains the location of the mdb in production
qDev contains the locatio of the mdb in development

What I would like to have is a form that ask what linkage do you wish to set
the program to; "Production" or "Development". Based on the answer, I would
like the 33 tables refreshed and saved with the appropriate links as provided
in the "UTbl_Link" Table. Any ideas on how this could be accomplished?

Than k you in advance
Chris
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Db Link via code (Klatuu Help) 2
Link Table Mgr Update 1
Link to tables via VBA 2
How to connect to a BE database? 0
Relink Tables From Multiple Sources? 2
use code to link to external database 4
Split Database Development 3
refreshing or relinking tables by code 2

Top