[AccessD] linked table trouble

Pedro,  
Here is our function.  Mind you that while I have made modifications to the
code, I am not the original author of this, so the style does not match my
style.  LOL.

Bobby

start of code ----------------------------

Public Function RelinkEnterpriseTables(lstrEnterprise As String) As Boolean
Dim strEnterpriseDB       As String
Dim AString               As String
  
Dim tdf                  As TableDef
Dim strTdfName           As String
Dim strTableDefName      As String
Dim strEnterpriseConnect As String
Dim strDBConnect         As String
Dim dbsCurrent           As Database
Dim arrTableNames(83)    As String
Dim i                    As Integer
Dim intresponse          As Integer
Dim strMsg               As String
  
  On Error GoTo Proc_Err
  DoCmd.Hourglass True
  
  ' Static tables connection string
  strDBConnect = ";Database=" & lstrEnterprise & ";pwd=" & SECUREPW
  
  Set dbsCurrent = CurrentDb()
  
  arrTableNames(0) = "CertIssuance"
  arrTableNames(1) = "CertDefaults"
  arrTableNames(2) = "SheaLots"
  arrTableNames(3) = "SheaPlanElv"
  arrTableNames(4) = "SheaConTemp"
'<snip>
   arrTableNames(82) = "DeleteLog"
   
  With dbsCurrent
    For i = 0 To (UBound(arrTableNames) - 1)
        strTableDefName = arrTableNames(i)
        
        If .TableDefs(strTableDefName).Connect <> strDBConnect Then
            ' Change link
            .TableDefs.Delete strTableDefName
            Set tdf = .CreateTableDef(strTableDefName)
            With tdf
                .SourceTableName = strTableDefName
                .Connect = strDBConnect
             End With
            .TableDefs.Append tdf
        End If
    Next
    .TableDefs.REFRESH
  End With

  On Error GoTo 0

  RelinkEnterpriseTables = True
  DoCmd.Hourglass False

RelinkEnterpriseTables_exit:
   On Error Resume Next
   Set tdf = Nothing
   dbsCurrent.Close
   Set dbsCurrent = Nothing
   Exit Function

Proc_Err:
    Select Case Err.Number
        Case 3265   'Linked table not found.  Re-establish link.
            Resume Next
        Case 3011   'Table not found in target database
            Select Case strTableDefName
                Case arrTableNames(0)
                    strMsg = "Enterprise [" & lstrEnterprise & "] is missing
the <snip>.  " & _
				"Do you want to add these tables to this
enterprise?"
                    intresponse = MsgBox(strMsg, vbQuestion + vbYesNo)
                    If intresponse = vbYes Then
                        If AddCertIssuanceTable(strTableDefName, dbsCurrent,
lstrEnterprise) Then
                            Resume Next
                        End If
                    Else
                        RelinkEnterpriseTables = False
                        Resume RelinkEnterpriseTables_exit
                    End If
                Case Else
            
                    strMsg = "Table [" & strTableDefName & "] in database ["
& lstrEnterprise _
                            & "] could not be found."
                    MsgBox strMsg, vbExclamation
                    RelinkEnterpriseTables = False
                    Resume RelinkEnterpriseTables_exit
            End Select
        Case Else
            MsgBox "The following error occured: " & Err.Number & ":  " &
Err.Description
            RelinkEnterpriseTables = False
            Resume RelinkEnterpriseTables_exit
    End Select
    
    Resume Next

End Function


'end of code ------------------------------------------------------

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
Sent: Monday, February 07, 2005 4:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] linked table trouble


Hello Bobby,

could you provide me the code, or do you use another methode.

Pedro Janssen



----- Original Message -----
From: "Bobby Heid" <bheid at appdevgrp.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Thursday, February 03, 2005 9:05 PM
Subject: RE: [AccessD] linked table trouble


> Way back in ACC97, we had issues with links.  So in our app, we just 
> programmatically relink every time.
>
> Bobby
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte 
> Foust
> Sent: Thursday, February 03, 2005 2:39 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] linked table trouble
>
>
> I don't have an answer for you, but I've heard that complaint several
times
> in other forums too.
>
> Charlotte Foust
>
>
> -----Original Message-----
> From: Pedro Janssen [mailto:pedro at plex.nl]
> Sent: Thursday, February 03, 2005 11:08 AM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] linked table trouble
>
>
> Hello Group,
>
> in the past we never had any troubles with linked tables. Since whe 
> are using A2003 the link between the databases and tables must
be
> renewed (with the linked Tabel manager) very often, in some computers 
> each morning. How is this possible? Is there a solution?
>
> Pedro Janssen
> --
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com