By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Don't link to system tables in backend

P: n/a
My front-end code manually links to the backend file.

I was wondering what stops Access from linking to the system tables in the
backend file. Is it just by virtue that they are hidden?

This has recently come up because I've been playing around with converting my
front end to A2003 format. At some point, I had a corruption issue with a
system file MSStorage something. Access kept saying it couldn't find it.
When I looked in the db Window I could see a link to it.

So I ran the code that deletes all links and then restarted my app. At
startup the app recreates all links. That solved the corruption problem.
But it got me thinking about the linking to backend system files.

Should I explicitly include VB Code that prevents linking to system tables
(and maybe other tables that I'm not aware of) contained in the backend.

Thanks.

--
Message posted via http://www.accessmonster.com

Aug 6 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
rdemyan via AccessMonster.com wrote:
My front-end code manually links to the backend file.

I was wondering what stops Access from linking to the system tables
in the backend file. Is it just by virtue that they are hidden?

This has recently come up because I've been playing around with
converting my front end to A2003 format. At some point, I had a
corruption issue with a system file MSStorage something. Access kept
saying it couldn't find it. When I looked in the db Window I could
see a link to it.

So I ran the code that deletes all links and then restarted my app.
At startup the app recreates all links. That solved the corruption
problem. But it got me thinking about the linking to backend system
files.

Should I explicitly include VB Code that prevents linking to system
tables (and maybe other tables that I'm not aware of) contained in
the backend.

Thanks.
I don't have it handy to check, but I'm pretty sure that the
auto-linking/refreshing code that is frequently recommended in these groups does
have logic to specifically skip the system tables.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 6 '06 #2

P: n/a
I looked at the relinking code on the mvps site and didn't see anything that
popped out at me. I'm only an intermediate level programmer so maybe I
missed it.

So checked the option to show hidden tables and system tables in my backend
file and then launched my front end. I then looked in the dbWindow on my
front end.

I can see the system tables "grayed out". However there are no arrows on the
left hand side indicating that they are linked, except for MSysAccessXML..
However, this system table has showed up as linked in my front end for months.
Even when I delete the link it reappears. It hasn't seemed to cause a
problem, yet.


Rick Brandt wrote:
>My front-end code manually links to the backend file.
[quoted text clipped - 17 lines]
>>
Thanks.

I don't have it handy to check, but I'm pretty sure that the
auto-linking/refreshing code that is frequently recommended in these groups does
have logic to specifically skip the system tables.
--
Message posted via http://www.accessmonster.com

Aug 6 '06 #3

P: n/a
rdemyan via AccessMonster.com wrote:
I looked at the relinking code on the mvps site and didn't see
anything that popped out at me. I'm only an intermediate level
programmer so maybe I missed it.

So checked the option to show hidden tables and system tables in my
backend file and then launched my front end. I then looked in the
dbWindow on my front end.

I can see the system tables "grayed out". However there are no
arrows on the left hand side indicating that they are linked, except
for MSysAccessXML.. However, this system table has showed up as
linked in my front end for months. Even when I delete the link it
reappears. It hasn't seemed to cause a problem, yet.
Okay I had a look to remind myself how it works. The code loops through and
recreates the links of existing linked tables. Therefore local tables (such as
the system tables) are automatically skipped.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 6 '06 #4

P: n/a
Okay I had a look to remind myself how it works. The code loops through and
recreates the links of existing linked tables. Therefore local tables (such as
the system tables) are automatically skipped.
How did it get linked then?

Aug 6 '06 #5

P: n/a
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>Okay I had a look to remind myself how it works. The code loops through and
recreates the links of existing linked tables. Therefore local tables (such
as
the system tables) are automatically skipped.

How did it get linked then?
How did what get linked? His system table? I have no idea. I don't know if we
have even established that he is using the same code. The code I use loops
through existing links skipping ODBC links. I'm not positive that it's based on
the code at the MVPS.Org site, but that is my recollection.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 6 '06 #6

P: n/a
Rick Brandt wrote:
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Okay I had a look to remind myself how it works. The code loops through and
recreates the links of existing linked tables. Therefore local tables (such
as
the system tables) are automatically skipped.
How did it get linked then?

How did what get linked? His system table? I have no idea.
In another thread he identified the linked table as MSysAccessStorage.

In Access 2003 (I believe the OP is using Access 2003) when I try to
link to MSysAccessStorage in another db, the linked table is named
MSysAccessStorage1 by default. I haven't been able to rename it to
MSysAccessStorage.

Aug 6 '06 #7

P: n/a
This damned MSysAccessStorage table keeps getting linked to my front end. I
don't know why. It's the only system table that keeps getting linked. I
delete it and then my code seems to be relinking it but no other system
tables.

Oh, and it's currently only happening with my app on one computer but not on
my other computer. Exact same front end??!

This file is causing an error to occur on startup.

Lyle Fairfield wrote:
>Okay I had a look to remind myself how it works. The code loops through and
recreates the links of existing linked tables. Therefore local tables (such
[quoted text clipped - 4 lines]
>>
How did what get linked? His system table? I have no idea.

In another thread he identified the linked table as MSysAccessStorage.

In Access 2003 (I believe the OP is using Access 2003) when I try to
link to MSysAccessStorage in another db, the linked table is named
MSysAccessStorage1 by default. I haven't been able to rename it to
MSysAccessStorage.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 7 '06 #8

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:6463ce9f7f344@uwe:
This damned MSysAccessStorage table keeps getting linked to my front
end. I don't know why. It's the only system table that keeps getting
linked. I delete it and then my code seems to be relinking it but no
other system tables.
and your code is?

--
Lyle Fairfield
Aug 7 '06 #9

P: n/a
A couple of notes:
1) I use a global error handler
2) My backends have all permissions removed so I use RWOP queries in the
front end.
3) I'm a beginning/intermediate programmer, so be gentle :)

Okay, here it is

Public Function RelinkToCurDir(PassedBEFile As String, FullPathProvided As
Boolean) As Boolean
'Note, when all permissions have been removed from the backend tables
'and with RWOP, relinking will cause an error and the code must use Resume
'Next. This is by design from Microsoft. When the error is ignored,
'then the relinking will work.
'3/22/2006 NOTE: Must use On Error Resume Next at the top of the code.
Trapping
'the error in an error handler and then using Resume Next does not work.
'THIS IS EXTREMELY IMPORTANT

On Error GoTo Err_Ctrl

Dim wsBACK As DAO.Workspace
Dim dbBACK As DAO.Database
Dim BEFile As String

Dim stDocName As String
Dim db As Database
Dim tdf As TableDef
Dim X As Boolean, TableToLink As String
BEFile = PassedBEFile
'NOTE: 3/25/2006: dbBACK needs to be included. If only db is used, then all
links are included
'(about 150) and are deleted each time as each backend file is linked. By
using dbBACK, we
'get the count of the tables from the backend file.

Set wsBACK = DBEngine.Workspaces(0)
Set dbBACK = wsBACK.OpenDatabase(BEFile)
Set db = CurrentDb()

On Error Resume Next

'Now link the tables
For k = dbBACK.TableDefs.Count - 1 To 0 Step -1

TableToLink = dbBACK.TableDefs(k).Name

'Delete the link if it already exists.
'Using the .Connect statement assures that only linked
'tables get deleted and not any front end tables

If fIsRemoteTable(TableToLink) Then
db.TableDefs.Delete TableToLink

Else
'Don't delete any front-end tables
'and Don't delete links that don't exist

End If

'Create the new link
Set tdf = db.CreateTableDef(TableToLink)

'Set the properties of the new link
'and append to the tabledefs collection
tdf.SourceTableName = TableToLink
tdf.Connect = ";DATABASE=" & BEFile
db.TableDefs.Append tdf

Next k

RelinkToCurDir = True
Exit_Function: On Error Resume Next

Set db = Nothing
Set dbBACK = Nothing
Set tdf = Nothing

Exit Function
Err_Ctrl:

'This is the error where the backend can't be found. This will cause a loop
error if not
'trapped here that takes about 30 to 50 cancels to stop.
If Err.Number = 3024 Then
Resume Exit_Function
ElseIf Err.Number = 3078 Then
Resume Exit_Function
End If

DoCmd.Hourglass False
errMsgStr = ""
ctrlfnctnm = "RelinkToCurDir"
Call StartupModule_err(Err.Number, Err.DESCRIPTION, Err.Source,
ctrlfnctnm, errMsgStr)
Resume Exit_Function
End Function

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++

Function fIsRemoteTable(strTbl As String) As Boolean

'Checks if a table is a linked table or this database table

Dim wsLOCAL As DAO.Workspace, dbLOCAL As DAO.Database, rsLOCAL As DAO.
Recordset
Dim rsLOCALFlag As Boolean
Dim strSQLLOCAL As String

On Error GoTo Exit_Sub

strSQLLOCAL = "SELECT Name FROM MSysObjects WHERE Type = 6 AND Name = '" &
strTbl & "';"
Set wsLOCAL = DBEngine.Workspaces(0)
Set dbLOCAL = wsLOCAL.OpenDatabase(CurrentProject.FullName)
Set rsLOCAL = dbLOCAL.OpenRecordset(strSQLLOCAL, dbOpenDynaset, dbSeeChanges)
rsLOCALFlag = True

If rsLOCAL.RecordCount = 0 Then
'Local Table or doesn't exist in table
fIsRemoteTable = False
Else
'Remote table
fIsRemoteTable = True
End If

Exit_Sub: On Error Resume Next

If rsLOCALFlag = True Then
rsLOCAL.Close
Set rsLOCAL = Nothing
dbLOCAL.Close
Set dbLOCAL = Nothing
Set wsLOCAL = Nothing
rsLOCALFlag = False
End If
DoCmd.SetWarnings True
Exit Function

Err_Ctrl:

DoCmd.Hourglass False

If Err.Number = 3265 Then
fIsRemoteTable = False
Resume Exit_Sub
End If

errMsgStr = ""
ctrlfnctnm = "fIsRemoteTable"
Call StartupModule_err(Err.Number, Err.DESCRIPTION, Err.Source,
ctrlfnctnm, errMsgStr)
Resume Exit_Sub
End Function


Lyle Fairfield wrote:
>This damned MSysAccessStorage table keeps getting linked to my front
end. I don't know why. It's the only system table that keeps getting
linked. I delete it and then my code seems to be relinking it but no
other system tables.

and your code is?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 7 '06 #10

P: n/a
3) I'm a beginning/intermediate programmer, so be gentle :)

OK.

1. Did you write this? This will have a bearing on the degree of
gentleness.

2. I know nothing of RWOP so someone who does will have to comment on
its impact on relinking.

3. Using On Error Resume Next to allow for a specific error will
conceal such other errors as exist.

4. You code starts in the BE to find the names of tables that should be
relinked. As I read it it will try to link the BE System Tables, but
should fail because a link to them will not be found in the FE. But
consider that in relation to # 3 above. Could the combination of the
two result in a link being made to a System Table in the BE? Well, it
seems that something does and for me Resume Next is the first thing
that jumps out at me.

5. IMO the simplest solution is that you discard this code and either
do a search for relinking code, or that you explain your situation
clearly here in CDMA and ask for suggestions.

Aug 7 '06 #11

P: n/a
Actually, the relinking code comes from Microsoft for linking tables where
all permissions are revoked (Security FAQ)

Yes, I hate the On Error Resume Next, but according to Microsoft Security FAQ,
it is the only way. As my notes state in the code I tried to use the error
handler but it failed. I've written a bunch of notes to remind myself of why
I did things, otherwise I forget and then waste time rewriting the code (that
I haven't looked at in months) and then ultimately have to go back to the
original.

Lyle Fairfield wrote:
>3) I'm a beginning/intermediate programmer, so be gentle :)

OK.

1. Did you write this? This will have a bearing on the degree of
gentleness.

2. I know nothing of RWOP so someone who does will have to comment on
its impact on relinking.

3. Using On Error Resume Next to allow for a specific error will
conceal such other errors as exist.

4. You code starts in the BE to find the names of tables that should be
relinked. As I read it it will try to link the BE System Tables, but
should fail because a link to them will not be found in the FE. But
consider that in relation to # 3 above. Could the combination of the
two result in a link being made to a System Table in the BE? Well, it
seems that something does and for me Resume Next is the first thing
that jumps out at me.

5. IMO the simplest solution is that you discard this code and either
do a search for relinking code, or that you explain your situation
clearly here in CDMA and ask for suggestions.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1

Aug 7 '06 #12

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:646565e6e0bb0@uwe:
Actually, the relinking code comes from Microsoft for linking tables
where all permissions are revoked (Security FAQ)
The article is not wonderfully clear; I am taking from what you say that
we must delete links and recreate them, ignoring errors, rather than
relink tables which are used by RWOP queries.

Surely someone can write simpler code than that (the code in the
article). IMO, Resume Next is not a great idea here unless it's a last
resort. Well even then it's not a great idea.

I hope there is someone reading this who uses RWOP and can create or
perhaps has already created code to relink tables used by RWOP queries.

How I would start is shown below. I am handicapped by not knowing the
error number which appears (which is why I'm munging by checking to see
if "permission" appears in the error description), and not having an
application with RWOP queries.

Just in case you decide to try this bear in mind, please,

a) it should be used only on a safe copy;
b) it's complete as it is but it must be called with the new backend path
as a parameter eg,
Sub test()
RelinkTables "C:\Documents and Settings\Lyle Fairfield\My Documents
\Access\northwind.mdb"
MsgBox "Done"
End Sub
;
c) I'm hoping it will motivate someone to post, "This is how it could be
done efficiently;
d) it may have a few undocumented methods (not sure, my code often does
but I haven't checked);
e) and that it will serve as a catalyst for the creation of a solution to
your System Table Link problem;
f) I've removed all indents to try to make it fit here but there may be
some lines split by the news clients and these will result in syntax
errors.

Sub RelinkTables(ByVal LinkTable As String)
Dim AttachedTables() As String
Dim CreateTableDef() As String
Dim Iterator As Long
Dim TableDefinition As dao.TableDef
Dim ThisDB As dao.Database
On Error GoTo ReLinkTablesErr
LinkTable = ";DATABASE=" & LinkTable
Set ThisDB = CurrentDb
AttachedTables = _
Split( _
CurrentProject.Connection.Execute( _
"SELECT Name, " _
& "ForeignName " _
& "FROM MSysObjects " _
& "WHERE Type = 6").GetString( _
adClipString, , ",", vbNewLine), vbNewLine)
With ThisDB
For Iterator = 0 To UBound(AttachedTables) - 1
CreateTableDef = Split(AttachedTables(Iterator), ",")
With .TableDefs
..Delete CreateTableDef(0)
..Refresh
Set TableDefinition = ThisDB.CreateTableDef(CreateTableDef(0))
With TableDefinition
..SourceTableName = CreateTableDef(1)
..Connect = LinkTable
End With
..Append TableDefinition
End With
Next Iterator
End With
ReLinkTablesExit:
Set ThisDB = Nothing
Exit Sub
ReLinkTablesErr:
With Err
Select Case .Number
Case 3021
MsgBox "No Linked Tables were found.", vbInformation, "Relinking Tables"
Resume ReLinkTablesExit
Case Else
If InStr(.Description, "Permission") <0 Then
Resume Next
Else
MsgBox .Description, vbInformation, "Relinking Tables Error # " & .Number
Resume ReLinkTablesExit
End If
End Select
End With
End Sub

--
Lyle Fairfield
Aug 7 '06 #13

P: n/a
"rdemyan via AccessMonster.com" <u6836@uwewrote in
news:646565e6e0bb0@uwe:
Actually, the relinking code comes from Microsoft for linking
tables where all permissions are revoked (Security FAQ)

Yes, I hate the On Error Resume Next, but according to Microsoft
Security FAQ, it is the only way. As my notes state in the code I
tried to use the error handler but it failed. I've written a
bunch of notes to remind myself of why I did things, otherwise I
forget and then waste time rewriting the code (that I haven't
looked at in months) and then ultimately have to go back to the
original.
Several points:

1. you can exclude system tables from any relinking by one of two
methods:

a. test the tabledef attributes for dbSystemObject (see the DAO
help for TableDefs.Attributes)

b. test if the table has a connect string -- if it does, it's an
attached table, if not, it's local.

2. don't use the tables in the back end as your source for what to
relink to. Simply use the existing linked tables in the front end.
That will prevent having to check the back end.

3. if the tables in the back end are available only to the owner,
then your users may be unable to relink. You may have to run your
relinking code in a separate workspace with a different user logon.

4. you might want to look at my relinking utility for databases with
multiple back ends:

http://www.dfenton.com/DFA/download/...Reconnect.html

It's not designed for use in apps with only one back end, but you
may find a look at the code useful in helping you understand how to
get the job done.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 7 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.