A.D. Tejpal
adtp at airtelmail.in
Wed Sep 1 01:03:10 CDT 2010
AddIns
=====
J.C.,
Once an AddIn is in loaded state, CodeDb & CodeProject refer to the
AddIn, while CurrentDb & CurrentProject refer to host db. For using host db
data as record source for a form belonging to the AddIn, four alternatives
are available:
Style 1 - Using a query executed via CodeDb, so as to populate a table
belonging to the AddIn. With this approach, there is no need to use the "IN
'DbPath'" syntax.
Style 2 - Using a recordset created via CodeDb, so as to populate a
table belonging to the AddIn. Here too, there is no need to use the "IN
'DbPath'" syntax.
Style 3 - Using a query executed via CurrentDb, so as to populate a
table created in host db. In this case, use of "IN 'HostDbPath'" syntax
becomes necessary.
Style 4 - Using a recordset created via CurrentDb, so as to populate a
table beloging to the AddIn. In this case, use of "IN 'AddInPath'" syntax
becomes necessary.
Sample code in load event of form belonging to the AddIn, for all the
four alternative styles mentioned above, is placed below (at the end of this
post).
Practical use of various alternatives is demonstrated in my sample AddIn
named Reports_AddIn. Only one form conforming to style 1 is in active use,
while other forms included for the purpose of demonstrating styles 2 to 4
are prefixed with "ZZZ". The sample AddIn is in access 2000 file format and
is available at Rogers Access Library. Link -
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
Certain interesting factors pertaining to Add-Ins in general, are
summarized below for ready reference:
1 - For code contained in modules belonging to the Add-In:
(a) If the AddIn is opened independently, CurrentProject / CurrentDb
are identical to CodeProject / CodeDb respectively (all refer to AddIn
itself).
(b) If the AddIn is activated in host db, CurrentProject / CurrentDb
refer to host db while CodeProject / CodeDb refer to AddIn itself.
(c) Host applications AllForms / AllReports / AllModules collections
can be accessed by code in AddIn, using CurrentProject.
(d) Tables / Queries are not covered by collections like those in
prev para.
(e) While the AddIn is in activated state, any query / table / form
/ report, whether in host db or in the AddIn, as well as modules in host db
can be opened directly by name, using DoCmd statement in AddIn code, without
having to use any special qualifier.
(f) If any pair of similar objects exists both in the AddIn as well
as the host db, code in AddIn module will act upon the object located in the
AddIn itself.
(g) For code in AddIn module, when the AddIn is in activated mode,
queries / recordests based upon CurrentDb do not recognize the tables in
AddIn itself, unless referred via IN clause, giving full path of the AddIN.
This path, returned by CodeProject.FullName, refers to the original location
of AddIn mda file e.g. C:\SelfSaveAddIn\SelfSaveAddIn.mda and not the shadow
AddIn file in AddIns folder in MyDocuments. There is no such problem if
queries / recordsets are based upon CodeDb instead of CurrentDb.
(h) Modules belonging to the AddIn can not be opened by DoCmd mehod
or otherwise addressed, even by code in the AddIn. DoCmd.CopyObject command
can however be used for copying modules from AddIn to host db.
2 - For code contained in modules belonging to the host db:
(a) CurrentProject / CurrentDb are identical to CodeProject / CodeDb
respectively, always referring to the host db, irrespective of the fact
whether the AddIn is in activated state or not.
(b) Whereas code in the AddIn can access all tables / queries /
forms / reports, whether belonging to AddIn or the host db (see para 1 (e)
above), code in host db can directly access only the objects belonging to
host db itself.
======================================
Best wishes,
A.D. Tejpal
------------
' Code in load event of form belonging to the AddIn
' LstReports is the name of list box located on this form
' CodeDb & CodeProject refer to the AddIn
' while CurrentDb & CurrentProject refer to host db
'===================================
' Style 1 - Using append query executed via CodeDb
' (Table TA_Reports belongs to the AddIn)
'-----------------------------------------------------
Private Sub Form_Load()
On Error GoTo ErrTrap
Dim db As DAO.Database
Dim obj As AccessObject
Dim Qst As String
' In AdIn's code modules, when AddIn is
' opened independently, CurrentProject as
' well as CodeProject refer to the AddIn.
' When AddIn is loaded in host db,
' CurrentProject refers to the host db
' and CodeProject refers to the AddIn.
Set db = CodeDb ' Refers to AddIn
' Clear table TA_Reports in the AddIn
Qst = "DELETE FROM TA_Reports;"
db.Execute Qst, dbFailOnError
If CurrentProject.AllReports.Count > 0 Then
Else
Me.LstReports.Requery
GoTo ExitPoint
End If
' Cycle through AllReports collection of host db
' and append report names to table TA_Reports
' in the AddIn
For Each obj In CurrentProject.AllReports
Qst = "INSERT INTO TA_Reports " & _
"(RepName) VALUES ('" & _
obj.Name & "');"
db.Execute Qst, dbFailOnError
Next
With Me.LstReports
.Requery
.SetFocus
.Selected(0) = True
End With
ExitPoint:
On Error Resume Next
Set obj = Nothing
Set db = Nothing
On Error GoTo 0
Exit Sub
ErrTrap:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
' Note - Row source for list box is permanently
' set to query QA_Reports of AddIn
End Sub
'-----------------------------------------------------
' Style 2 - Using recordset opened via CodeDb
' (Table TA_Reports belongs to the AddIn)
'-----------------------------------------------------
Private Sub Form_Load()
On Error GoTo ErrTrap
Dim db As DAO.Database
Dim obj As AccessObject
Dim rst As DAO.Recordset
Dim Qst As String
' In AdIn's code modules, when AddIn is
' opened independently, CurrentProject as
' well as CodeProject refer to the AddIn.
' When AddIn is loaded in host db,
' CurrentProject refers to the host db
' and CodeProject refers to the AddIn.
Set db = CodeDb ' Refers to AddIn
' Clear table TA_Reports in the AddIn
Qst = "DELETE FROM TA_Reports;"
db.Execute Qst, dbFailOnError
If CurrentProject.AllReports.Count > 0 Then
Else
Me.LstReports.Requery
GoTo ExitPoint
End If
' Cycle through AllReports collection of host db
' and append report names to table TA_Reports
' in the AddIn
Set rst = db.OpenRecordset("TA_Reports")
For Each obj In CurrentProject.AllReports
With rst
.AddNew
.Fields(0) = obj.Name
.Update
End With
Next
rst.Close
Set rst = Nothing
With Me.LstReports
.Requery
.SetFocus
.Selected(0) = True
End With
ExitPoint:
On Error Resume Next
Set obj = Nothing
Set db = Nothing
On Error GoTo 0
Exit Sub
ErrTrap:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
' Note - Row source for list box is permanently
' set to query QA_Reports of AddIn
End Sub
'-----------------------------------------------------
' Style 3 - Using append query executed via CurrentDb
' (Table TA_Reports is created in host db via code in
' AddIn)
'-----------------------------------------------------
Private Sub Form_Load()
On Error Resume Next
Dim db As DAO.Database
Dim obj As AccessObject
Dim Qst As String
' Clear the row source for list box
' (so as to free up table TA_Reports)
Me.LstReports.RowSource = ""
' Note - In this particular case, the above
' statement is redundant as no permanent
' row source has been assigned to the list
' box. (Retained to demonstrate the idea).
' In AdIn's code modules, when AddIn is
' opened independently, CurrentProject as
' well as CodeProject refer to the AddIn.
' When AddIn is loaded in host db,
' CurrentProject refers to the host db
' and CodeProject refers to the AddIn.
If CurrentProject.AllReports.Count > 0 Then
Else
Exit Sub
End If
Set db = CurrentDb ' Refers to host db
' Delete table TA_Reports in host db - if existing
Qst = "DROP TABLE TA_Reports;" ' (A)
db.Execute Qst, dbFailOnError
' Create table TA_Reports in host db
Qst = "CREATE TABLE TA_Reports " & _
"(RepName TEXT);" ' (B)
db.Execute Qst, dbFailOnError
' Cycle through AllReports collection and
' append report names to table TA_Reports
' in host db
For Each obj In CurrentProject.AllReports
Qst = "INSERT INTO TA_Reports " & _
"(RepName) VALUES ('" & _
obj.Name & "');"
db.Execute Qst, dbFailOnError ' (C)
Next
Set obj = Nothing
Set db = Nothing
' Set the row source for list box, based upon
' table TA_Reports in host db
Me.LstReports.RowSource = _
"SELECT RepName " & _
"FROM TA_Reports IN '" & _
CurrentProject.FullName & _
"' ORDER BY RepName;" ' (D)
Me.LstReports.SetFocus
Me.LstReports.Selected(0) = True
On Error GoTo 0
' Note - When AddIn is loaded, statements (A) to (C)
' look for table TA_Reports in host db only.
' In statement (D) IN clause identifying the db
' to which the table belongs, is found necessary.
' (Apparently on account of the fact that the
' form & table do not belong to same db).
End Sub
'-----------------------------------------------------
' Style 4 - Using recordset opened via CurrentDb
' (Table TA_Reports belongs to the AddIn)
'-----------------------------------------------------
Private Sub Form_Load()
On Error GoTo ErrTrap
Dim db As DAO.Database
Dim obj As AccessObject
Dim rst As DAO.Recordset
Dim Qst As String
' In AddIn's code modules, when AddIn is
' opened independently, CurrentProject as
' well as CodeProject refer to the AddIn.
' When AddIn is loaded in host db,
' CurrentProject refers to the host db
' and CodeProject refers to the AddIn.
Set db = CurrentDb ' Refers to host db
' Clear table TA_Reports in the AddIn
Qst = "DELETE FROM TA_Reports " & _
"IN '" & CodeProject.FullName & "';"
db.Execute Qst, dbFailOnError
If CurrentProject.AllReports.Count > 0 Then
Else
Me.LstReports.Requery
GoTo ExitPoint
End If
' Cycle through AllReports collection of host db
' and append report names to table TA_Reports
' in the AddIn
Qst = "SELECT * FROM TA_Reports " & _
"IN '" & CodeProject.FullName & "';"
Set rst = db.OpenRecordset(Qst)
For Each obj In CurrentProject.AllReports
With rst
.AddNew
.Fields(0) = obj.Name
.Update
End With
Next
rst.Close
Set rst = Nothing
With Me.LstReports
.Requery
.SetFocus
.Selected(0) = True
End With
ExitPoint:
On Error Resume Next
Set obj = Nothing
Set db = Nothing
On Error GoTo 0
Exit Sub
ErrTrap:
MsgBox Err.Number & " - " & Err.Description
Resume ExitPoint
' Note - Row source for list box is permanently
' set to query QA_Reports of AddIn
End Sub
'-----------------------------------------------------
----- Original Message -----
From: jwcolby
To: Access Developers discussion and problem solving
Sent: Wednesday, September 01, 2010 07:07
Subject: [AccessD] Leave a bound form in a library.
One of the challenges of using an "addin" library is placing forms in the
add-in but having them
load records from the FE. For example if I make a bound form to load the
users table but I leave
that form in the library (add-in), then when the form opens it tries to load
the data from the table
in the library. IOW a form tries to pull its data from whatever container
it is placed in.
One way around that is to use the "in 'MyFePath\MyDbName.mdb' syntax that is
valid in access. As an
example SQL to pull the records from the FE might look as follows:
SELECT * FROM usystblPLSObjFrm in 'C:\Dev\C2DbPLS\C2DbPLS.mda' ORDER BY
PLSF_Name;
This would obviously pull all fields from the table usystblPLSObjFrm
physically located in
C:\Dev\C2DbPLS\C2DbPLS.mda.
This is a PITA and requires jumping through hoops but that is the only
solution that I know of.
If any of you know of another solution please let me know since I do have to
get adept at hoop
jumping to do it my way.
Thanks,
--
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com