|
|
I'm not sure this is where to post this question, but here it is anyhow.
Somebody who was working with me a while ago came up with the following code
to relink back end files. I don't know if it is adapted from the code at
the mvps web site, or what exactly. Tables may be in more than one BE
database. In particular, the Employee table is used by a number of
applications. I don't want to present the user with any options, as most
users wouldn't know what to do if presented with something like the Linked
Table Manager, or for that matter if asked whether they want to relink the
tables. Rather, I want the code to run invisibly on startup. The BE files
will not move, and I have used UNC paths to the BE files.
I have used the code below, which I call in the startup form's Load event,
and have not had a problem with users not being able to get at the data.
Debug.Print lists the tables and the paths correctly. I wonder, though, if
I am leaving out something, as other code I have seen for relinking the BE
files seems to be rather more complex than this.
Public Function RelinkBE() As Boolean
On Error GoTo ProcError
Dim tdf As TableDef
Dim strMsg As String, strCall As String
strCall = vbCrLf & "Contact tech support."
RelinkBE = True
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
ProcExit:
Exit Function
ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" & strCall
End Select
MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False
Resume ProcExit
End Function
|
|
Hi Bruce
This code is not really doing anything useful at all. If the local table is
present, and the Connect string is valid, then the table is already linked.
For relinking code to be useful, it needs to be able to fix problems where
the tables are *not* already correctly linked. This means it must be able
to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > I'm not sure this is where to post this question, but here it is anyhow.
> Somebody who was working with me a while ago came up with the following
> code to relink back end files. I don't know if it is adapted from the
> code at the mvps web site, or what exactly. Tables may be in more than
> one BE database. In particular, the Employee table is used by a number of
> applications. I don't want to present the user with any options, as most
> users wouldn't know what to do if presented with something like the Linked
> Table Manager, or for that matter if asked whether they want to relink the
> tables. Rather, I want the code to run invisibly on startup. The BE
> files will not move, and I have used UNC paths to the BE files.
>
> I have used the code below, which I call in the startup form's Load event,
> and have not had a problem with users not being able to get at the data.
> Debug.Print lists the tables and the paths correctly. I wonder, though,
> if I am leaving out something, as other code I have seen for relinking the
> BE files seems to be rather more complex than this.
>
> Public Function RelinkBE() As Boolean
>
> On Error GoTo ProcError
>
> Dim tdf As TableDef
> Dim strMsg As String, strCall As String
>
> strCall = vbCrLf & "Contact tech support."
>
> RelinkBE = True
>
> For Each tdf In CurrentDb.TableDefs
> If Len(tdf.Connect) > 0 Then
> Debug.Print tdf.Name & " " & tdf.Connect
> tdf.RefreshLink
> End If
> Next tdf
> Set tdf = Nothing
>
> ProcExit:
> Exit Function
>
> ProcError:
> Select Case Err.Number
> Case 3011 'Bad Table Name
> strMsg = "Table Not Found." & strCall
> Case 3024 'File Name Not Found
> strMsg = "Database File Name Not Found." & strCall
> Case 3044 'Path Not found
> strMsg = "Database Path Not Found." & strCall
> Case Else
> strMsg = "Error " & Err.Number & " (" & Err.Description & _
> ") in function ReLinkBE of Module mdlRelink" & strCall
> End Select
>
> MsgBox strMsg, vbExclamation, "Call Support"
> RelinkBE = False
>
> Resume ProcExit
>
> End Function
|
|
OK, thanks for the information. I have seen code, such as at the mvps web
site, to refresh table links, but message boxes asking the user if they want
to refresh the links, etc. are out of the question. The only thing I want
is for a message box to appear if there is a problem. I am still trying to
sort out how to use the code without message boxes, at least not routine
ones that appear every time the application starts. Users will simply click
through such messages. I probably would do so myself for messages that
appear every time.
Will the code I posted at least generate a message if there is a problem?
It seems to me it will. Perhaps an error could cause an otherwise invisible
command button to appear, and the mvps refresh links code could be run from
the command button as needed.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
[Quoted Text] > Hi Bruce
>
> This code is not really doing anything useful at all. If the local table
> is present, and the Connect string is valid, then the table is already
> linked.
>
> For relinking code to be useful, it needs to be able to fix problems where
> the tables are *not* already correctly linked. This means it must be able
> to:
> 1. read (from an INI file or somewhere) the correct location of the
> backend database(s)
> 2. browse for the backend database(s) if they don't exist
> 3. check that the local tables are connected to the correct backends
> 4. relink them if they are not
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>> I'm not sure this is where to post this question, but here it is anyhow.
>> Somebody who was working with me a while ago came up with the following
>> code to relink back end files. I don't know if it is adapted from the
>> code at the mvps web site, or what exactly. Tables may be in more than
>> one BE database. In particular, the Employee table is used by a number
>> of applications. I don't want to present the user with any options, as
>> most users wouldn't know what to do if presented with something like the
>> Linked Table Manager, or for that matter if asked whether they want to
>> relink the tables. Rather, I want the code to run invisibly on startup.
>> The BE files will not move, and I have used UNC paths to the BE files.
>>
>> I have used the code below, which I call in the startup form's Load
>> event, and have not had a problem with users not being able to get at the
>> data. Debug.Print lists the tables and the paths correctly. I wonder,
>> though, if I am leaving out something, as other code I have seen for
>> relinking the BE files seems to be rather more complex than this.
>>
>> Public Function RelinkBE() As Boolean
>>
>> On Error GoTo ProcError
>>
>> Dim tdf As TableDef
>> Dim strMsg As String, strCall As String
>>
>> strCall = vbCrLf & "Contact tech support."
>>
>> RelinkBE = True
>>
>> For Each tdf In CurrentDb.TableDefs
>> If Len(tdf.Connect) > 0 Then
>> Debug.Print tdf.Name & " " & tdf.Connect
>> tdf.RefreshLink
>> End If
>> Next tdf
>> Set tdf = Nothing
>>
>> ProcExit:
>> Exit Function
>>
>> ProcError:
>> Select Case Err.Number
>> Case 3011 'Bad Table Name
>> strMsg = "Table Not Found." & strCall
>> Case 3024 'File Name Not Found
>> strMsg = "Database File Name Not Found." & strCall
>> Case 3044 'Path Not found
>> strMsg = "Database Path Not Found." & strCall
>> Case Else
>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>> ") in function ReLinkBE of Module mdlRelink" & strCall
>> End Select
>>
>> MsgBox strMsg, vbExclamation, "Call Support"
>> RelinkBE = False
>>
>> Resume ProcExit
>>
>> End Function
>
>
|
|
Hi Bruce
Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the option
of doing nothing and asking for support, or attempting to find the correct
backend themselves.
One trap to watch for is that a user will often browse to the *frontend* and
choose that, instead of the backend. Of course, the frontend does contain
tables with all the correct names, so a link to these tables can be created,
but it will not work because it is circular! So always check, after a user
has selected a backend file, that it is not CurrentDb.Name.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > OK, thanks for the information. I have seen code, such as at the mvps web
> site, to refresh table links, but message boxes asking the user if they
> want to refresh the links, etc. are out of the question. The only thing I
> want is for a message box to appear if there is a problem. I am still
> trying to sort out how to use the code without message boxes, at least not
> routine ones that appear every time the application starts. Users will
> simply click through such messages. I probably would do so myself for
> messages that appear every time.
> Will the code I posted at least generate a message if there is a problem?
> It seems to me it will. Perhaps an error could cause an otherwise
> invisible command button to appear, and the mvps refresh links code could
> be run from the command button as needed.
>
> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>> Hi Bruce
>>
>> This code is not really doing anything useful at all. If the local table
>> is present, and the Connect string is valid, then the table is already
>> linked.
>>
>> For relinking code to be useful, it needs to be able to fix problems
>> where the tables are *not* already correctly linked. This means it must
>> be able to:
>> 1. read (from an INI file or somewhere) the correct location of the
>> backend database(s)
>> 2. browse for the backend database(s) if they don't exist
>> 3. check that the local tables are connected to the correct backends
>> 4. relink them if they are not
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>> I'm not sure this is where to post this question, but here it is anyhow.
>>> Somebody who was working with me a while ago came up with the following
>>> code to relink back end files. I don't know if it is adapted from the
>>> code at the mvps web site, or what exactly. Tables may be in more than
>>> one BE database. In particular, the Employee table is used by a number
>>> of applications. I don't want to present the user with any options, as
>>> most users wouldn't know what to do if presented with something like the
>>> Linked Table Manager, or for that matter if asked whether they want to
>>> relink the tables. Rather, I want the code to run invisibly on startup.
>>> The BE files will not move, and I have used UNC paths to the BE files.
>>>
>>> I have used the code below, which I call in the startup form's Load
>>> event, and have not had a problem with users not being able to get at
>>> the data. Debug.Print lists the tables and the paths correctly. I
>>> wonder, though, if I am leaving out something, as other code I have seen
>>> for relinking the BE files seems to be rather more complex than this.
>>>
>>> Public Function RelinkBE() As Boolean
>>>
>>> On Error GoTo ProcError
>>>
>>> Dim tdf As TableDef
>>> Dim strMsg As String, strCall As String
>>>
>>> strCall = vbCrLf & "Contact tech support."
>>>
>>> RelinkBE = True
>>>
>>> For Each tdf In CurrentDb.TableDefs
>>> If Len(tdf.Connect) > 0 Then
>>> Debug.Print tdf.Name & " " & tdf.Connect
>>> tdf.RefreshLink
>>> End If
>>> Next tdf
>>> Set tdf = Nothing
>>>
>>> ProcExit:
>>> Exit Function
>>>
>>> ProcError:
>>> Select Case Err.Number
>>> Case 3011 'Bad Table Name
>>> strMsg = "Table Not Found." & strCall
>>> Case 3024 'File Name Not Found
>>> strMsg = "Database File Name Not Found." & strCall
>>> Case 3044 'Path Not found
>>> strMsg = "Database Path Not Found." & strCall
>>> Case Else
>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>> ") in function ReLinkBE of Module mdlRelink" &
>>> strCall
>>> End Select
>>>
>>> MsgBox strMsg, vbExclamation, "Call Support"
>>> RelinkBE = False
>>>
>>> Resume ProcExit
>>>
>>> End Function
>>
>>
>
|
|
Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before that
happens the user is presented with two message boxes, both with the safe
option (don't undo) highlighted. Several users have clicked through the
non-default choice on both message boxes without looking at either one, then
complained bitterly to me about how my database destroyed their work. Some
of these folks are brilliant in their fields, and make sound choices on
matters affecting the lives and safety of others, but I would not be
confident in their ability to relink a table. They need to call me or
somebody else in the know, if I can locate and train such a person.
I have discovered a version of the mvps code that a helper (computer science
major) devised a while ago. It uses fewer message boxes (for instance, it
doesn't ask if they want to refresh the table links, since presumably anyone
opening the database means to use the data). I think I could also either
present the users with more information about how to relink, or direct them
to further documentation on the network. With a split database they're not
likely to do any harm by relinking incorrectly, so maybe I worry more than I
need to.
Anyhow, thanks for the input.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:1F676185-CFF8-450F-A1D9-66A16D58CEBA[ at ]microsoft.com...
[Quoted Text] > Hi Bruce
>
> Yes, the code you posted will raise an error if one of the tables is not
> already correctly linked. I suppose you could then give the user the
> option of doing nothing and asking for support, or attempting to find the
> correct backend themselves.
>
> One trap to watch for is that a user will often browse to the *frontend*
> and choose that, instead of the backend. Of course, the frontend does
> contain tables with all the correct names, so a link to these tables can
> be created, but it will not work because it is circular! So always check,
> after a user has selected a backend file, that it is not CurrentDb.Name.
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
> news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
>> OK, thanks for the information. I have seen code, such as at the mvps
>> web site, to refresh table links, but message boxes asking the user if
>> they want to refresh the links, etc. are out of the question. The only
>> thing I want is for a message box to appear if there is a problem. I am
>> still trying to sort out how to use the code without message boxes, at
>> least not routine ones that appear every time the application starts.
>> Users will simply click through such messages. I probably would do so
>> myself for messages that appear every time.
>> Will the code I posted at least generate a message if there is a problem?
>> It seems to me it will. Perhaps an error could cause an otherwise
>> invisible command button to appear, and the mvps refresh links code could
>> be run from the command button as needed.
>>
>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>>> Hi Bruce
>>>
>>> This code is not really doing anything useful at all. If the local
>>> table is present, and the Connect string is valid, then the table is
>>> already linked.
>>>
>>> For relinking code to be useful, it needs to be able to fix problems
>>> where the tables are *not* already correctly linked. This means it must
>>> be able to:
>>> 1. read (from an INI file or somewhere) the correct location of the
>>> backend database(s)
>>> 2. browse for the backend database(s) if they don't exist
>>> 3. check that the local tables are connected to the correct backends
>>> 4. relink them if they are not
>>> --
>>> Good Luck :-)
>>>
>>> Graham Mandeno [Access MVP]
>>> Auckland, New Zealand
>>>
>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>>> I'm not sure this is where to post this question, but here it is
>>>> anyhow. Somebody who was working with me a while ago came up with the
>>>> following code to relink back end files. I don't know if it is adapted
>>>> from the code at the mvps web site, or what exactly. Tables may be in
>>>> more than one BE database. In particular, the Employee table is used
>>>> by a number of applications. I don't want to present the user with any
>>>> options, as most users wouldn't know what to do if presented with
>>>> something like the Linked Table Manager, or for that matter if asked
>>>> whether they want to relink the tables. Rather, I want the code to run
>>>> invisibly on startup. The BE files will not move, and I have used UNC
>>>> paths to the BE files.
>>>>
>>>> I have used the code below, which I call in the startup form's Load
>>>> event, and have not had a problem with users not being able to get at
>>>> the data. Debug.Print lists the tables and the paths correctly. I
>>>> wonder, though, if I am leaving out something, as other code I have
>>>> seen for relinking the BE files seems to be rather more complex than
>>>> this.
>>>>
>>>> Public Function RelinkBE() As Boolean
>>>>
>>>> On Error GoTo ProcError
>>>>
>>>> Dim tdf As TableDef
>>>> Dim strMsg As String, strCall As String
>>>>
>>>> strCall = vbCrLf & "Contact tech support."
>>>>
>>>> RelinkBE = True
>>>>
>>>> For Each tdf In CurrentDb.TableDefs
>>>> If Len(tdf.Connect) > 0 Then
>>>> Debug.Print tdf.Name & " " & tdf.Connect
>>>> tdf.RefreshLink
>>>> End If
>>>> Next tdf
>>>> Set tdf = Nothing
>>>>
>>>> ProcExit:
>>>> Exit Function
>>>>
>>>> ProcError:
>>>> Select Case Err.Number
>>>> Case 3011 'Bad Table Name
>>>> strMsg = "Table Not Found." & strCall
>>>> Case 3024 'File Name Not Found
>>>> strMsg = "Database File Name Not Found." & strCall
>>>> Case 3044 'Path Not found
>>>> strMsg = "Database Path Not Found." & strCall
>>>> Case Else
>>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>>> ") in function ReLinkBE of Module mdlRelink" &
>>>> strCall
>>>> End Select
>>>>
>>>> MsgBox strMsg, vbExclamation, "Call Support"
>>>> RelinkBE = False
>>>>
>>>> Resume ProcExit
>>>>
>>>> End Function
>>>
>>>
>>
>
>
|
|
FWIW, what I do is store the full path(s) to the backend(s) in an INI file.
when the database opens, it checks that the Connect strings for the linked
tables match the correct backend in the INI file. If not (as happens when a
new version of the frontend has just been installed) it silently relinks all
the tables.
Only if there is a problem with relinking do I display a message. For some
applications I check whether the user is an Admin and, if so, let them
browse for the correct backend to fix the problem. Otherwise the user just
gets a message to seek assistance.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:eHZb6WlRJHA.4992[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Thanks again. This is a bit of a digression, but here it is anyhow: I
> built a database in which the Before Update event validates that all
> required fields are filled in. If not, the record is undone. Before that
> happens the user is presented with two message boxes, both with the safe
> option (don't undo) highlighted. Several users have clicked through the
> non-default choice on both message boxes without looking at either one,
> then complained bitterly to me about how my database destroyed their work.
> Some of these folks are brilliant in their fields, and make sound choices
> on matters affecting the lives and safety of others, but I would not be
> confident in their ability to relink a table. They need to call me or
> somebody else in the know, if I can locate and train such a person.
>
> I have discovered a version of the mvps code that a helper (computer
> science major) devised a while ago. It uses fewer message boxes (for
> instance, it doesn't ask if they want to refresh the table links, since
> presumably anyone opening the database means to use the data). I think I
> could also either present the users with more information about how to
> relink, or direct them to further documentation on the network. With a
> split database they're not likely to do any harm by relinking incorrectly,
> so maybe I worry more than I need to.
>
> Anyhow, thanks for the input.
>
> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
> news:1F676185-CFF8-450F-A1D9-66A16D58CEBA[ at ]microsoft.com...
>> Hi Bruce
>>
>> Yes, the code you posted will raise an error if one of the tables is not
>> already correctly linked. I suppose you could then give the user the
>> option of doing nothing and asking for support, or attempting to find the
>> correct backend themselves.
>>
>> One trap to watch for is that a user will often browse to the *frontend*
>> and choose that, instead of the backend. Of course, the frontend does
>> contain tables with all the correct names, so a link to these tables can
>> be created, but it will not work because it is circular! So always
>> check, after a user has selected a backend file, that it is not
>> CurrentDb.Name.
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>> news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
>>> OK, thanks for the information. I have seen code, such as at the mvps
>>> web site, to refresh table links, but message boxes asking the user if
>>> they want to refresh the links, etc. are out of the question. The only
>>> thing I want is for a message box to appear if there is a problem. I am
>>> still trying to sort out how to use the code without message boxes, at
>>> least not routine ones that appear every time the application starts.
>>> Users will simply click through such messages. I probably would do so
>>> myself for messages that appear every time.
>>> Will the code I posted at least generate a message if there is a
>>> problem? It seems to me it will. Perhaps an error could cause an
>>> otherwise invisible command button to appear, and the mvps refresh links
>>> code could be run from the command button as needed.
>>>
>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>>>> Hi Bruce
>>>>
>>>> This code is not really doing anything useful at all. If the local
>>>> table is present, and the Connect string is valid, then the table is
>>>> already linked.
>>>>
>>>> For relinking code to be useful, it needs to be able to fix problems
>>>> where the tables are *not* already correctly linked. This means it
>>>> must be able to:
>>>> 1. read (from an INI file or somewhere) the correct location of the
>>>> backend database(s)
>>>> 2. browse for the backend database(s) if they don't exist
>>>> 3. check that the local tables are connected to the correct backends
>>>> 4. relink them if they are not
>>>> --
>>>> Good Luck :-)
>>>>
>>>> Graham Mandeno [Access MVP]
>>>> Auckland, New Zealand
>>>>
>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>>>> I'm not sure this is where to post this question, but here it is
>>>>> anyhow. Somebody who was working with me a while ago came up with the
>>>>> following code to relink back end files. I don't know if it is
>>>>> adapted from the code at the mvps web site, or what exactly. Tables
>>>>> may be in more than one BE database. In particular, the Employee
>>>>> table is used by a number of applications. I don't want to present
>>>>> the user with any options, as most users wouldn't know what to do if
>>>>> presented with something like the Linked Table Manager, or for that
>>>>> matter if asked whether they want to relink the tables. Rather, I
>>>>> want the code to run invisibly on startup. The BE files will not move,
>>>>> and I have used UNC paths to the BE files.
>>>>>
>>>>> I have used the code below, which I call in the startup form's Load
>>>>> event, and have not had a problem with users not being able to get at
>>>>> the data. Debug.Print lists the tables and the paths correctly. I
>>>>> wonder, though, if I am leaving out something, as other code I have
>>>>> seen for relinking the BE files seems to be rather more complex than
>>>>> this.
>>>>>
>>>>> Public Function RelinkBE() As Boolean
>>>>>
>>>>> On Error GoTo ProcError
>>>>>
>>>>> Dim tdf As TableDef
>>>>> Dim strMsg As String, strCall As String
>>>>>
>>>>> strCall = vbCrLf & "Contact tech support."
>>>>>
>>>>> RelinkBE = True
>>>>>
>>>>> For Each tdf In CurrentDb.TableDefs
>>>>> If Len(tdf.Connect) > 0 Then
>>>>> Debug.Print tdf.Name & " " & tdf.Connect
>>>>> tdf.RefreshLink
>>>>> End If
>>>>> Next tdf
>>>>> Set tdf = Nothing
>>>>>
>>>>> ProcExit:
>>>>> Exit Function
>>>>>
>>>>> ProcError:
>>>>> Select Case Err.Number
>>>>> Case 3011 'Bad Table Name
>>>>> strMsg = "Table Not Found." & strCall
>>>>> Case 3024 'File Name Not Found
>>>>> strMsg = "Database File Name Not Found." & strCall
>>>>> Case 3044 'Path Not found
>>>>> strMsg = "Database Path Not Found." & strCall
>>>>> Case Else
>>>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>>>> ") in function ReLinkBE of Module mdlRelink" &
>>>>> strCall
>>>>> End Select
>>>>>
>>>>> MsgBox strMsg, vbExclamation, "Call Support"
>>>>> RelinkBE = False
>>>>>
>>>>> Resume ProcExit
>>>>>
>>>>> End Function
>>>>
>>>>
>>>
>>
>>
>
|
|
Thanks for the feedback, but I have to admit I don't know exactly how to
store the path in an ini file. Actually, I may be able to figure that out,
but the problem for me is how to reference what was stored there, and how to
silently relinkd the tables if the Connect string does not match the ini
file. I suppose I would go back to the mvps code for relinking.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:7773561F-3377-450C-92C8-FE2F5712B4A2[ at ]microsoft.com...
[Quoted Text] > FWIW, what I do is store the full path(s) to the backend(s) in an INI
> file. when the database opens, it checks that the Connect strings for the
> linked tables match the correct backend in the INI file. If not (as
> happens when a new version of the frontend has just been installed) it
> silently relinks all the tables.
>
> Only if there is a problem with relinking do I display a message. For
> some applications I check whether the user is an Admin and, if so, let
> them browse for the correct backend to fix the problem. Otherwise the
> user just gets a message to seek assistance.
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
> news:eHZb6WlRJHA.4992[ at ]TK2MSFTNGP05.phx.gbl...
>> Thanks again. This is a bit of a digression, but here it is anyhow: I
>> built a database in which the Before Update event validates that all
>> required fields are filled in. If not, the record is undone. Before
>> that happens the user is presented with two message boxes, both with the
>> safe option (don't undo) highlighted. Several users have clicked through
>> the non-default choice on both message boxes without looking at either
>> one, then complained bitterly to me about how my database destroyed their
>> work. Some of these folks are brilliant in their fields, and make sound
>> choices on matters affecting the lives and safety of others, but I would
>> not be confident in their ability to relink a table. They need to call
>> me or somebody else in the know, if I can locate and train such a person.
>>
>> I have discovered a version of the mvps code that a helper (computer
>> science major) devised a while ago. It uses fewer message boxes (for
>> instance, it doesn't ask if they want to refresh the table links, since
>> presumably anyone opening the database means to use the data). I think I
>> could also either present the users with more information about how to
>> relink, or direct them to further documentation on the network. With a
>> split database they're not likely to do any harm by relinking
>> incorrectly, so maybe I worry more than I need to.
>>
>> Anyhow, thanks for the input.
>>
>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>> news:1F676185-CFF8-450F-A1D9-66A16D58CEBA[ at ]microsoft.com...
>>> Hi Bruce
>>>
>>> Yes, the code you posted will raise an error if one of the tables is not
>>> already correctly linked. I suppose you could then give the user the
>>> option of doing nothing and asking for support, or attempting to find
>>> the correct backend themselves.
>>>
>>> One trap to watch for is that a user will often browse to the *frontend*
>>> and choose that, instead of the backend. Of course, the frontend does
>>> contain tables with all the correct names, so a link to these tables can
>>> be created, but it will not work because it is circular! So always
>>> check, after a user has selected a backend file, that it is not
>>> CurrentDb.Name.
>>> --
>>> Good Luck :-)
>>>
>>> Graham Mandeno [Access MVP]
>>> Auckland, New Zealand
>>>
>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>> news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
>>>> OK, thanks for the information. I have seen code, such as at the mvps
>>>> web site, to refresh table links, but message boxes asking the user if
>>>> they want to refresh the links, etc. are out of the question. The only
>>>> thing I want is for a message box to appear if there is a problem. I
>>>> am still trying to sort out how to use the code without message boxes,
>>>> at least not routine ones that appear every time the application
>>>> starts. Users will simply click through such messages. I probably
>>>> would do so myself for messages that appear every time.
>>>> Will the code I posted at least generate a message if there is a
>>>> problem? It seems to me it will. Perhaps an error could cause an
>>>> otherwise invisible command button to appear, and the mvps refresh
>>>> links code could be run from the command button as needed.
>>>>
>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>>> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>>>>> Hi Bruce
>>>>>
>>>>> This code is not really doing anything useful at all. If the local
>>>>> table is present, and the Connect string is valid, then the table is
>>>>> already linked.
>>>>>
>>>>> For relinking code to be useful, it needs to be able to fix problems
>>>>> where the tables are *not* already correctly linked. This means it
>>>>> must be able to:
>>>>> 1. read (from an INI file or somewhere) the correct location of the
>>>>> backend database(s)
>>>>> 2. browse for the backend database(s) if they don't exist
>>>>> 3. check that the local tables are connected to the correct
>>>>> backends
>>>>> 4. relink them if they are not
>>>>> --
>>>>> Good Luck :-)
>>>>>
>>>>> Graham Mandeno [Access MVP]
>>>>> Auckland, New Zealand
>>>>>
>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>>>>> I'm not sure this is where to post this question, but here it is
>>>>>> anyhow. Somebody who was working with me a while ago came up with the
>>>>>> following code to relink back end files. I don't know if it is
>>>>>> adapted from the code at the mvps web site, or what exactly. Tables
>>>>>> may be in more than one BE database. In particular, the Employee
>>>>>> table is used by a number of applications. I don't want to present
>>>>>> the user with any options, as most users wouldn't know what to do if
>>>>>> presented with something like the Linked Table Manager, or for that
>>>>>> matter if asked whether they want to relink the tables. Rather, I
>>>>>> want the code to run invisibly on startup. The BE files will not
>>>>>> move, and I have used UNC paths to the BE files.
>>>>>>
>>>>>> I have used the code below, which I call in the startup form's Load
>>>>>> event, and have not had a problem with users not being able to get at
>>>>>> the data. Debug.Print lists the tables and the paths correctly. I
>>>>>> wonder, though, if I am leaving out something, as other code I have
>>>>>> seen for relinking the BE files seems to be rather more complex than
>>>>>> this.
>>>>>>
>>>>>> Public Function RelinkBE() As Boolean
>>>>>>
>>>>>> On Error GoTo ProcError
>>>>>>
>>>>>> Dim tdf As TableDef
>>>>>> Dim strMsg As String, strCall As String
>>>>>>
>>>>>> strCall = vbCrLf & "Contact tech support."
>>>>>>
>>>>>> RelinkBE = True
>>>>>>
>>>>>> For Each tdf In CurrentDb.TableDefs
>>>>>> If Len(tdf.Connect) > 0 Then
>>>>>> Debug.Print tdf.Name & " " & tdf.Connect
>>>>>> tdf.RefreshLink
>>>>>> End If
>>>>>> Next tdf
>>>>>> Set tdf = Nothing
>>>>>>
>>>>>> ProcExit:
>>>>>> Exit Function
>>>>>>
>>>>>> ProcError:
>>>>>> Select Case Err.Number
>>>>>> Case 3011 'Bad Table Name
>>>>>> strMsg = "Table Not Found." & strCall
>>>>>> Case 3024 'File Name Not Found
>>>>>> strMsg = "Database File Name Not Found." & strCall
>>>>>> Case 3044 'Path Not found
>>>>>> strMsg = "Database Path Not Found." & strCall
>>>>>> Case Else
>>>>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>>>>> ") in function ReLinkBE of Module mdlRelink" &
>>>>>> strCall
>>>>>> End Select
>>>>>>
>>>>>> MsgBox strMsg, vbExclamation, "Call Support"
>>>>>> RelinkBE = False
>>>>>>
>>>>>> Resume ProcExit
>>>>>>
>>>>>> End Function
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
|
|
Hi Bruce
OK, here's a brief description of my method:
I have two tables in my frontend:
USysDataSources
=============
DataSourceID Numeric (primary key)
DataSourceName Text
DataSourceType Text
ConnectionParams Text
UserID Text
Password Text
This contains one record for each backend.
Only the first two fields are required for plain Access backends - the
others are for SQL Server, ODBC and other backends.
USysLinkedTables
=============
LocalTable Text (primary key)
DataSource Numeric (foreign key to DataSourceID)
SourceTableName Text (optional - if null then same as LocalTable)
Critical Yes/No (if true [default] then failure to relink causes
application to fail)
This contains one record for each linked table.
The frontend also contains a query named USysCompareLinkedTables:
SELECT D.DataSourceID, D.DataSourceName,
GetDataSource([DataSourceName],"",False,False) AS Filename,
T.LocalTable, T.Critical,
IIf(IsNull([SourceTableName]),[LocalTable],[SourceTableName]) AS
SourceTable,
S.Database, S.ForeignName, S.Type
FROM USysDataSources AS D
INNER JOIN (USysLinkedTables AS T
LEFT JOIN MSysObjects AS S ON T.LocalTable = S.Name)
ON D.DataSourceID = T.DataSource
WHERE (((S.Type) Is Null Or (S.Type)=6));
This lists, for each linked table, the name of the backend and source table
it is linked to, and the name of the backend and source table that it
*ought* to be linked to.
Note the function GetDataSource, which reads the "correct" backend file from
the INI file and checks that it exists, then returns its full path. It also
optionally offers to browse for the backend if it cannot be found, but this
is suppressed by the False arguments.
The startup code for the database opens a recordset with the following SQL:
Select * from USysCompareLinkedTables
where (Database Is Null) OR (Database<>Filename)
OR (ForeignName<>SourceTable);
This lists only the "broken" tables which require relinking. If there are
any, then another function is called to relink ALL the tables.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:ub2Lm8KSJHA.3628[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Thanks for the feedback, but I have to admit I don't know exactly how to
> store the path in an ini file. Actually, I may be able to figure that
> out, but the problem for me is how to reference what was stored there, and
> how to silently relinkd the tables if the Connect string does not match
> the ini file. I suppose I would go back to the mvps code for relinking.
>
> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
> news:7773561F-3377-450C-92C8-FE2F5712B4A2[ at ]microsoft.com...
>> FWIW, what I do is store the full path(s) to the backend(s) in an INI
>> file. when the database opens, it checks that the Connect strings for the
>> linked tables match the correct backend in the INI file. If not (as
>> happens when a new version of the frontend has just been installed) it
>> silently relinks all the tables.
>>
>> Only if there is a problem with relinking do I display a message. For
>> some applications I check whether the user is an Admin and, if so, let
>> them browse for the correct backend to fix the problem. Otherwise the
>> user just gets a message to seek assistance.
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>> news:eHZb6WlRJHA.4992[ at ]TK2MSFTNGP05.phx.gbl...
>>> Thanks again. This is a bit of a digression, but here it is anyhow: I
>>> built a database in which the Before Update event validates that all
>>> required fields are filled in. If not, the record is undone. Before
>>> that happens the user is presented with two message boxes, both with the
>>> safe option (don't undo) highlighted. Several users have clicked
>>> through the non-default choice on both message boxes without looking at
>>> either one, then complained bitterly to me about how my database
>>> destroyed their work. Some of these folks are brilliant in their fields,
>>> and make sound choices on matters affecting the lives and safety of
>>> others, but I would not be confident in their ability to relink a table.
>>> They need to call me or somebody else in the know, if I can locate and
>>> train such a person.
>>>
>>> I have discovered a version of the mvps code that a helper (computer
>>> science major) devised a while ago. It uses fewer message boxes (for
>>> instance, it doesn't ask if they want to refresh the table links, since
>>> presumably anyone opening the database means to use the data). I think
>>> I could also either present the users with more information about how to
>>> relink, or direct them to further documentation on the network. With a
>>> split database they're not likely to do any harm by relinking
>>> incorrectly, so maybe I worry more than I need to.
>>>
>>> Anyhow, thanks for the input.
>>>
>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>> news:1F676185-CFF8-450F-A1D9-66A16D58CEBA[ at ]microsoft.com...
>>>> Hi Bruce
>>>>
>>>> Yes, the code you posted will raise an error if one of the tables is
>>>> not already correctly linked. I suppose you could then give the user
>>>> the option of doing nothing and asking for support, or attempting to
>>>> find the correct backend themselves.
>>>>
>>>> One trap to watch for is that a user will often browse to the
>>>> *frontend* and choose that, instead of the backend. Of course, the
>>>> frontend does contain tables with all the correct names, so a link to
>>>> these tables can be created, but it will not work because it is
>>>> circular! So always check, after a user has selected a backend file,
>>>> that it is not CurrentDb.Name.
>>>> --
>>>> Good Luck :-)
>>>>
>>>> Graham Mandeno [Access MVP]
>>>> Auckland, New Zealand
>>>>
>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>> news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
>>>>> OK, thanks for the information. I have seen code, such as at the mvps
>>>>> web site, to refresh table links, but message boxes asking the user if
>>>>> they want to refresh the links, etc. are out of the question. The
>>>>> only thing I want is for a message box to appear if there is a
>>>>> problem. I am still trying to sort out how to use the code without
>>>>> message boxes, at least not routine ones that appear every time the
>>>>> application starts. Users will simply click through such messages. I
>>>>> probably would do so myself for messages that appear every time.
>>>>> Will the code I posted at least generate a message if there is a
>>>>> problem? It seems to me it will. Perhaps an error could cause an
>>>>> otherwise invisible command button to appear, and the mvps refresh
>>>>> links code could be run from the command button as needed.
>>>>>
>>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>>>> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>>>>>> Hi Bruce
>>>>>>
>>>>>> This code is not really doing anything useful at all. If the local
>>>>>> table is present, and the Connect string is valid, then the table is
>>>>>> already linked.
>>>>>>
>>>>>> For relinking code to be useful, it needs to be able to fix problems
>>>>>> where the tables are *not* already correctly linked. This means it
>>>>>> must be able to:
>>>>>> 1. read (from an INI file or somewhere) the correct location of
>>>>>> the backend database(s)
>>>>>> 2. browse for the backend database(s) if they don't exist
>>>>>> 3. check that the local tables are connected to the correct
>>>>>> backends
>>>>>> 4. relink them if they are not
>>>>>> --
>>>>>> Good Luck :-)
>>>>>>
>>>>>> Graham Mandeno [Access MVP]
>>>>>> Auckland, New Zealand
>>>>>>
>>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>>>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>>>>>> I'm not sure this is where to post this question, but here it is
>>>>>>> anyhow. Somebody who was working with me a while ago came up with
>>>>>>> the following code to relink back end files. I don't know if it is
>>>>>>> adapted from the code at the mvps web site, or what exactly. Tables
>>>>>>> may be in more than one BE database. In particular, the Employee
>>>>>>> table is used by a number of applications. I don't want to present
>>>>>>> the user with any options, as most users wouldn't know what to do if
>>>>>>> presented with something like the Linked Table Manager, or for that
>>>>>>> matter if asked whether they want to relink the tables. Rather, I
>>>>>>> want the code to run invisibly on startup. The BE files will not
>>>>>>> move, and I have used UNC paths to the BE files.
>>>>>>>
>>>>>>> I have used the code below, which I call in the startup form's Load
>>>>>>> event, and have not had a problem with users not being able to get
>>>>>>> at the data. Debug.Print lists the tables and the paths correctly.
>>>>>>> I wonder, though, if I am leaving out something, as other code I
>>>>>>> have seen for relinking the BE files seems to be rather more complex
>>>>>>> than this.
>>>>>>>
>>>>>>> Public Function RelinkBE() As Boolean
>>>>>>>
>>>>>>> On Error GoTo ProcError
>>>>>>>
>>>>>>> Dim tdf As TableDef
>>>>>>> Dim strMsg As String, strCall As String
>>>>>>>
>>>>>>> strCall = vbCrLf & "Contact tech support."
>>>>>>>
>>>>>>> RelinkBE = True
>>>>>>>
>>>>>>> For Each tdf In CurrentDb.TableDefs
>>>>>>> If Len(tdf.Connect) > 0 Then
>>>>>>> Debug.Print tdf.Name & " " & tdf.Connect
>>>>>>> tdf.RefreshLink
>>>>>>> End If
>>>>>>> Next tdf
>>>>>>> Set tdf = Nothing
>>>>>>>
>>>>>>> ProcExit:
>>>>>>> Exit Function
>>>>>>>
>>>>>>> ProcError:
>>>>>>> Select Case Err.Number
>>>>>>> Case 3011 'Bad Table Name
>>>>>>> strMsg = "Table Not Found." & strCall
>>>>>>> Case 3024 'File Name Not Found
>>>>>>> strMsg = "Database File Name Not Found." & strCall
>>>>>>> Case 3044 'Path Not found
>>>>>>> strMsg = "Database Path Not Found." & strCall
>>>>>>> Case Else
>>>>>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>>>>>> ") in function ReLinkBE of Module mdlRelink" &
>>>>>>> strCall
>>>>>>> End Select
>>>>>>>
>>>>>>> MsgBox strMsg, vbExclamation, "Call Support"
>>>>>>> RelinkBE = False
>>>>>>>
>>>>>>> Resume ProcExit
>>>>>>>
>>>>>>> End Function
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>
|
|
Thanks again, Graham.. It will be some time before I can sort this out. In
particular it seems I need to use something like GetPrivateProfileString to
read from the ini file, and I need to get on with some other things for now,
so that investigation will have to wait. I think I'll look for a way to
relink all tables every time and leave it at that for now. When I have the
time I will study your suggestions further and begin to experiment. Reading
from an ini file (or other text file, I suppose) could be a useful thing to
know, so I expect the study will have benefits beyond the immediate
question. Also, I am starting to get some understanding about how the
system tables (MSysObject, in this case) can be put to good use beyond their
behind-the-scenes activity.
Again, thanks for the time and care you have put into your detailed
responses.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:6F5B0F3D-6509-4854-A9BE-61C012B07D9F[ at ]microsoft.com...
[Quoted Text] > Hi Bruce
>
> OK, here's a brief description of my method:
>
> I have two tables in my frontend:
>
> USysDataSources
> =============
> DataSourceID Numeric (primary key)
> DataSourceName Text
> DataSourceType Text
> ConnectionParams Text
> UserID Text
> Password Text
>
> This contains one record for each backend.
> Only the first two fields are required for plain Access backends - the
> others are for SQL Server, ODBC and other backends.
>
> USysLinkedTables
> =============
> LocalTable Text (primary key)
> DataSource Numeric (foreign key to DataSourceID)
> SourceTableName Text (optional - if null then same as LocalTable)
> Critical Yes/No (if true [default] then failure to relink
> causes application to fail)
>
> This contains one record for each linked table.
>
> The frontend also contains a query named USysCompareLinkedTables:
>
> SELECT D.DataSourceID, D.DataSourceName,
> GetDataSource([DataSourceName],"",False,False) AS Filename,
> T.LocalTable, T.Critical,
> IIf(IsNull([SourceTableName]),[LocalTable],[SourceTableName]) AS
> SourceTable,
> S.Database, S.ForeignName, S.Type
> FROM USysDataSources AS D
> INNER JOIN (USysLinkedTables AS T
> LEFT JOIN MSysObjects AS S ON T.LocalTable = S.Name)
> ON D.DataSourceID = T.DataSource
> WHERE (((S.Type) Is Null Or (S.Type)=6));
>
> This lists, for each linked table, the name of the backend and source
> table it is linked to, and the name of the backend and source table that
> it *ought* to be linked to.
>
> Note the function GetDataSource, which reads the "correct" backend file
> from the INI file and checks that it exists, then returns its full path.
> It also optionally offers to browse for the backend if it cannot be found,
> but this is suppressed by the False arguments.
>
> The startup code for the database opens a recordset with the following
> SQL:
>
> Select * from USysCompareLinkedTables
> where (Database Is Null) OR (Database<>Filename)
> OR (ForeignName<>SourceTable);
>
> This lists only the "broken" tables which require relinking. If there are
> any, then another function is called to relink ALL the tables.
>
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
> news:ub2Lm8KSJHA.3628[ at ]TK2MSFTNGP06.phx.gbl...
>> Thanks for the feedback, but I have to admit I don't know exactly how to
>> store the path in an ini file. Actually, I may be able to figure that
>> out, but the problem for me is how to reference what was stored there,
>> and how to silently relinkd the tables if the Connect string does not
>> match the ini file. I suppose I would go back to the mvps code for
>> relinking.
>>
>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>> news:7773561F-3377-450C-92C8-FE2F5712B4A2[ at ]microsoft.com...
>>> FWIW, what I do is store the full path(s) to the backend(s) in an INI
>>> file. when the database opens, it checks that the Connect strings for
>>> the linked tables match the correct backend in the INI file. If not (as
>>> happens when a new version of the frontend has just been installed) it
>>> silently relinks all the tables.
>>>
>>> Only if there is a problem with relinking do I display a message. For
>>> some applications I check whether the user is an Admin and, if so, let
>>> them browse for the correct backend to fix the problem. Otherwise the
>>> user just gets a message to seek assistance.
>>> --
>>> Good Luck :-)
>>>
>>> Graham Mandeno [Access MVP]
>>> Auckland, New Zealand
>>>
>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>> news:eHZb6WlRJHA.4992[ at ]TK2MSFTNGP05.phx.gbl...
>>>> Thanks again. This is a bit of a digression, but here it is anyhow: I
>>>> built a database in which the Before Update event validates that all
>>>> required fields are filled in. If not, the record is undone. Before
>>>> that happens the user is presented with two message boxes, both with
>>>> the safe option (don't undo) highlighted. Several users have clicked
>>>> through the non-default choice on both message boxes without looking at
>>>> either one, then complained bitterly to me about how my database
>>>> destroyed their work. Some of these folks are brilliant in their
>>>> fields, and make sound choices on matters affecting the lives and
>>>> safety of others, but I would not be confident in their ability to
>>>> relink a table. They need to call me or somebody else in the know, if I
>>>> can locate and train such a person.
>>>>
>>>> I have discovered a version of the mvps code that a helper (computer
>>>> science major) devised a while ago. It uses fewer message boxes (for
>>>> instance, it doesn't ask if they want to refresh the table links, since
>>>> presumably anyone opening the database means to use the data). I think
>>>> I could also either present the users with more information about how
>>>> to relink, or direct them to further documentation on the network.
>>>> With a split database they're not likely to do any harm by relinking
>>>> incorrectly, so maybe I worry more than I need to.
>>>>
>>>> Anyhow, thanks for the input.
>>>>
>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>>> news:1F676185-CFF8-450F-A1D9-66A16D58CEBA[ at ]microsoft.com...
>>>>> Hi Bruce
>>>>>
>>>>> Yes, the code you posted will raise an error if one of the tables is
>>>>> not already correctly linked. I suppose you could then give the user
>>>>> the option of doing nothing and asking for support, or attempting to
>>>>> find the correct backend themselves.
>>>>>
>>>>> One trap to watch for is that a user will often browse to the
>>>>> *frontend* and choose that, instead of the backend. Of course, the
>>>>> frontend does contain tables with all the correct names, so a link to
>>>>> these tables can be created, but it will not work because it is
>>>>> circular! So always check, after a user has selected a backend file,
>>>>> that it is not CurrentDb.Name.
>>>>> --
>>>>> Good Luck :-)
>>>>>
>>>>> Graham Mandeno [Access MVP]
>>>>> Auckland, New Zealand
>>>>>
>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>>> news:eO5my3YRJHA.4504[ at ]TK2MSFTNGP02.phx.gbl...
>>>>>> OK, thanks for the information. I have seen code, such as at the
>>>>>> mvps web site, to refresh table links, but message boxes asking the
>>>>>> user if they want to refresh the links, etc. are out of the question.
>>>>>> The only thing I want is for a message box to appear if there is a
>>>>>> problem. I am still trying to sort out how to use the code without
>>>>>> message boxes, at least not routine ones that appear every time the
>>>>>> application starts. Users will simply click through such messages. I
>>>>>> probably would do so myself for messages that appear every time.
>>>>>> Will the code I posted at least generate a message if there is a
>>>>>> problem? It seems to me it will. Perhaps an error could cause an
>>>>>> otherwise invisible command button to appear, and the mvps refresh
>>>>>> links code could be run from the command button as needed.
>>>>>>
>>>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
>>>>>> news:8F252999-5A6E-475F-9A92-E06784043514[ at ]microsoft.com...
>>>>>>> Hi Bruce
>>>>>>>
>>>>>>> This code is not really doing anything useful at all. If the local
>>>>>>> table is present, and the Connect string is valid, then the table is
>>>>>>> already linked.
>>>>>>>
>>>>>>> For relinking code to be useful, it needs to be able to fix problems
>>>>>>> where the tables are *not* already correctly linked. This means it
>>>>>>> must be able to:
>>>>>>> 1. read (from an INI file or somewhere) the correct location of
>>>>>>> the backend database(s)
>>>>>>> 2. browse for the backend database(s) if they don't exist
>>>>>>> 3. check that the local tables are connected to the correct
>>>>>>> backends
>>>>>>> 4. relink them if they are not
>>>>>>> --
>>>>>>> Good Luck :-)
>>>>>>>
>>>>>>> Graham Mandeno [Access MVP]
>>>>>>> Auckland, New Zealand
>>>>>>>
>>>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>>>>>>> news:uOFWkbPRJHA.5080[ at ]TK2MSFTNGP05.phx.gbl...
>>>>>>>> I'm not sure this is where to post this question, but here it is
>>>>>>>> anyhow. Somebody who was working with me a while ago came up with
>>>>>>>> the following code to relink back end files. I don't know if it is
>>>>>>>> adapted from the code at the mvps web site, or what exactly.
>>>>>>>> Tables may be in more than one BE database. In particular, the
>>>>>>>> Employee table is used by a number of applications. I don't want
>>>>>>>> to present the user with any options, as most users wouldn't know
>>>>>>>> what to do if presented with something like the Linked Table
>>>>>>>> Manager, or for that matter if asked whether they want to relink
>>>>>>>> the tables. Rather, I want the code to run invisibly on startup.
>>>>>>>> The BE files will not move, and I have used UNC paths to the BE
>>>>>>>> files.
>>>>>>>>
>>>>>>>> I have used the code below, which I call in the startup form's Load
>>>>>>>> event, and have not had a problem with users not being able to get
>>>>>>>> at the data. Debug.Print lists the tables and the paths correctly.
>>>>>>>> I wonder, though, if I am leaving out something, as other code I
>>>>>>>> have seen for relinking the BE files seems to be rather more
>>>>>>>> complex than this.
>>>>>>>>
>>>>>>>> Public Function RelinkBE() As Boolean
>>>>>>>>
>>>>>>>> On Error GoTo ProcError
>>>>>>>>
>>>>>>>> Dim tdf As TableDef
>>>>>>>> Dim strMsg As String, strCall As String
>>>>>>>>
>>>>>>>> strCall = vbCrLf & "Contact tech support."
>>>>>>>>
>>>>>>>> RelinkBE = True
>>>>>>>>
>>>>>>>> For Each tdf In CurrentDb.TableDefs
>>>>>>>> If Len(tdf.Connect) > 0 Then
>>>>>>>> Debug.Print tdf.Name & " " & tdf.Connect
>>>>>>>> tdf.RefreshLink
>>>>>>>> End If
>>>>>>>> Next tdf
>>>>>>>> Set tdf = Nothing
>>>>>>>>
>>>>>>>> ProcExit:
>>>>>>>> Exit Function
>>>>>>>>
>>>>>>>> ProcError:
>>>>>>>> Select Case Err.Number
>>>>>>>> Case 3011 'Bad Table Name
>>>>>>>> strMsg = "Table Not Found." & strCall
>>>>>>>> Case 3024 'File Name Not Found
>>>>>>>> strMsg = "Database File Name Not Found." & strCall
>>>>>>>> Case 3044 'Path Not found
>>>>>>>> strMsg = "Database Path Not Found." & strCall
>>>>>>>> Case Else
>>>>>>>> strMsg = "Error " & Err.Number & " (" & Err.Description & _
>>>>>>>> ") in function ReLinkBE of Module mdlRelink" &
>>>>>>>> strCall
>>>>>>>> End Select
>>>>>>>>
>>>>>>>> MsgBox strMsg, vbExclamation, "Call Support"
>>>>>>>> RelinkBE = False
>>>>>>>>
>>>>>>>> Resume ProcExit
>>>>>>>>
>>>>>>>> End Function
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
|
|
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote:
[Quoted Text] >FWIW, what I do is store the full path(s) to the backend(s) in an INI file.
>when the database opens, it checks that the Connect strings for the linked
>tables match the correct backend in the INI file. If not (as happens when a
>new version of the frontend has just been installed) it silently relinks all
>the tables.
>
>Only if there is a problem with relinking do I display a message.
I do the same.
>For some
>applications I check whether the user is an Admin and, if so, let them
>browse for the correct backend to fix the problem. Otherwise the user just
>gets a message to seek assistance.
Now that I've never bothered to do. Why can't the user just ask for
instructions over the phone on relinking to the appropriate BE?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote:
[Quoted Text] >Thanks again, Graham.. It will be some time before I can sort this out. In
>particular it seems I need to use something like GetPrivateProfileString to
>read from the ini file,
And the equivalent to write to the INI file once you've found the
correct BE.
Or you could use the registry. I initially chose to use INI files
because I figured that it would be easier for the users if they ever
had to manually update the file. But in over ten years I don't think
I've ever had the need.
>I think I'll look for a way to
>relink all tables every time and leave it at that for now.
Note that for best performance you should do either one of the
following:
1) Open a database variable against the backend
or
2) Once you've relinked the first table open a recordset variable
against that table and continue relinking all the other tables.
This will make a huge difference in performance when someone else is
already in the FE linked to the BE in question.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
Thanks for the reply. When you say I should open a database variable
against the backend, would that be something like this?
Dim db as DAO.Database
Set db = CurrentDatabase()
or for the recordset:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMain", dbOpenDynaset)
(where tblMain is the first linked table)
If so, in either case would this be in the relinking code, and would I close
the recordset at the end of the relinking code? If not, where have I missed
the boat?
I sort of get what you are saying (although I have to say I don't understand
the "why"), but not enough to implement it with any confidence.
"Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message
news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54[ at ]4ax.com...
[Quoted Text] > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote: > >>Thanks again, Graham.. It will be some time before I can sort this out. >>In >>particular it seems I need to use something like GetPrivateProfileString >>to >>read from the ini file, > > And the equivalent to write to the INI file once you've found the > correct BE. > > Or you could use the registry. I initially chose to use INI files > because I figured that it would be easier for the users if they ever > had to manually update the file. But in over ten years I don't think > I've ever had the need. > >>I think I'll look for a way to >>relink all tables every time and leave it at that for now. > > Note that for best performance you should do either one of the > following: > > 1) Open a database variable against the backend > or > 2) Once you've relinked the first table open a recordset variable > against that table and continue relinking all the other tables. > > This will make a huge difference in performance when someone else is > already in the FE linked to the BE in question. > > Tony > -- > Tony Toews, Microsoft Access MVP > Please respond only in the newsgroups so that others can > read the entire thread of messages. > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
Hi Tony
[Quoted Text] >>For some
>>applications I check whether the user is an Admin and, if so, let them
>>browse for the correct backend to fix the problem. Otherwise the user
>>just
>>gets a message to seek assistance.
>
> Now that I've never bothered to do. Why can't the user just ask for
> instructions over the phone on relinking to the appropriate BE?
It depends, Tony. Note I said "For *some* applications".
Some sites have many users, a few of whom can be trusted to make intelligent
decisions on their own about where the correct backend might be, but most of
whom cannot. In these cases the message might say "Ask <your local support
person> to remedy this problem". Other installations might say "Browse and
select the file containing your back-end data for <name of datasource>".
Still others might say "Hands in the air! Back away from the keyboard!
Phone Graham NOW!"
<g>
--
Cheers,
Graham M
|
|
Hi Bruce
What Tony means is that when you create a linked table, part of the process
involves the file system creating a reference to the backend database. If
there is not one already from the current process, then the file will need
to be opened, a lengthy process which, for an MDB, also involves creating an
LDB file. Once the table is linked, the reference is dropped. When the
number of file system references to the backend database drops to zero, the
file closes (and the LDB is deleted).
All this happens in a single line of code:
<TableDef>.RefreshLink
or
<Database>.TableDefs.Append <TableDef>
If you are linking many tables in the same backend, then it is much faster
to open the backend database first, so that it already has a reference and
each table link will not cause a file Open/Close with all the other
associated overhead.
So, to clarify, the procedure is as follows:
1. Ascertain the path to the backend
2. Open the backend:
Dim dbBE as Database
Set dbBE = DBEngine(0).OpenDatabase(<path to backend>)
3. Link all the tables in that backend, one by one
4. Close the backend:
dbBE.Close
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:ekOWgEkSJHA.2428[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Thanks for the reply. When you say I should open a database variable > against the backend, would that be something like this? > > Dim db as DAO.Database > Set db = CurrentDatabase() > > or for the recordset: > > Dim db As DAO.Database > Dim rs As DAO.Recordset > > Set db = CurrentDb > Set rs = db.OpenRecordset("tblMain", dbOpenDynaset) > (where tblMain is the first linked table) > > If so, in either case would this be in the relinking code, and would I > close the recordset at the end of the relinking code? If not, where have > I missed the boat? > > I sort of get what you are saying (although I have to say I don't > understand the "why"), but not enough to implement it with any confidence. > > > "Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message > news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54[ at ]4ax.com... >> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote: >> >>>Thanks again, Graham.. It will be some time before I can sort this out. >>>In >>>particular it seems I need to use something like GetPrivateProfileString >>>to >>>read from the ini file, >> >> And the equivalent to write to the INI file once you've found the >> correct BE. >> >> Or you could use the registry. I initially chose to use INI files >> because I figured that it would be easier for the users if they ever >> had to manually update the file. But in over ten years I don't think >> I've ever had the need. >> >>>I think I'll look for a way to >>>relink all tables every time and leave it at that for now. >> >> Note that for best performance you should do either one of the >> following: >> >> 1) Open a database variable against the backend >> or >> 2) Once you've relinked the first table open a recordset variable >> against that table and continue relinking all the other tables. >> >> This will make a huge difference in performance when someone else is >> already in the FE linked to the BE in question. >> >> Tony >> -- >> Tony Toews, Microsoft Access MVP >> Please respond only in the newsgroups so that others can >> read the entire thread of messages. >> Microsoft Access Links, Hints, Tips & Accounting Systems at >> http://www.granite.ab.ca/accsmstr.htm>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/>
|
|
Hi Tony (and Bruce)
[Quoted Text] > Or you could use the registry. I initially chose to use INI files...
As we've discussed in another forum, I would definitely NOT use the
Registry. This is because the wonderful world of "virtualisation" in
Windows Vista (aka "smoke and mirrors") screws about with anything you write
to a known shared location (including Windows folders and the Registry)
unless you are running with elevated administrator privileges.
For this reason, neither your frontend nor your INI file should be in any of
these "known shared" folders (or their subfolders). These include:
C:\Windows
C:\Program Files
C:\ProgramData (in XP this was
C:\Documents and Settings\All Users\AppData)
Bruce, here is the code I use to read/write INI files:
=========== start code ==================
Private Declare Function GetPrivateProfileSection _
Lib "kernel32" _
Alias "GetPrivateProfileSectionA" ( _
ByVal lpAppName As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long
Private Declare Function WritePrivateProfileString _
Lib "kernel32" _
Alias "WritePrivateProfileStringA" ( _
ByVal lpSectionName As String, _
ByVal lpKeyName As Any, _
ByVal lpString As Any, _
ByVal lpFileName As String _
) As Long
Public Function GetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
Optional sDefault As String) _
As String
Const cBufSize = 1024
Dim sBuffer As String, lRetLen As Long
sBuffer = String(cBufSize, vbNullChar)
lRetLen = GetPrivateProfileString(sSection, sKey, _
sDefault, sBuffer, cBufSize, sIniFile)
GetIniValue = Left(sBuffer, lRetLen)
End Function
Public Function SetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
sValue As String) _
As Long
SetIniValue = WritePrivateProfileString(sSection, _
sKey, sValue, sIniFile)
End Function
=========== end code ==================
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message
news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54[ at ]4ax.com...
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote:
>
>>Thanks again, Graham.. It will be some time before I can sort this out.
>>In
>>particular it seems I need to use something like GetPrivateProfileString
>>to
>>read from the ini file,
>
> And the equivalent to write to the INI file once you've found the
> correct BE.
>
> Or you could use the registry. I initially chose to use INI files
> because I figured that it would be easier for the users if they ever
> had to manually update the file. But in over ten years I don't think
> I've ever had the need.
>
>>I think I'll look for a way to
>>relink all tables every time and leave it at that for now.
>
> Note that for best performance you should do either one of the
> following:
>
> 1) Open a database variable against the backend
> or
> 2) Once you've relinked the first table open a recordset variable
> against that table and continue relinking all the other tables.
>
> This will make a huge difference in performance when someone else is
> already in the FE linked to the BE in question.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
I'll give that a try when I have a chance to get back to it, which may not
be tomorrow. Two more questions for now: Is there any need to made the
dbBE variable a DAO.Database? And is the path to the back end a path first
to the Access executable, followed by a switch that includes the path to the
BE (like with decompile, etc.), or is it just a direct path to the BE file?
BTW, I don't think I have a project so far with more than maybe 20 tables in
a single BE database.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:2EB71A86-A9C3-4925-A7AD-52DAF529FDD2[ at ]microsoft.com...
[Quoted Text] > Hi Bruce > > What Tony means is that when you create a linked table, part of the > process involves the file system creating a reference to the backend > database. If there is not one already from the current process, then the > file will need to be opened, a lengthy process which, for an MDB, also > involves creating an LDB file. Once the table is linked, the reference is > dropped. When the number of file system references to the backend database > drops to zero, the file closes (and the LDB is deleted). > > All this happens in a single line of code: > <TableDef>.RefreshLink > or > <Database>.TableDefs.Append <TableDef> > > If you are linking many tables in the same backend, then it is much faster > to open the backend database first, so that it already has a reference and > each table link will not cause a file Open/Close with all the other > associated overhead. > > So, to clarify, the procedure is as follows: > > 1. Ascertain the path to the backend > 2. Open the backend: > Dim dbBE as Database > Set dbBE = DBEngine(0).OpenDatabase(<path to backend>) > 3. Link all the tables in that backend, one by one > 4. Close the backend: > dbBE.Close > > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:ekOWgEkSJHA.2428[ at ]TK2MSFTNGP04.phx.gbl... >> Thanks for the reply. When you say I should open a database variable >> against the backend, would that be something like this? >> >> Dim db as DAO.Database >> Set db = CurrentDatabase() >> >> or for the recordset: >> >> Dim db As DAO.Database >> Dim rs As DAO.Recordset >> >> Set db = CurrentDb >> Set rs = db.OpenRecordset("tblMain", dbOpenDynaset) >> (where tblMain is the first linked table) >> >> If so, in either case would this be in the relinking code, and would I >> close the recordset at the end of the relinking code? If not, where have >> I missed the boat? >> >> I sort of get what you are saying (although I have to say I don't >> understand the "why"), but not enough to implement it with any >> confidence. >> >> >> "Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message >> news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54[ at ]4ax.com... >>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote: >>> >>>>Thanks again, Graham.. It will be some time before I can sort this out. >>>>In >>>>particular it seems I need to use something like GetPrivateProfileString >>>>to >>>>read from the ini file, >>> >>> And the equivalent to write to the INI file once you've found the >>> correct BE. >>> >>> Or you could use the registry. I initially chose to use INI files >>> because I figured that it would be easier for the users if they ever >>> had to manually update the file. But in over ten years I don't think >>> I've ever had the need. >>> >>>>I think I'll look for a way to >>>>relink all tables every time and leave it at that for now. >>> >>> Note that for best performance you should do either one of the >>> following: >>> >>> 1) Open a database variable against the backend >>> or >>> 2) Once you've relinked the first table open a recordset variable >>> against that table and continue relinking all the other tables. >>> >>> This will make a huge difference in performance when someone else is >>> already in the FE linked to the BE in question. >>> >>> Tony >>> -- >>> Tony Toews, Microsoft Access MVP >>> Please respond only in the newsgroups so that others can >>> read the entire thread of messages. >>> Microsoft Access Links, Hints, Tips & Accounting Systems at >>> http://www.granite.ab.ca/accsmstr.htm>>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/>> > >
|
|
OK, I think that makes it clearer. At least I can begin to understand how
the values get to the GetPrivateProfileSection and WritePrivateProfileString
functions (by using the public functions, which would be called from
somewhere in the code, I expect). I'm still working on understanding ByVal,
but I think I'm starting to get a handle on this stuff. Thanks again. Now
I will study and experiment some more.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:F852E18D-89B1-4ABE-BC76-5B78555BF645[ at ]microsoft.com...
[Quoted Text] > Hi Tony (and Bruce) > >> Or you could use the registry. I initially chose to use INI files... > > As we've discussed in another forum, I would definitely NOT use the > Registry. This is because the wonderful world of "virtualisation" in > Windows Vista (aka "smoke and mirrors") screws about with anything you > write to a known shared location (including Windows folders and the > Registry) unless you are running with elevated administrator privileges. > > For this reason, neither your frontend nor your INI file should be in any > of these "known shared" folders (or their subfolders). These include: > C:\Windows > C:\Program Files > C:\ProgramData (in XP this was > C:\Documents and Settings\All Users\AppData) > > Bruce, here is the code I use to read/write INI files: > > =========== start code ================== > Private Declare Function GetPrivateProfileSection _ > Lib "kernel32" _ > Alias "GetPrivateProfileSectionA" ( _ > ByVal lpAppName As String, _ > ByVal lpReturnedString As String, _ > ByVal nSize As Long, _ > ByVal lpFileName As String _ > ) As Long > > Private Declare Function WritePrivateProfileString _ > Lib "kernel32" _ > Alias "WritePrivateProfileStringA" ( _ > ByVal lpSectionName As String, _ > ByVal lpKeyName As Any, _ > ByVal lpString As Any, _ > ByVal lpFileName As String _ > ) As Long > > Public Function GetIniValue( _ > sIniFile As String, _ > sSection As String, _ > sKey As String, _ > Optional sDefault As String) _ > As String > Const cBufSize = 1024 > Dim sBuffer As String, lRetLen As Long > sBuffer = String(cBufSize, vbNullChar) > lRetLen = GetPrivateProfileString(sSection, sKey, _ > sDefault, sBuffer, cBufSize, sIniFile) > GetIniValue = Left(sBuffer, lRetLen) > End Function > > Public Function SetIniValue( _ > sIniFile As String, _ > sSection As String, _ > sKey As String, _ > sValue As String) _ > As Long > SetIniValue = WritePrivateProfileString(sSection, _ > sKey, sValue, sIniFile) > End Function > =========== end code ================== > > > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > > "Tony Toews [MVP]" <ttoews[ at ]telusplanet.net> wrote in message > news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54[ at ]4ax.com... >> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote: >> >>>Thanks again, Graham.. It will be some time before I can sort this out. >>>In >>>particular it seems I need to use something like GetPrivateProfileString >>>to >>>read from the ini file, >> >> And the equivalent to write to the INI file once you've found the >> correct BE. >> >> Or you could use the registry. I initially chose to use INI files >> because I figured that it would be easier for the users if they ever >> had to manually update the file. But in over ten years I don't think >> I've ever had the need. >> >>>I think I'll look for a way to >>>relink all tables every time and leave it at that for now. >> >> Note that for best performance you should do either one of the >> following: >> >> 1) Open a database variable against the backend >> or >> 2) Once you've relinked the first table open a recordset variable >> against that table and continue relinking all the other tables. >> >> This will make a huge difference in performance when someone else is >> already in the FE linked to the BE in question. >> >> Tony >> -- >> Tony Toews, Microsoft Access MVP >> Please respond only in the newsgroups so that others can >> read the entire thread of messages. >> Microsoft Access Links, Hints, Tips & Accounting Systems at >> http://www.granite.ab.ca/accsmstr.htm>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/> >
|
|
Hi Bruce
Yes, you should Dim dbBE as DAO.Database. (It could be declared as Object,
but every database should have a reference to DAO anyway, so early binding
is faster and gives better compile-time checking.) In any case, it will
need to be declared.
BTW, EVERY variable you use should be declared. using implicit declaration
is asking for trouble. EVERY module, including Form and Report modules
should have Option Explicit at the top.
The path to the backend is just that - the full file path - for example:
"\\MyServer\SomeShare\My App Folder\Back-end file.mdb"
BTW, there is no law that says an Access database must have the extension
"MDB". All my backends are .ABE files (Access Back-End). It at least
discourages users from double-clicking on them to open them!
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > I'll give that a try when I have a chance to get back to it, which may not
> be tomorrow. Two more questions for now: Is there any need to made the
> dbBE variable a DAO.Database? And is the path to the back end a path
> first to the Access executable, followed by a switch that includes the
> path to the BE (like with decompile, etc.), or is it just a direct path to
> the BE file?
>
> BTW, I don't think I have a project so far with more than maybe 20 tables
> in a single BE database.
|
|
I declare every variable. I have chosen Option Explicit as the default
option, so Access won't let me implicitly declare. It has been a great help
with debugging.
I didn't know that about using an extension other that mdb for BE files.
ABE is not a registered file type on my machine, so I suppose that means I
would need to add it. I use user-level security for my split databases
(which is all of my newer ones), so a user who attempts to open the file,
either FE or BE, by double clicking the icon will receive a message about
insufficient permissions. The only way in is through a shortcut that
temporarily joins the user to the secure workgroup file.
Thanks again for the information. This is an area of Access with which I
have had little experience, due in large part to a lack of information. I
have lots of information now, so I can work on gaining experience.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Hi Bruce
>
> Yes, you should Dim dbBE as DAO.Database. (It could be declared as
> Object, but every database should have a reference to DAO anyway, so early
> binding is faster and gives better compile-time checking.) In any case,
> it will need to be declared.
>
> BTW, EVERY variable you use should be declared. using implicit
> declaration is asking for trouble. EVERY module, including Form and
> Report modules should have Option Explicit at the top.
>
> The path to the backend is just that - the full file path - for example:
> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb"
>
> BTW, there is no law that says an Access database must have the extension
> "MDB". All my backends are .ABE files (Access Back-End). It at least
> discourages users from double-clicking on them to open them!
>
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl...
>> I'll give that a try when I have a chance to get back to it, which may
>> not be tomorrow. Two more questions for now: Is there any need to made
>> the dbBE variable a DAO.Database? And is the path to the back end a path
>> first to the Access executable, followed by a switch that includes the
>> path to the BE (like with decompile, etc.), or is it just a direct path
>> to the BE file?
>>
>> BTW, I don't think I have a project so far with more than maybe 20 tables
>> in a single BE database.
>
>
|
|
I believe that Graham's point is that by using ABE which isn't registered,
double-clicking on the back-end file means it won't automatically open:
instead, you'll get a message from Windows that it doesn't know what to do
with the file.
You could set ABE files to automatically open the files in Access, but
there's really no need to.
Once you open Access, you can choose File | Open, change Files of type to
All Files (*.*) and navigate to open the .ABE file that way.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >
> I didn't know that about using an extension other that mdb for BE files.
> ABE is not a registered file type on my machine, so I suppose that means I
> would need to add it. I use user-level security for my split databases
> (which is all of my newer ones), so a user who attempts to open the file,
> either FE or BE, by double clicking the icon will receive a message about
> insufficient permissions. The only way in is through a shortcut that
> temporarily joins the user to the secure workgroup file.
>
|
|
OK, I think I get it. The only program that needs to "see" the BE files is
the FE, which will be able to get at the data no matter the file extension.
Thanks.
"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message
news:uMHochxSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >I believe that Graham's point is that by using ABE which isn't registered, >double-clicking on the back-end file means it won't automatically open: >instead, you'll get a message from Windows that it doesn't know what to do >with the file. > > You could set ABE files to automatically open the files in Access, but > there's really no need to. > > Once you open Access, you can choose File | Open, change Files of type to > All Files (*.*) and navigate to open the .ABE file that way. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele> (no e-mails, please!) > > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >> >> I didn't know that about using an extension other that mdb for BE files. >> ABE is not a registered file type on my machine, so I suppose that means >> I would need to add it. I use user-level security for my split databases >> (which is all of my newer ones), so a user who attempts to open the file, >> either FE or BE, by double clicking the icon will receive a message about >> insufficient permissions. The only way in is through a shortcut that >> temporarily joins the user to the secure workgroup file. >> > >
|
|
Precisely!
--
Cheers :-)
Graham M
"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message
news:uMHochxSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >I believe that Graham's point is that by using ABE which isn't registered, >double-clicking on the back-end file means it won't automatically open: >instead, you'll get a message from Windows that it doesn't know what to do >with the file. > > You could set ABE files to automatically open the files in Access, but > there's really no need to. > > Once you open Access, you can choose File | Open, change Files of type to > All Files (*.*) and navigate to open the .ABE file that way. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele> (no e-mails, please!) > > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >> >> I didn't know that about using an extension other that mdb for BE files. >> ABE is not a registered file type on my machine, so I suppose that means >> I would need to add it. I use user-level security for my split databases >> (which is all of my newer ones), so a user who attempts to open the file, >> either FE or BE, by double clicking the icon will receive a message about >> insufficient permissions. The only way in is through a shortcut that >> temporarily joins the user to the secure workgroup file. >> > >
|
|
Hi Bruce
Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >I declare every variable. I have chosen Option Explicit as the default
>option, so Access won't let me implicitly declare. It has been a great
>help with debugging.
>
> I didn't know that about using an extension other that mdb for BE files.
> ABE is not a registered file type on my machine, so I suppose that means I
> would need to add it. I use user-level security for my split databases
> (which is all of my newer ones), so a user who attempts to open the file,
> either FE or BE, by double clicking the icon will receive a message about
> insufficient permissions. The only way in is through a shortcut that
> temporarily joins the user to the secure workgroup file.
>
> Thanks again for the information. This is an area of Access with which I
> have had little experience, due in large part to a lack of information. I
> have lots of information now, so I can work on gaining experience.
>
> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl...
>> Hi Bruce
>>
>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as
>> Object, but every database should have a reference to DAO anyway, so
>> early binding is faster and gives better compile-time checking.) In any
>> case, it will need to be declared.
>>
>> BTW, EVERY variable you use should be declared. using implicit
>> declaration is asking for trouble. EVERY module, including Form and
>> Report modules should have Option Explicit at the top.
>>
>> The path to the backend is just that - the full file path - for example:
>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb"
>>
>> BTW, there is no law that says an Access database must have the extension
>> "MDB". All my backends are .ABE files (Access Back-End). It at least
>> discourages users from double-clicking on them to open them!
>>
>>
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl...
>>> I'll give that a try when I have a chance to get back to it, which may
>>> not be tomorrow. Two more questions for now: Is there any need to made
>>> the dbBE variable a DAO.Database? And is the path to the back end a
>>> path first to the Access executable, followed by a switch that includes
>>> the path to the BE (like with decompile, etc.), or is it just a direct
>>> path to the BE file?
>>>
>>> BTW, I don't think I have a project so far with more than maybe 20
>>> tables in a single BE database.
>>
>>
>
|
|
I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users group
has no permissions anywhere. I think that means some variant of the code in
14.1 of the FAQ would work (which seems to be the same general idea as the
code at the mvps web site, I think).
I have to say I marvel anew at the Security FAQ each time I see it. It is
an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.
Since I do not have RWOP queries I think I can use RefreshLink. The FAQ
states:
"The best way to implement this [it is not clear in context to what "this"
refers; maybe it means restricting access to the data, or maybe RWOP] is to
remove ALL permissions from the underlying table(s) and use only queries to
get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to the
tables in the destination database. It would help if I knew whether the
destination database is the link's destination (i.e. the BE) or if
"destination" means something else.
Anyhow, I have not found a need for RWOP queries, so I think I'm OK with
RefreshLink. If I have a problem that could be solved through the use of
RWOP queries I have not been able to identify that a RWOP query is what I
need. I think Joan Wild has some more information about this on her web
site. Yet another thing to sort out.
I do appreciate your pointing this out. I have to say, though, that it will
take a lot of development time I do not have right now to sort out how to
read the correct path from an ini file or somewhere, check the existing link
against that, and refresh the link if need be. For now the users will get
an error message, and I will fix it. I wish there was another way, but a
better solution will have to wait.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:A66CBCB9-26A2-4784-A941-8086AD04AF0D[ at ]microsoft.com...
[Quoted Text] > Hi Bruce > > Finally, if you use user-level security then I recommend you read the > section on linked tables (section 14) in the Access Security FAQ: > http://support.microsoft.com/support/access/content/secfaq.asp> > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >>I declare every variable. I have chosen Option Explicit as the default >>option, so Access won't let me implicitly declare. It has been a great >>help with debugging. >> >> I didn't know that about using an extension other that mdb for BE files. >> ABE is not a registered file type on my machine, so I suppose that means >> I would need to add it. I use user-level security for my split databases >> (which is all of my newer ones), so a user who attempts to open the file, >> either FE or BE, by double clicking the icon will receive a message about >> insufficient permissions. The only way in is through a shortcut that >> temporarily joins the user to the secure workgroup file. >> >> Thanks again for the information. This is an area of Access with which I >> have had little experience, due in large part to a lack of information. >> I have lots of information now, so I can work on gaining experience. >> >> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl... >>> Hi Bruce >>> >>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as >>> Object, but every database should have a reference to DAO anyway, so >>> early binding is faster and gives better compile-time checking.) In any >>> case, it will need to be declared. >>> >>> BTW, EVERY variable you use should be declared. using implicit >>> declaration is asking for trouble. EVERY module, including Form and >>> Report modules should have Option Explicit at the top. >>> >>> The path to the backend is just that - the full file path - for example: >>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb" >>> >>> BTW, there is no law that says an Access database must have the >>> extension "MDB". All my backends are .ABE files (Access Back-End). It >>> at least discourages users from double-clicking on them to open them! >>> >>> >>> -- >>> Good Luck :-) >>> >>> Graham Mandeno [Access MVP] >>> Auckland, New Zealand >>> >>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl... >>>> I'll give that a try when I have a chance to get back to it, which may >>>> not be tomorrow. Two more questions for now: Is there any need to >>>> made the dbBE variable a DAO.Database? And is the path to the back end >>>> a path first to the Access executable, followed by a switch that >>>> includes the path to the BE (like with decompile, etc.), or is it just >>>> a direct path to the BE file? >>>> >>>> BTW, I don't think I have a project so far with more than maybe 20 >>>> tables in a single BE database. >>> >>> >> > >
|
|
Hi Bruce
The method I use (and would recommend) is the one in 14.3, because it
*always* works, no matter what the permissions on the backend tables.
Note that allowing all permissions on the linked tables in the frontend does
not grant permissions to the *data*. It just allows the user to delete and
relink the tables.
Yes, you're right, user-level security can seem like an "impenetrable
thicket", but if properly implemented it works well and is sufficiently
secure for most applications. I am one among many who are very disappointed
that it has been canned in A2007.
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:eYgtJF$SJHA.5268[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >I have full permissions for all users on the BE tables, and table >permissions restricted in the FE depending on the group. The Users group >has no permissions anywhere. I think that means some variant of the code >in 14.1 of the FAQ would work (which seems to be the same general idea as >the code at the mvps web site, I think). > > I have to say I marvel anew at the Security FAQ each time I see it. It is > an almost inpenetrable thicket, further impeded by instructions and > information going back to Access 2.0. > > Since I do not have RWOP queries I think I can use RefreshLink. The FAQ > states: > "The best way to implement this [it is not clear in context to what "this" > refers; maybe it means restricting access to the data, or maybe RWOP] is > to remove ALL permissions from the underlying table(s) and use only > queries to get at the data you want your users to have." > But later is seems to suggest that full permissions can be assigned to the > tables in the destination database. It would help if I knew whether the > destination database is the link's destination (i.e. the BE) or if > "destination" means something else. > > Anyhow, I have not found a need for RWOP queries, so I think I'm OK with > RefreshLink. If I have a problem that could be solved through the use of > RWOP queries I have not been able to identify that a RWOP query is what I > need. I think Joan Wild has some more information about this on her web > site. Yet another thing to sort out. > > I do appreciate your pointing this out. I have to say, though, that it > will take a lot of development time I do not have right now to sort out > how to read the correct path from an ini file or somewhere, check the > existing link against that, and refresh the link if need be. For now the > users will get an error message, and I will fix it. I wish there was > another way, but a better solution will have to wait. > > "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message > news:A66CBCB9-26A2-4784-A941-8086AD04AF0D[ at ]microsoft.com... >> Hi Bruce >> >> Finally, if you use user-level security then I recommend you read the >> section on linked tables (section 14) in the Access Security FAQ: >> http://support.microsoft.com/support/access/content/secfaq.asp>> >> -- >> Good Luck :-) >> >> Graham Mandeno [Access MVP] >> Auckland, New Zealand >> >> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >> news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >>>I declare every variable. I have chosen Option Explicit as the default >>>option, so Access won't let me implicitly declare. It has been a great >>>help with debugging. >>> >>> I didn't know that about using an extension other that mdb for BE files. >>> ABE is not a registered file type on my machine, so I suppose that means >>> I would need to add it. I use user-level security for my split >>> databases (which is all of my newer ones), so a user who attempts to >>> open the file, either FE or BE, by double clicking the icon will receive >>> a message about insufficient permissions. The only way in is through a >>> shortcut that temporarily joins the user to the secure workgroup file. >>> >>> Thanks again for the information. This is an area of Access with which >>> I have had little experience, due in large part to a lack of >>> information. I have lots of information now, so I can work on gaining >>> experience. >>> >>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl... >>>> Hi Bruce >>>> >>>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as >>>> Object, but every database should have a reference to DAO anyway, so >>>> early binding is faster and gives better compile-time checking.) In >>>> any case, it will need to be declared. >>>> >>>> BTW, EVERY variable you use should be declared. using implicit >>>> declaration is asking for trouble. EVERY module, including Form and >>>> Report modules should have Option Explicit at the top. >>>> >>>> The path to the backend is just that - the full file path - for >>>> example: >>>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb" >>>> >>>> BTW, there is no law that says an Access database must have the >>>> extension "MDB". All my backends are .ABE files (Access Back-End). It >>>> at least discourages users from double-clicking on them to open them! >>>> >>>> >>>> -- >>>> Good Luck :-) >>>> >>>> Graham Mandeno [Access MVP] >>>> Auckland, New Zealand >>>> >>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl... >>>>> I'll give that a try when I have a chance to get back to it, which may >>>>> not be tomorrow. Two more questions for now: Is there any need to >>>>> made the dbBE variable a DAO.Database? And is the path to the back >>>>> end a path first to the Access executable, followed by a switch that >>>>> includes the path to the BE (like with decompile, etc.), or is it just >>>>> a direct path to the BE file? >>>>> >>>>> BTW, I don't think I have a project so far with more than maybe 20 >>>>> tables in a single BE database. >>>> >>>> >>> >> >> >
|
|
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote:
[Quoted Text] >>>For some
>>>applications I check whether the user is an Admin and, if so, let them
>>>browse for the correct backend to fix the problem. Otherwise the user
>>>just
>>>gets a message to seek assistance.
>>
>> Now that I've never bothered to do. Why can't the user just ask for
>> instructions over the phone on relinking to the appropriate BE?
>
>It depends, Tony. Note I said "For *some* applications".
>
>Some sites have many users, a few of whom can be trusted to make intelligent
>decisions on their own about where the correct backend might be, but most of
>whom cannot. In these cases the message might say "Ask <your local support
>person> to remedy this problem". Other installations might say "Browse and
>select the file containing your back-end data for <name of datasource>".
>Still others might say "Hands in the air! Back away from the keyboard!
>Phone Graham NOW!"
<chuckle> Fair enough.
One of these years I want to write a TCP/IP multi cast host module.
It's only job would be to respond to client apps, such as my FE and
tell the FE the network path to use. Thus the IT person could run
this host module and tell the user to install the FE. (The FE
installs without requiring administrator privileges so long as a
version of Access is already on the system.) Then when the user
runs the FE it first does a TCP/IP multi-cast to see if a host exists.
if it does it then interrogates the host looking for the path to the
backend on the network server. Then host responds and the client FE
relinks automatically. Thus the only thing the user needs to do is to
click on a file or URL to install the FE.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
|
|
It's not ULS that's the impenetrable thicket, it's the documentation. ULS
is a bit difficult to follow at first (Jack MacDonald's paper of the subject
was the one that finally led me to understand what is going on), but the FAQ
is helpful only after the subject has been learned elsewhere. I still think
they could have updated the FAQ at least to include Access 2003 in the
title.
I think I have found enough clues in the documentation to deduce that the
destination database is the FE and the source database is the BE. Section
14.3 states that the Connect property can be used as long as there are "full
permissions in the destination (FE) database and Open/Run permissions on the
source database (BE) - no permissions at all are necessary on the source
tables." Assuming this literally referes to database permissions rather
permissions to any tables, queries, etc., it either says that there are no
security implications to granting all users full permissions to the FE
database, or that I would need to use the code in the faq_SetPermissions
function to change permissions temporarily. However, the documentation for
that function "assumes that a member of the Admins group will be executing
the code". I'm not sure why it assumes that, since the group name
apparentlly is passed to the function, but assuming it is necessary to pass
the Admins group name, either the code can be run only by members of the
Admins group or everybody needs to be a member of the group. If membership
in the Admins group is necessary to run the code either everybody needs to
be a member of the Admins group (which pretty much wipes out security), or
only a few users can relink tables. Or maybe the faq_SetPermissions
function is not necessary. In any case, it seems that using the Connect
property is possible
I could be mistaken about the meaning of "source" and "destination". The
source could be the origin of data (FE) that flows to the destination (BE),
or the source could be the collection of data (BE) that flows to the
destination where it will be seen and used (the FE). Figurative language
should be defined. Maybe I'm supposed to know the meaning of the two terms,
but I have not found a way to frame the search question. If I had to bet I
would say the source is the BE.
For now users will get an error message if the table link is not
successfully refreshed, and I will fix it. This has not happened, so maybe
I will continue to be lucky for a while longer. Beyond that maybe I can use
the code at the mvps web site (RefreshLink), modified to remove some or all
of the prompts. Maybe I can combine it with an ini file that lists the
path.
Beyond that I don't have a prayer, at least not until I have a few more
years experience behind me. I am extremely frustrated by my inability to
sort this out, but at least I am old enough that I know when to admit I am
beaten. Thanks for trying to help. I really do appreciate the time you
have put into your responses.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:E019493E-A1FE-46CC-842F-5C60C1C464BA[ at ]microsoft.com...
[Quoted Text] > Hi Bruce > > The method I use (and would recommend) is the one in 14.3, because it > *always* works, no matter what the permissions on the backend tables. > > Note that allowing all permissions on the linked tables in the frontend > does not grant permissions to the *data*. It just allows the user to > delete and relink the tables. > > Yes, you're right, user-level security can seem like an "impenetrable > thicket", but if properly implemented it works well and is sufficiently > secure for most applications. I am one among many who are very > disappointed that it has been canned in A2007. > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:eYgtJF$SJHA.5268[ at ]TK2MSFTNGP04.phx.gbl... >>I have full permissions for all users on the BE tables, and table >>permissions restricted in the FE depending on the group. The Users group >>has no permissions anywhere. I think that means some variant of the code >>in 14.1 of the FAQ would work (which seems to be the same general idea as >>the code at the mvps web site, I think). >> >> I have to say I marvel anew at the Security FAQ each time I see it. It >> is an almost inpenetrable thicket, further impeded by instructions and >> information going back to Access 2.0. >> >> Since I do not have RWOP queries I think I can use RefreshLink. The FAQ >> states: >> "The best way to implement this [it is not clear in context to what >> "this" refers; maybe it means restricting access to the data, or maybe >> RWOP] is to remove ALL permissions from the underlying table(s) and use >> only queries to get at the data you want your users to have." >> But later is seems to suggest that full permissions can be assigned to >> the tables in the destination database. It would help if I knew whether >> the destination database is the link's destination (i.e. the BE) or if >> "destination" means something else. >> >> Anyhow, I have not found a need for RWOP queries, so I think I'm OK with >> RefreshLink. If I have a problem that could be solved through the use of >> RWOP queries I have not been able to identify that a RWOP query is what I >> need. I think Joan Wild has some more information about this on her web >> site. Yet another thing to sort out. >> >> I do appreciate your pointing this out. I have to say, though, that it >> will take a lot of development time I do not have right now to sort out >> how to read the correct path from an ini file or somewhere, check the >> existing link against that, and refresh the link if need be. For now the >> users will get an error message, and I will fix it. I wish there was >> another way, but a better solution will have to wait. >> >> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >> news:A66CBCB9-26A2-4784-A941-8086AD04AF0D[ at ]microsoft.com... >>> Hi Bruce >>> >>> Finally, if you use user-level security then I recommend you read the >>> section on linked tables (section 14) in the Access Security FAQ: >>> http://support.microsoft.com/support/access/content/secfaq.asp>>> >>> -- >>> Good Luck :-) >>> >>> Graham Mandeno [Access MVP] >>> Auckland, New Zealand >>> >>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>> news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >>>>I declare every variable. I have chosen Option Explicit as the default >>>>option, so Access won't let me implicitly declare. It has been a great >>>>help with debugging. >>>> >>>> I didn't know that about using an extension other that mdb for BE >>>> files. ABE is not a registered file type on my machine, so I suppose >>>> that means I would need to add it. I use user-level security for my >>>> split databases (which is all of my newer ones), so a user who attempts >>>> to open the file, either FE or BE, by double clicking the icon will >>>> receive a message about insufficient permissions. The only way in is >>>> through a shortcut that temporarily joins the user to the secure >>>> workgroup file. >>>> >>>> Thanks again for the information. This is an area of Access with which >>>> I have had little experience, due in large part to a lack of >>>> information. I have lots of information now, so I can work on gaining >>>> experience. >>>> >>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>>> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl... >>>>> Hi Bruce >>>>> >>>>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as >>>>> Object, but every database should have a reference to DAO anyway, so >>>>> early binding is faster and gives better compile-time checking.) In >>>>> any case, it will need to be declared. >>>>> >>>>> BTW, EVERY variable you use should be declared. using implicit >>>>> declaration is asking for trouble. EVERY module, including Form and >>>>> Report modules should have Option Explicit at the top. >>>>> >>>>> The path to the backend is just that - the full file path - for >>>>> example: >>>>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb" >>>>> >>>>> BTW, there is no law that says an Access database must have the >>>>> extension "MDB". All my backends are .ABE files (Access Back-End). >>>>> It at least discourages users from double-clicking on them to open >>>>> them! >>>>> >>>>> >>>>> -- >>>>> Good Luck :-) >>>>> >>>>> Graham Mandeno [Access MVP] >>>>> Auckland, New Zealand >>>>> >>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl... >>>>>> I'll give that a try when I have a chance to get back to it, which >>>>>> may not be tomorrow. Two more questions for now: Is there any need >>>>>> to made the dbBE variable a DAO.Database? And is the path to the >>>>>> back end a path first to the Access executable, followed by a switch >>>>>> that includes the path to the BE (like with decompile, etc.), or is >>>>>> it just a direct path to the BE file? >>>>>> >>>>>> BTW, I don't think I have a project so far with more than maybe 20 >>>>>> tables in a single BE database. >>>>> >>>>> >>>> >>> >>> >> > >
|
|
Hi Bruce
You're correct about "source" and "destination". Source is where you are
linking *from* (backend) and destination is where you are linking *to*
(frontend).
You're also correct about the FAQ being a difficult document to grasp. In
its defence, it covers a lot of information about a very complex area, but
it was also written by quite a number of people over a number of years and
versions, and it would definitely benefit from a rewrite. For a start, I
doubt anyone is interested in the Access 2.0 bits any more! Unfortunately,
since ULS is the baby that MS has thrown out with the bathwater, putting
resources into a rewrite is unlikely :-(
Basically, any user who may relink tables (and for me this means any user
who may open the frontend) should have all permissions to <New
tables/queries> in the frontend (see section 16.3). Then, provided they
have Open/Run permissions on the backend (which they would need to read the
data anyway), they can link tables using the delete-and-recreate-TableDef
method (section 14.3).
Sorry if you already told me, but how did you get on with reading/writing
the INI file?
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message
news:OHbbUajTJHA.5860[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > It's not ULS that's the impenetrable thicket, it's the documentation. ULS > is a bit difficult to follow at first (Jack MacDonald's paper of the > subject was the one that finally led me to understand what is going on), > but the FAQ is helpful only after the subject has been learned elsewhere. > I still think they could have updated the FAQ at least to include Access > 2003 in the title. > > I think I have found enough clues in the documentation to deduce that the > destination database is the FE and the source database is the BE. Section > 14.3 states that the Connect property can be used as long as there are > "full permissions in the destination (FE) database and Open/Run > permissions on the source database (BE) - no permissions at all are > necessary on the source tables." Assuming this literally referes to > database permissions rather permissions to any tables, queries, etc., it > either says that there are no security implications to granting all users > full permissions to the FE database, or that I would need to use the code > in the faq_SetPermissions function to change permissions temporarily. > However, the documentation for that function "assumes that a member of the > Admins group will be executing the code". I'm not sure why it assumes > that, since the group name apparentlly is passed to the function, but > assuming it is necessary to pass the Admins group name, either the code > can be run only by members of the Admins group or everybody needs to be a > member of the group. If membership in the Admins group is necessary to > run the code either everybody needs to be a member of the Admins group > (which pretty much wipes out security), or only a few users can relink > tables. Or maybe the faq_SetPermissions function is not necessary. In any > case, it seems that using the Connect property is possible > > I could be mistaken about the meaning of "source" and "destination". The > source could be the origin of data (FE) that flows to the destination > (BE), or the source could be the collection of data (BE) that flows to the > destination where it will be seen and used (the FE). Figurative language > should be defined. Maybe I'm supposed to know the meaning of the two > terms, but I have not found a way to frame the search question. If I had > to bet I would say the source is the BE. > > For now users will get an error message if the table link is not > successfully refreshed, and I will fix it. This has not happened, so > maybe I will continue to be lucky for a while longer. Beyond that maybe I > can use the code at the mvps web site (RefreshLink), modified to remove > some or all of the prompts. Maybe I can combine it with an ini file that > lists the path. > > Beyond that I don't have a prayer, at least not until I have a few more > years experience behind me. I am extremely frustrated by my inability to > sort this out, but at least I am old enough that I know when to admit I am > beaten. Thanks for trying to help. I really do appreciate the time you > have put into your responses. > > "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message > news:E019493E-A1FE-46CC-842F-5C60C1C464BA[ at ]microsoft.com... >> Hi Bruce >> >> The method I use (and would recommend) is the one in 14.3, because it >> *always* works, no matter what the permissions on the backend tables. >> >> Note that allowing all permissions on the linked tables in the frontend >> does not grant permissions to the *data*. It just allows the user to >> delete and relink the tables. >> >> Yes, you're right, user-level security can seem like an "impenetrable >> thicket", but if properly implemented it works well and is sufficiently >> secure for most applications. I am one among many who are very >> disappointed that it has been canned in A2007. >> -- >> Good Luck :-) >> >> Graham Mandeno [Access MVP] >> Auckland, New Zealand >> >> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >> news:eYgtJF$SJHA.5268[ at ]TK2MSFTNGP04.phx.gbl... >>>I have full permissions for all users on the BE tables, and table >>>permissions restricted in the FE depending on the group. The Users group >>>has no permissions anywhere. I think that means some variant of the code >>>in 14.1 of the FAQ would work (which seems to be the same general idea as >>>the code at the mvps web site, I think). >>> >>> I have to say I marvel anew at the Security FAQ each time I see it. It >>> is an almost inpenetrable thicket, further impeded by instructions and >>> information going back to Access 2.0. >>> >>> Since I do not have RWOP queries I think I can use RefreshLink. The FAQ >>> states: >>> "The best way to implement this [it is not clear in context to what >>> "this" refers; maybe it means restricting access to the data, or maybe >>> RWOP] is to remove ALL permissions from the underlying table(s) and use >>> only queries to get at the data you want your users to have." >>> But later is seems to suggest that full permissions can be assigned to >>> the tables in the destination database. It would help if I knew whether >>> the destination database is the link's destination (i.e. the BE) or if >>> "destination" means something else. >>> >>> Anyhow, I have not found a need for RWOP queries, so I think I'm OK with >>> RefreshLink. If I have a problem that could be solved through the use >>> of RWOP queries I have not been able to identify that a RWOP query is >>> what I need. I think Joan Wild has some more information about this on >>> her web site. Yet another thing to sort out. >>> >>> I do appreciate your pointing this out. I have to say, though, that it >>> will take a lot of development time I do not have right now to sort out >>> how to read the correct path from an ini file or somewhere, check the >>> existing link against that, and refresh the link if need be. For now >>> the users will get an error message, and I will fix it. I wish there >>> was another way, but a better solution will have to wait. >>> >>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>> news:A66CBCB9-26A2-4784-A941-8086AD04AF0D[ at ]microsoft.com... >>>> Hi Bruce >>>> >>>> Finally, if you use user-level security then I recommend you read the >>>> section on linked tables (section 14) in the Access Security FAQ: >>>> http://support.microsoft.com/support/access/content/secfaq.asp>>>> >>>> -- >>>> Good Luck :-) >>>> >>>> Graham Mandeno [Access MVP] >>>> Auckland, New Zealand >>>> >>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>> news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >>>>>I declare every variable. I have chosen Option Explicit as the default >>>>>option, so Access won't let me implicitly declare. It has been a great >>>>>help with debugging. >>>>> >>>>> I didn't know that about using an extension other that mdb for BE >>>>> files. ABE is not a registered file type on my machine, so I suppose >>>>> that means I would need to add it. I use user-level security for my >>>>> split databases (which is all of my newer ones), so a user who >>>>> attempts to open the file, either FE or BE, by double clicking the >>>>> icon will receive a message about insufficient permissions. The only >>>>> way in is through a shortcut that temporarily joins the user to the >>>>> secure workgroup file. >>>>> >>>>> Thanks again for the information. This is an area of Access with >>>>> which I have had little experience, due in large part to a lack of >>>>> information. I have lots of information now, so I can work on gaining >>>>> experience. >>>>> >>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>>>> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl... >>>>>> Hi Bruce >>>>>> >>>>>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as >>>>>> Object, but every database should have a reference to DAO anyway, so >>>>>> early binding is faster and gives better compile-time checking.) In >>>>>> any case, it will need to be declared. >>>>>> >>>>>> BTW, EVERY variable you use should be declared. using implicit >>>>>> declaration is asking for trouble. EVERY module, including Form and >>>>>> Report modules should have Option Explicit at the top. >>>>>> >>>>>> The path to the backend is just that - the full file path - for >>>>>> example: >>>>>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb" >>>>>> >>>>>> BTW, there is no law that says an Access database must have the >>>>>> extension "MDB". All my backends are .ABE files (Access Back-End). >>>>>> It at least discourages users from double-clicking on them to open >>>>>> them! >>>>>> >>>>>> >>>>>> -- >>>>>> Good Luck :-) >>>>>> >>>>>> Graham Mandeno [Access MVP] >>>>>> Auckland, New Zealand >>>>>> >>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>>>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl... >>>>>>> I'll give that a try when I have a chance to get back to it, which >>>>>>> may not be tomorrow. Two more questions for now: Is there any need >>>>>>> to made the dbBE variable a DAO.Database? And is the path to the >>>>>>> back end a path first to the Access executable, followed by a switch >>>>>>> that includes the path to the BE (like with decompile, etc.), or is >>>>>>> it just a direct path to the BE file? >>>>>>> >>>>>>> BTW, I don't think I have a project so far with more than maybe 20 >>>>>>> tables in a single BE database. >>>>>> >>>>>> >>>>> >>>> >>>> >>> >> >> >
|
|
Thanks again for your interest and your reply. OK, so the full permissions
are for New tables/queries in the FE. I have been assigning full
permissions to all FE tables for all users (except the Users group), and
restricting permissions in the BE. It took me a while to sort out that the
most restrictive permissions apply in this case, but the least restrictive
group permissions apply if a user is a member of several groups. Anyhow, it
sounds as if I have already addressed the permissions issue that is needed
to apply the Connect property as described in 14.3. That was the part I
couldn't sort out.
I have not had much of a chance to look at Access 2007 security. I have
Office 2007 installed at home, but am still trying to sort out the ribbons
and whatnot. I hope it is still possible to administer permissions and
logins in such a way that Access "knows" who is working on the database. I
have several projects in which the user "signs" a record by clicking a
button that inserts the CurrentUser. I have set it up so that each person's
Windows User Name is also their ULS name. The security logon shows this
name, and the user supplies the password (which is chosen separately from
the Windows password; the user can select the same password, but the Windows
one has to change from time to time). I hope this general technique can
still be used in Access 2007. There's not much chance that my company is
going to upgrade to Office 2007 any time soon, but eventually I will need to
address these issues. I think ULS is quite adequate for security on
databases used locally (excepting any that include personal and financial
information, etc., but these are purchased software packages, not homegrown
projects), so like you I am a bit unhappy to see it discontinued.
As for the ini file reading/writing, I have fallen a bit behind in my
day-to-day tasks as a result of developing my latest project (which I'm glad
to say is up and running, and working just as I had hoped), plus I need to
get a jump on some year-end stuff, so I can't devote much more time now to
sorting out the ini file techniques. However, I have saved the post in my
personal collection of Help documents, and hope to return to it in the next
few weeks. I think it will be very useful, both for relinking and in
general, so I most definitely want to study it further.
"Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message
news:CDC3653F-0B91-4FF6-A8A8-B64525D3A339[ at ]microsoft.com...
[Quoted Text] > Hi Bruce > > You're correct about "source" and "destination". Source is where you are > linking *from* (backend) and destination is where you are linking *to* > (frontend). > > You're also correct about the FAQ being a difficult document to grasp. In > its defence, it covers a lot of information about a very complex area, but > it was also written by quite a number of people over a number of years and > versions, and it would definitely benefit from a rewrite. For a start, I > doubt anyone is interested in the Access 2.0 bits any more! > Unfortunately, since ULS is the baby that MS has thrown out with the > bathwater, putting resources into a rewrite is unlikely :-( > > Basically, any user who may relink tables (and for me this means any user > who may open the frontend) should have all permissions to <New > tables/queries> in the frontend (see section 16.3). Then, provided they > have Open/Run permissions on the backend (which they would need to read > the data anyway), they can link tables using the > delete-and-recreate-TableDef method (section 14.3). > > Sorry if you already told me, but how did you get on with reading/writing > the INI file? > > -- > Good Luck :-) > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message > news:OHbbUajTJHA.5860[ at ]TK2MSFTNGP02.phx.gbl... >> It's not ULS that's the impenetrable thicket, it's the documentation. >> ULS is a bit difficult to follow at first (Jack MacDonald's paper of the >> subject was the one that finally led me to understand what is going on), >> but the FAQ is helpful only after the subject has been learned elsewhere. >> I still think they could have updated the FAQ at least to include Access >> 2003 in the title. >> >> I think I have found enough clues in the documentation to deduce that the >> destination database is the FE and the source database is the BE. >> Section 14.3 states that the Connect property can be used as long as >> there are "full permissions in the destination (FE) database and Open/Run >> permissions on the source database (BE) - no permissions at all are >> necessary on the source tables." Assuming this literally referes to >> database permissions rather permissions to any tables, queries, etc., it >> either says that there are no security implications to granting all users >> full permissions to the FE database, or that I would need to use the code >> in the faq_SetPermissions function to change permissions temporarily. >> However, the documentation for that function "assumes that a member of >> the Admins group will be executing the code". I'm not sure why it >> assumes that, since the group name apparentlly is passed to the function, >> but assuming it is necessary to pass the Admins group name, either the >> code can be run only by members of the Admins group or everybody needs to >> be a member of the group. If membership in the Admins group is necessary >> to run the code either everybody needs to be a member of the Admins group >> (which pretty much wipes out security), or only a few users can relink >> tables. Or maybe the faq_SetPermissions function is not necessary. In >> any case, it seems that using the Connect property is possible >> >> I could be mistaken about the meaning of "source" and "destination". The >> source could be the origin of data (FE) that flows to the destination >> (BE), or the source could be the collection of data (BE) that flows to >> the destination where it will be seen and used (the FE). Figurative >> language should be defined. Maybe I'm supposed to know the meaning of >> the two terms, but I have not found a way to frame the search question. >> If I had to bet I would say the source is the BE. >> >> For now users will get an error message if the table link is not >> successfully refreshed, and I will fix it. This has not happened, so >> maybe I will continue to be lucky for a while longer. Beyond that maybe >> I can use the code at the mvps web site (RefreshLink), modified to remove >> some or all of the prompts. Maybe I can combine it with an ini file that >> lists the path. >> >> Beyond that I don't have a prayer, at least not until I have a few more >> years experience behind me. I am extremely frustrated by my inability to >> sort this out, but at least I am old enough that I know when to admit I >> am beaten. Thanks for trying to help. I really do appreciate the time >> you have put into your responses. >> >> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >> news:E019493E-A1FE-46CC-842F-5C60C1C464BA[ at ]microsoft.com... >>> Hi Bruce >>> >>> The method I use (and would recommend) is the one in 14.3, because it >>> *always* works, no matter what the permissions on the backend tables. >>> >>> Note that allowing all permissions on the linked tables in the frontend >>> does not grant permissions to the *data*. It just allows the user to >>> delete and relink the tables. >>> >>> Yes, you're right, user-level security can seem like an "impenetrable >>> thicket", but if properly implemented it works well and is sufficiently >>> secure for most applications. I am one among many who are very >>> disappointed that it has been canned in A2007. >>> -- >>> Good Luck :-) >>> >>> Graham Mandeno [Access MVP] >>> Auckland, New Zealand >>> >>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>> news:eYgtJF$SJHA.5268[ at ]TK2MSFTNGP04.phx.gbl... >>>>I have full permissions for all users on the BE tables, and table >>>>permissions restricted in the FE depending on the group. The Users >>>>group has no permissions anywhere. I think that means some variant of >>>>the code in 14.1 of the FAQ would work (which seems to be the same >>>>general idea as the code at the mvps web site, I think). >>>> >>>> I have to say I marvel anew at the Security FAQ each time I see it. It >>>> is an almost inpenetrable thicket, further impeded by instructions and >>>> information going back to Access 2.0. >>>> >>>> Since I do not have RWOP queries I think I can use RefreshLink. The >>>> FAQ states: >>>> "The best way to implement this [it is not clear in context to what >>>> "this" refers; maybe it means restricting access to the data, or maybe >>>> RWOP] is to remove ALL permissions from the underlying table(s) and use >>>> only queries to get at the data you want your users to have." >>>> But later is seems to suggest that full permissions can be assigned to >>>> the tables in the destination database. It would help if I knew >>>> whether the destination database is the link's destination (i.e. the >>>> BE) or if "destination" means something else. >>>> >>>> Anyhow, I have not found a need for RWOP queries, so I think I'm OK >>>> with RefreshLink. If I have a problem that could be solved through the >>>> use of RWOP queries I have not been able to identify that a RWOP query >>>> is what I need. I think Joan Wild has some more information about this >>>> on her web site. Yet another thing to sort out. >>>> >>>> I do appreciate your pointing this out. I have to say, though, that it >>>> will take a lot of development time I do not have right now to sort out >>>> how to read the correct path from an ini file or somewhere, check the >>>> existing link against that, and refresh the link if need be. For now >>>> the users will get an error message, and I will fix it. I wish there >>>> was another way, but a better solution will have to wait. >>>> >>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>>> news:A66CBCB9-26A2-4784-A941-8086AD04AF0D[ at ]microsoft.com... >>>>> Hi Bruce >>>>> >>>>> Finally, if you use user-level security then I recommend you read the >>>>> section on linked tables (section 14) in the Access Security FAQ: >>>>> http://support.microsoft.com/support/access/content/secfaq.asp>>>>> >>>>> -- >>>>> Good Luck :-) >>>>> >>>>> Graham Mandeno [Access MVP] >>>>> Auckland, New Zealand >>>>> >>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>>> news:u0vgJpwSJHA.5056[ at ]TK2MSFTNGP04.phx.gbl... >>>>>>I declare every variable. I have chosen Option Explicit as the >>>>>>default option, so Access won't let me implicitly declare. It has >>>>>>been a great help with debugging. >>>>>> >>>>>> I didn't know that about using an extension other that mdb for BE >>>>>> files. ABE is not a registered file type on my machine, so I suppose >>>>>> that means I would need to add it. I use user-level security for my >>>>>> split databases (which is all of my newer ones), so a user who >>>>>> attempts to open the file, either FE or BE, by double clicking the >>>>>> icon will receive a message about insufficient permissions. The only >>>>>> way in is through a shortcut that temporarily joins the user to the >>>>>> secure workgroup file. >>>>>> >>>>>> Thanks again for the information. This is an area of Access with >>>>>> which I have had little experience, due in large part to a lack of >>>>>> information. I have lots of information now, so I can work on gaining >>>>>> experience. >>>>>> >>>>>> "Graham Mandeno" <Graham.Mandeno[ at ]nomail.please> wrote in message >>>>>> news:OTS5XKpSJHA.1908[ at ]TK2MSFTNGP04.phx.gbl... >>>>>>> Hi Bruce >>>>>>> >>>>>>> Yes, you should Dim dbBE as DAO.Database. (It could be declared as >>>>>>> Object, but every database should have a reference to DAO anyway, so >>>>>>> early binding is faster and gives better compile-time checking.) In >>>>>>> any case, it will need to be declared. >>>>>>> >>>>>>> BTW, EVERY variable you use should be declared. using implicit >>>>>>> declaration is asking for trouble. EVERY module, including Form and >>>>>>> Report modules should have Option Explicit at the top. >>>>>>> >>>>>>> The path to the backend is just that - the full file path - for >>>>>>> example: >>>>>>> "\\MyServer\SomeShare\My App Folder\Back-end file.mdb" >>>>>>> >>>>>>> BTW, there is no law that says an Access database must have the >>>>>>> extension "MDB". All my backends are .ABE files (Access Back-End). >>>>>>> It at least discourages users from double-clicking on them to open >>>>>>> them! >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Good Luck :-) >>>>>>> >>>>>>> Graham Mandeno [Access MVP] >>>>>>> Auckland, New Zealand >>>>>>> >>>>>>> "BruceM" <bamoob[ at ]yawhodotcalm.not> wrote in message >>>>>>> news:OiysMhoSJHA.5900[ at ]TK2MSFTNGP05.phx.gbl... >>>>>>>> I'll give that a try when I have a chance to get back to it, which >>>>>>>> may not be tomorrow. Two more questions for now: Is there any >>>>>>>> need to made the dbBE variable a DAO.Database? And is the path to >>>>>>>> the back end a path first to the Access executable, followed by a >>>>>>>> switch that includes the path to the BE (like with decompile, >>>>>>>> etc.), or is it just a direct path to the BE file? >>>>>>>> >>>>>>>> BTW, I don't think I have a project so far with more than maybe 20 >>>>>>>> tables in a single BE database. >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > >
|
|
|