Database nightmare!!! 1

[01491]

I have some code that opens up another database (reference db1)


Dim appAccess As Access.Application
Dim strDB As String

strDB = "R:\Rad\RAD Application 97.mdb"
Set appAccess = _
CreateObject("Access.Application.8&quot
appAccess.OpenCurrentDatabase strDB


This works fine, but when I add the following line, trying to activate a module within this database, it doesn't recognise any of the forms or tables in the module code.(ie code from db1).Incidently, 'RAPlink' is module name and 'UpdateRAP' is the sub name.

db1.RAPlink.UpdateRAP

Here is the module code:

Sub UpdateRAP()
Dim intCurrentRow As Integer
Dim strCoreActivityFilter As String

DoCmd.SetWarnings False
For intCurrentRow = 0 To Forms!frmOpen!List158.ListCount
strCoreActivityFilter = "([tblriskactivity]![CoreActivity])='" & Forms!frmOpen!List158.Column(0, intCurrentRow) & "' "
If intCurrentRow = 0 Then
DoCmd.RunSQL " SELECT DISTINCT TOP 5 tblRiskActivity.RiskID, tblRisk.Risk, qrySingleRating.Rating, tblRiskActivity.CoreActivity INTO tblRAPlink FROM (tblRisk INNER JOIN tblRiskActivity ON tblRisk.RiskID = tblRiskActivity.RiskID) INNER JOIN qrySingleRating ON tblRisk.RiskID = qrySingleRating.RiskID WHERE (" & strCoreActivityFilter & " AND ((tblRisk.Entity)='Global')) ORDER BY qrySingleRating.Rating DESC ;"
Else
DoCmd.RunSQL "INSERT INTO tblRAPlink SELECT DISTINCT TOP 5 tblRiskActivity.RiskID, tblRisk.Risk, qrySingleRating.Rating, tblRiskActivity.CoreActivity FROM (tblRisk INNER JOIN tblRiskActivity ON tblRisk.RiskID = tblRiskActivity.RiskID) INNER JOIN qrySingleRating ON tblRisk.RiskID = qrySingleRating.RiskID WHERE (" & strCoreActivityFilter & " AND ((tblRisk.Entity)='Global'))ORDER BY qrySingleRating.Rating DESC ;"
End If
Next intCurrentRow

End Sub


Any help much appreciated!!!

 

[jfischer]

A couple of things.

1) If you are opening the database "db1" in a second instance of Access, you do not need to put "db1" in the References of your main program. You refer to the second instance of "db1" as "appAccess.CurrentDb", not as "db1."

2) If you "do" put "db1" in your main program References, you do not need to open a second instance of Access to use it. But, you should make your "UpdateRAP" procedure "Public", i.e.:

Public Sub UpdateRAP()
...
End Sub

Otherwise, the procedure won't be visible to the main program.