[AccessD] Connect String for Linked Table
Jim Dettman
jimdettman at verizon.net
Sat Nov 21 11:04:53 CST 2015
If you can work up a small sample db, feel free to forward and I'll have a
look.
Would probably be tomorrow though before I get to it; one last ride on the
bike before the snow flies, mowers need to go away, and the snow blower
needs to come out<g>.
Jim.
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Charlotte Foust
Sent: Saturday, November 21, 2015 11:42 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connect String for Linked Table
Thanks Jim,
I've already done all those things and I can step through the code and
watch it fail. I just can't find a reason why. I tried setting a
tabledef variable to the object using the current access methods. When that
failed, I tried checking to see if the table existed. I started off simple
and got more complicated as I tried to determine why something built-in
should fail. Originally, I just set the variable to the tabledef object so
I could read its connection property. That didn't work because access
insisted the item wasn't found in tabledefs. This is driving me around me
around the bend.
Charlotte
On Nov 21, 2015 4:26 AM, "Jim Dettman" <jimdettman at verizon.net> wrote:
> 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
>
>
> --
> 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
More information about the AccessD
mailing list