[AccessD] Connect String for Linked Table

[Jim Dettman]

Charlotte,

 Yes, that's exactly the method you need to use.

 I would say the same for checking for the existence of a tabledef and not
rely on looking at msysobjects.  The reason I say that is because the tables
are un-documented, so you never know what changes Microsoft might make in
them.  

 Msysobjects is pretty safe and a lot of people have used it however.  I've
never heard of anyone having a problem, but you are using a backdoor method.
Personally I've always used a method/property through the object model
rather than anything direct.  In this case I do:

Function DoesTblExist(strTblName As String) As Boolean

    Dim db As DAO.Database, tbl As DAO.TableDef

    On Error Resume Next

    Set db = CurDb(True)
    Set tbl = db.TableDefs(strTblName)

    If Err.Number = 3265 Then
        DoesTblExist = False
    Else
        DoesTblExist = True
    End If

End Function


 As for checking the link, I do what gustav suggested and attempt to open a
recordset on the table:


Function IsJETTableAttached(strDatabaseName As String, strTableName As
String) As Integer

    Dim rstTest As DAO.Recordset
    Dim intRet As Integer

    On Error Resume Next
    Err = 0

    Set rstTest = CurDb.OpenRecordset("SELECT * FROM " & strTableName & "
WHERE 1 = 0")
    
    If Err = 0 Then
        intRet = True
    Else
        intRet = RefreshJETTables(strDatabaseName)
    End If

    rstTest.Close
    Set rstTest = Nothing

    IsJETTableAttached = intRet
    
    If IsJETTableAttached = False Then
        Call ApplicationExit
    End If

End Function

 The WHERE 1 = 0 is a cheap way to return no records.   I also allow the
user to browse to the location of the BE in RefreshJETTables(), which is why
the logic is the way it is.  The BE database name is explicitly passed in
intentionally rather than relying on what's in the TableDefs connect string.


 Couple other points:

1. I'd check what's being passed to the routine as a table name when called
from code.  Looks to me like a problem with the call rather than the
routine.

2. CurrentDB() vs dbEngine(0)(0): CurrentDB() is Access's reference to the
database, dbengine is JET's.   Access's is the one you want to use as it
will always be up to date.   If you want to know a little more about all
that and a method that gives you the best of both worlds, give this a read:

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative
http://rdsrc.us/rO0m9j

3. Your existing routine could be simplified a bit:

Public Function DoesObjectExist(ByVal strObjName As String) As Boolean

    On Error Resume Next

    DoesObjectExist = DCount("*", "MSysObjects", "[Name] = '" & strObjName &
"'")>0

End Function

 Don't need the variable really and the count should use "*" instead of a
field name.  The asterisk has optimizations built-in to speed up the count.

 Both are very moot points given the task at hand, but it would be
fractionally faster.

HTH,
Jim.


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gustav Brock
Sent: Saturday, November 21, 2015 04:41 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connect String for Linked Table

Hi Charlotte

By second thought, I don't think you can check for a broken link other than
trying to open the table and catch an error.

A TableDef with a link doesn't go except if you delete it (why should you?).
Or it might never has been there - it's a new table required by a new
version of the application - and you would know that and have code that
creates this TableDef.

I just check one essential small table linked from the backend. If opening
it fails, I relink all tables of that backend.

/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Charlotte
Foust <charlotte.foust at gmail.com>
Sendt: 21. november 2015 00:02
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Connect String for Linked Table

Public Function DoesObjectExist(ByVal strObjName As String) As Boolean
    On Error Resume Next
    Dim intCnt As Integer

    intCnt = DCount("[Name]", "MSysObjects", "[Name] = '" & strObjName &
"'")
    DoesObjectExist = (intCnt > 0)
End Function

this *usually* returns true for a table with a broken link. Except,
sometimes it doesn't.  It does when I call it from the immediate window,
but when it's called from code, it may return a False.  If my hair weren't
already nearly white, it would be with this.

I'm also sometimes getting "item not found in this collection" when I try
to call the above routine from code.

Charlotte


Charlotte Foust
(916) 206-4336

On Fri, Nov 20, 2015 at 2:57 PM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Charlotte
>
> And your code is?
>
> /gustav
>
> ________________________________________
> Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Charlotte
> Foust <charlotte.foust at gmail.com>
> Sendt: 20. november 2015 23:15
> Til: Access Developers discussion and problem solving
> Emne: [AccessD] Connect String for Linked Table
>
> I'm working on a db built in 2010 on Windows 7 but I'm using 2013 on
> Windows 10.  I have code that worked just fine last year but is breaking
> this year because suddenly, if the back end file is moved or renamed, the
> code to set a tabledef object to the linked table in question can't find
> the linked table.  Obviously, the table still exists because the linked
> table manager can see it, so it's still in the system tables, but the code
> breaks.
>
> Any ideas?
>
> Charlotte Foust
> (916) 206-4336

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