rosalyn.clarke at barclays.com
rosalyn.clarke at barclays.com
Fri May 9 03:27:32 CDT 2008
A thousand thanks. I'll let you know how I get on.
Roz
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: 08 May 2008 19:01
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Drive mappings & global constants
First, put the two procedures, PopulateBEPath and ReadIniFile into a standard
module. Remove the lines that start Call GlobalErrors(...
2nd, create a text file and title it PSI.ini (for this example). Open the
file and paste in the three lines under 'Example .ini file contents'. Save
the file and put this file into the same folder where your database is.
3rd, And I Forgot This - paste this declaration at the top of your standard
module.
'-------------------------------------------------------------
Declare Function GetPrivateProfileString Lib "kernel32" _
Alias "GetPrivateProfileStringA" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String) As Long
'--------------------------------------------------------------
4th, create a form titled frmGlobalVariables with 4 unbound text fields.
The names of those textboxes are shown in the PopulateBEPath folder.
5th, open the form and the standard module. Put your cursor into the
PopulateBEPath procedure and start stepping through. Hopefully all will
work, or it will be close.
6th, this code is the very first procedure my database runs when it opens.
This code might also work in Excel or Word, but I rarely do any VBA in Excel
and never in Word. Once you get this working in Access, maybe an Excel or
Word guru here can help with those applications.
Any questions - call back!
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rosalyn.clarke at barclays.com
Sent: Thursday, May 08, 2008 11:35 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Drive mappings & global constants
That sounds perfect Dan! How do you pass the information from the .ini file
into the database?
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: 08 May 2008 14:09
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Drive mappings & global constants
Hi Roz,
I had a similar server switching event at an early customer where it took me
5 hours to recode one database. Vowing to never go through that again, I
eventually learned how to put the server path in one .ini file. Now the
database reads the path from the .ini file on opening. A .ini file is simply
a text file with a .ini extension, structured in a specific way.
For my convenience, I put the .ini file into the same folder as the front end
.mdb. You could use one file for all your databases.
You might be able to create one small database just for this purpose that
could then be called by all the other databases??? I haven't tried this
myself.
Last month at a different customer when we switched servers, changing the
path to the new server was a complete non-event. I just typed in the new
path in the .ini file - done in 20 seconds.
Note: frmGlobalVariables is a hidden sentinel form which contains many
global variables and is always open.
Note: SystemFolderPath and SystemBEName are functions which read from the
sentinel form.
Example .ini file contents:
-----------------------------------------------------------------------
[Parameters]
SystemFolderPath=\\10.99.99.99\prod$\Operations\PSISystem
SystemBEName=PSIBE.mdb
-----------------------------------------------------------------------
Example Code:
'----------------------------------------------------------------------
Public Sub PopulateBEPath()
On Error GoTo EH
Dim frm As Form
Set frm = Forms("frmGlobalVariables")
frm("txtINIFileName") = "PSI.ini"
frm("txtSystemFolderPath") = ReadIniFile("Parameters",
"SystemFolderPath")
frm("txtSystemBEName") = ReadIniFile("Parameters", "SystemBEName")
frm("txtBEFullPath") = SystemFolderPath & "\BackEnd\" & SystemBEName
Exit Sub
EH:
Call GlobalErrors("", Err.Number, Err.Description, "Startup",
"PopulateBEPath", , , "Line " & Erl)
End Sub
HTH,
Dan
'-----------------------------------------------------------------------
Public Function ReadIniFile(ByVal stgSect As String, ByVal stgKey As String)
As String On Error GoTo EH
Dim strRet As String * 128
Dim intSize As Integer
Dim intCharsReturned As Integer
Dim stgINIPath As String
If stgSect = "" Or stgKey = "" Then
MsgBox "Section Or Key To Read Not Specified!", vbExclamation, "INI"
Else
stgINIPath = CurrentProject.Path
intCharsReturned = GetPrivateProfileString(stgSect, stgKey, "",
strRet, 128, stgINIPath & "\" & "PSI.ini")
If intCharsReturned > 0 Then
ReadIniFile = Left$(strRet, intCharsReturned)
End If
End If
Exit Function
EH:
Call GlobalErrors("", Err.Number, Err.Description, "INI File",
"ReadIniFile", , , "Line " & Erl)
End Function
'--------------------------------------------------------------------------
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rosalyn.clarke at barclays.com
Sent: Thursday, May 08, 2008 5:57 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Drive mappings & global constants
Dear List
I have just been asked to resolve something, in zero time as usual, and I'm
sure there should be an elegant solution I just can't think of it.
There is a team that has 100+ Access databases, Excel spreadsheets and Word
templates that contain dependencies on a particular network drive that is
currently mapped to 'h:\'. In a few weeks time they will no longer be able to
use 'h:\' and will be given a new drive mapping. At some point shortly after
that, the server will be changed too, so the current DNS path will no longer
be valid either.
I don't know -and nobody will say for definite - what the new mapping & new
DNS path will be. These files are critical and the business insists on no
more than 24 hours downtime.
The dependencies include import/export routines, linked tables & linked
files.
What I would like to do is run a giant F&R on the code modules and replace
the root drives with a constant, and then make the constant available so that
ALL the applications use the constant. Then they can muck about with drive
mappings etc. to their heart's content and I won't have to come back to the
South coast and stay up all night changing code.
Any tips or ideas for making this work? Is it possible? I have code for
updating the code modules that should almost work but I've no idea how to
expose a constant across multiple apps.
TIA
Roz
"DSMP Mission - Building the future of Barclays computing infrastructure.
4K
customers won in 2007, 10k+ to delight in 2008."
This e-mail and any attachments are confidential and intended solely for the
addressee and may also be privileged or exempt from disclosure under
applicable law. If you are not the addressee, or have received this e-mail in
error, please notify the sender immediately, delete it from your system and
do not copy, disclose or otherwise act upon any part of this e-mail or its
attachments.
Internet communications are not guaranteed to be secure or virus-free.
The Barclays Group does not accept responsibility for any loss arising from
unauthorised access to, or interference with, any Internet communications by
any third party, or from the transmission of any viruses. Replies to this
e-mail may be monitored by the Barclays Group for operational or business
reasons.
Any opinion or other information in this e-mail or its attachments that does
not relate to the business of the Barclays Group is personal to the sender
and is not given or endorsed by the Barclays Group.
Barclays Bank PLC.Registered in England and Wales (registered no. 1026167).
Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom.
Barclays Bank PLC is authorised and regulated by the Financial Services
Authority.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
This e-mail and any attachments are confidential and intended solely for the
addressee and may also be privileged or exempt from disclosure under
applicable law. If you are not the addressee, or have received this e-mail in
error, please notify the sender immediately, delete it from your system and
do not copy, disclose or otherwise act upon any part of this e-mail or its
attachments.
Internet communications are not guaranteed to be secure or virus-free.
The Barclays Group does not accept responsibility for any loss arising from
unauthorised access to, or interference with, any Internet communications by
any third party, or from the transmission of any viruses. Replies to this
e-mail may be monitored by the Barclays Group for operational or business
reasons.
Any opinion or other information in this e-mail or its attachments that does
not relate to the business of the Barclays Group is personal to the sender
and is not given or endorsed by the Barclays Group.
Barclays Bank PLC.Registered in England and Wales (registered no. 1026167).
Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom.
Barclays Bank PLC is authorised and regulated by the Financial Services
Authority.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
This e-mail and any attachments are confidential and intended solely for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this e-mail in error, please notify the sender immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments.
Internet communications are not guaranteed to be secure or virus-free.
The Barclays Group does not accept responsibility for any loss arising from unauthorised access to, or interference with, any Internet communications by any third party, or from the transmission of any viruses. Replies to this e-mail may be monitored by the Barclays Group for operational or business reasons.
Any opinion or other information in this e-mail or its attachments that does not relate to the business of the Barclays Group is personal to the sender and is not given or endorsed by the Barclays Group.
Barclays Bank PLC.Registered in England and Wales (registered no. 1026167).
Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom.
Barclays Bank PLC is authorised and regulated by the Financial Services Authority.