[AccessD] Set references via automation

Dan Waters df.waters at comcast.net
Sun Jan 1 08:41:37 CST 2012


I William,

I've been setting up recordsets like this for many years now with no known
issues.  So, perhaps someone else can weigh in on any cons to this approach!

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
Sent: Saturday, December 31, 2011 10:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Set references via automation

Is it not best to set an object equal to Codedb and then use the
OpenRecordset property (or method, I continually mix those) of the object
rather than based on CodeDb directly?

That seems to be a safety rule for CurrentDB and I would expect the two
classes to behave similarly?

Or not. Not really even sure what I am talking about.

NICE FUNCTIONS THOUGH!

HYAPPEY NIEW YEAH!
On Dec 31, 2011 10:34 AM, "Dan Waters" <df.waters at comcast.net> wrote:

> Yes!  I think you'll find this helpful.  This is the very first 
> procedure that is run when the app opens.  I use an AutoExec macro, 
> and it runs this procedure first.
>
> The stgDeveloperLibraryPath is taken from a table, but it could be 
> hardcoded, or in a local table.
>
> HTH!
> Dan
>
>
> '--------------------------------
>
> Public Function ResetLibraryReference()
> 1     On Error GoTo EH
>
>          '-- Purpose:  This will reset the library reference to ensure 
> that the FE file is referencing the correct Library file. _
>                        On a client PC, this isn't a problem, but that 
> reference does need to be reset on the server.  Otherwise, _
>                        the TEST FE could reference the PROD library, etc.
>
>          Dim ref As Access.Reference
>          Dim stgCurrentPath As String
>          Dim stgPrompt As String
>          Dim stgDeveloperLibraryPath As String
>          Dim appFE As Access.Application
>          Dim intCount As Integer
>
> 2         Set appFE = CurrentProject.Application
>
> 3         If Environ("ComputerName") = "DanWaters" Then  '-- Developer PC
>
> 4             stgDeveloperLibraryPath =
> ReadPSIParameter("DeveloperLibraryPath")
>
>              '-- Is the correct Library already referenced?
> 5             For Each ref In appFE.References
> 6                 If ref.FullPath = stgDeveloperLibraryPath Then
> 7                     Exit Function
> 8                 End If
> 9             Next ref
>
>              '--  Find and delete the incorrect library reference
> 10            For Each ref In appFE.References
> 11                If InStr(ref.FullPath, "PSILibrary") <> 0 Then
> 12                    appFE.References.Remove ref
> 13                End If
> 14            Next ref
>
>              '-- Add the correct library reference
> 15            Access.References.AddFromFile stgDeveloperLibraryPath
>
> 16        Else
>
> 17            stgCurrentPath = CurrentProject.Path
>
> 18            For intCount = appFE.References.Count To 1 Step -1
> 19                Set ref = appFE.References(intCount)
> 20                If InStr(ref.Name, "PSILibrary") <> 0 Then
> 21                    appFE.References.Remove ref
>      '22                    MsgBox appFE.References.Count  '-- TEST
>      '23                    MsgBox "Removed Library Reference", vbOKOnly +
> vbInformation, "Removed Reference"  '-- TEST
> 22                End If
> 23            Next
>
>              '-- Add the correct library reference
> 24            appFE.References.AddFromFile stgCurrentPath &
> "\PSILibrary.mdb"
>      '27            MsgBox "Added Library Reference", vbOKOnly +
> vbInformation, "Added Reference"  '-- TEST
>      '28            MsgBox appFE.References.Count  '-- TEST
>
> 25        End If
>
>          '-- Call a hidden SysCmd to automatically compile/save all 
> modules.
> 26        Call SysCmd(504, 16483)
>
> 27        Exit Function
>
> EH:
> 28        stgPrompt = "This system has experienced a startup error in the
> ResetLibraryReference code procedure." _
>              & vbNewLine & vbNewLine _
>              & "Line: " & Erl & vbNewLine _
>              & "Number: " & Err.Number & vbNewLine _
>              & "Description: " & Err.Description _
>              & vbNewLine & vbNewLine _
>              & "Contact the system developer.  This system will now Quit."
> _
>              & vbNewLine & vbNewLine _
>              & "NOTE:  Be sure the library file project name = 
> 'PSILibrary'."
> 29        MsgBox stgPrompt, vbCritical & vbOKOnly, "Startup Error"
> 30        Application.Quit
>
> End Function
>
> '--------------------------------
> Public Function ReadPSIParameter(stgParameter As String) As String
>
>    Dim stg As String
>    Dim rst As DAO.Recordset
>
>    stg = "SELECT " & stgParameter & " FROM tblPSIParameters IN '" & 
> CurrentProject.Path & "\PSIConfig.mdb" & "'"
>    Set rst = CodeDb.OpenRecordset(stg, dbOpenForwardOnly)
>    ReadPSIParameter = rst(stgParameter)
>    rst.Close
>    Set rst = Nothing
>
>    Exit Function
>    ErrEx.Bookmark = BOOKMARK_ONERROR
>
> End Function
> '--------------------------------
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Saturday, December 31, 2011 6:34 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Set references via automation
>
> Is it possible to set references in an access container (Lib or FE) 
> via automation?  Open the access container and then set a reference in 
> that container, all from code running in the object that is performing 
> the automation?
>
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list