This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C1EB83.43C34B80
Content-Type: text/plain;
	charset="iso-8859-1"

I have a access db that uses SQL tables and views  that are linked and I
would like to be able to verify those links when the db opens.  Does anyone
know how I can verify the links and relink the tables if necessary?
 
Thanks,
Robert

I know that there are routines that do what you want in FMS Total
SourceBook

I have seen a routine to verify all the links in a database (cycles thr
ough
the collection of tables and finds attached tables then tests the link)
 and
a routine to re-link or refresh the link.

I think I am alright to give you some sample code but I must recommend 
you
buy the product - see www.fmsinc.com and check it out

TestLinkedTables - tests the links of all the linked tables in the name
d
database

Public Function TestLinkedTables() As Boolean
  ' Comments  : Tests the links of all the Linked tables in the named
database
  ' Parameters: None
  ' Returns   : True - Tables are correctly linked,
  '             False - One or more tables contain errors
  ' Source    : Total Visual SourceBook 2000
  '
  Dim tdfTmp As Dao.TableDef
  Dim varTmp As Variant
  Dim fError As Boolean
  Dim lngErrorNumber As Long

  On Error GoTo PROC_ERR

  fError =3D False

  ' Look through each table in the database
  For Each tdfTmp In m_dbsCurrent.TableDefs

    ' See if its a linked table
    If tdfTmp.Connect <> "" Then
      ' Try and get the first field's name. If the table's link is brok
en,
      ' this will generate a runtime error.
      On Error Resume Next
      varTmp =3D tdfTmp.Fields(0).Name
      lngErrorNumber =3D Err.number
      ' Resume Error handling
      On Error GoTo PROC_ERR
      ' Did an error occur?
      fError =3D (lngErrorNumber <> 0)

      ' Error, so drop out of the loop
      If fError Then
        Exit For
      End If

    End If

  Next tdfTmp

  TestLinkedTables =3D Not fError

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "TestLinkedTables"
  Resume PROC_EXIT

End Function

ReLinkTable - relinks a named table

 Public Function ReLinkTable(strTable As String, strPath As String) As
Boolean
  ' Comments  : Re-links the named table to the named path
  ' Parameters: strTable - Table name of the linked table
  '             strPath - Path name of the database containing the real

table
  ' Returns   : True if successful, False otherwise
  ' Source    : Total Visual SourceBook 2000
  '
  Dim tdfTmp As Dao.TableDef
  Dim strPrefix As String
  Dim strNewConnect As String

  On Error GoTo PROC_ERR

  ' Get a handle to the table's defintion
  Set tdfTmp =3D m_dbsCurrent.TableDefs(strTable)

  ' Only try to relink if it is already a linked table
  If tdfTmp.Connect <> "" Then
    ' Build the new connect string
    strPrefix =3D Left$(tdfTmp.Connect, InStr(tdfTmp.Connect, "=3D"))
    strNewConnect =3D strPrefix & strPath

    ' Relink the linked table
    tdfTmp.Connect =3D strNewConnect
    tdfTmp.RefreshLink

    ReLinkTable =3D True
  Else
    ReLinkTable =3D False
  End If

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
    "ReLinkTable"
  Resume PROC_EXIT

End Function

Hope this helps - but buy the product if you can - I am only a user , I
 do
not work for them!
There is code to re-link ALL linked tables but you may wish to check fo
r
invalid links and then re-link

Joe Dunn




                                                                       
                   
                    "Railey,                                           
                   
                    Robert"                To:     "Access" <access@p...
.wrox.com>         
                    <RRailey@P...        cc:                         
                   
                    ISORS.NET>             Subject:     [access] Verify
 SQL linked tables?
                                                                       
                   
                    24/04/2002                                         
                   
                    11:29                                              
                   
                    Please respond                                     
                   
                    to "Access"                                        
                   
                                                                       
                   
                                                                       
                   





I have a access db that uses SQL tables and  views=A0 that are linked a
nd I
would like to be able to verify those links  when the db opens.=A0 Does

anyone know how I can verify the links and relink  the tables if necess
ary?

Thanks,
Robert





*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Write a function that does a "read" (SELECT) query from each linked 
table.  Any errors (or a specific error-code) would signal a possible bad 
link.  You could return the tablename that got the error from the 
function.  Call the function from AutoOpen or some other event that runs 
when the MDB opens.

R

I have a access db that uses SQL tables and views  that are linked and I
would like to be able to verify those links when the db opens.  Does 
anyone
know how I can verify the links and relink the tables if necessary?
 
Thanks,
Robert