Can't see table when using remote module control

[BoulderRidge]

I am trying to run a function in database A from database B. This thread (thread705-892104) plus some modifications actually has me to the place where I can browse from B to A and set the required reference to database A programatically. So it lets me start running the function in database A but as soon as the function refers to a table which lives in database A I get an error: can't find table or query. The table is right there, native to database A but the function in database A no longer sees it and I don't have a clue so far how to fix that. Seems to negate all the other nice progress...any thoughts?

Here is the code...if you plow thru all this you probably have too much time on your hands, but hey...

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'*** IN DATABASE A ***

Public Function RefreshDataRemote(rsReportRows As ADODB.Recordset, ctlStatus As TextBox) As Boolean
'*** This version modified to run remotely from load ***
'Original version saved to run manually as Local
On Error GoTo ErrMe
RefreshDataRemote = True

'Dim stuff here...

rsReportRows.MoveLast
intTotalItems = rsReportRows.RecordCount
rsReportRows.MoveFirst
intCounter = 1

ctlStatus = "Beginning process for " & intTotalItems & " report/row items."
'*** Up to here it works, reads the passed ADO recordset, etc....

strReportCodePrev = rsReportRows!ReportCode
' Fetch the first report information (includes UseDefault flag)

'*** On this function call it crashes because it can't find a local table ***
If Not GetReportInfo(rsReportRows!ReportCode, strHWSelect, strHWFrom) Then
MsgBox "Error refreshing data for report " & rsReportRows!ReportCode & ". Process aborted.", vbCritical + vbOKOnly, "Warning"
End If

'...do more stuff, wrap up
ExitMe:
DoCmd.SetWarnings True
Exit Function

Public Function GetReportInfo(ByVal strReportCodeNow As String, ByRef strHWSelect As String, ByRef strHWFrom As String) As Boolean
'Retrieves select fields from tblLST_Reports
'Parameters are passed by reference and assigned new values in this procedure
On Error GoTo ErrMe
GetHWReportInfo = True

'******* HERE IS WHERE IT CRASHES--tblLST_Reports IS JUST A LOCAL TABLE IN DATABASE A *******
strHWSelect = DLookup("HWSelect", "tblLST_Reports", "ReportCode='" & strReportCodeNow & "'")
strHWFrom = DLookup("HWFrom", "tblLST_Reports", "ReportCode='" & strReportCodeNow & "'")
gblnUseDefaults = DLookup("UseDefaultFilter", "tblLST_Reports", "ReportCode='" & strReportCodeNow & "'")

If IsNull(strHWSelect) Or IsNull(strHWFrom) Then GetReportInfo = False

ExitMe:
Exit Function

ErrMe:
GetReportInfo = False
Resume ExitMe

End Function

'*** IN DATABASE B UNDER FORM (form has text box txtPathFile, txtStatus and command buttons Browse and Refresh ***
Private Sub cmdBrowse_Click()

Call RemoteTest1(Me.txtPathFile)

End Sub

Private Sub cmdRefresh_Click()

If RunRemote(Me.txtStatus) Then
Me.txtStatus = "Report data has been refreshed."
Else
Me.txtStatus = "Refresh failed. Please resolve before using reports."
End If

End Sub

'*** IN DATABASE B PUBLIC MODULE ***
Function RemoteTest1(ctlPathFile As TextBox) As Boolean
'Browse to database containing procedure you want to reference
RemoteTest1 = True
Dim strDB As String
Dim intResponse As Integer

intResponse = MsgBox("Before you proceed, please make sure the XYZ client file 'Refresh Links' connection is pointing to the newly loaded database files.", vbExclamation + vbOKCancel, "Verify client file links")
If intResponse = vbCancel Then
RemoteTest1 = False
GoTo ExitMe
End If

TryAgain:
strDB = GetOpenFile_Load(DBDir(), "Select current XYZ client file")
If strDB = "" Then
intResponse = MsgBox("Please select an Access database containing the current XYZ application (XYZ.mdb).", vbExclamation + vbOKCancel, "Selection required")
If intResponse = vbCancel Then
GoTo ExitMe
Else
GoTo TryAgain
End If
End If
ctlPathFile = strDB

'Before you can run the procedure remotely you have to set a reference to it.
If ReferenceFromFile(strDB) Then
MsgBox "Links and references are ready. Click 'Refresh Report Data' to continue."
Else
MsgBox "Unable to reference current database. Reports were not updated.", vbCritical + vbOKOnly, "Warning"
End If

ExitMe:
On Error Resume Next
DoCmd.SetWarnings True
Exit Function

ErrMe:
Call ShowErr(Err.Number, Err.Description, "cmdExport_Click")
RemoteTest1 = False
Resume ExitMe

End Function

Function ReferenceFromFile(strFileName As String) As Boolean
'Note: gcClientFile is declared public constant elsewhere and assigned the name of the referenced database (XYZ)
Dim ref As Reference
Dim i As Integer

On Error GoTo Error_ReferenceFromFile
Set ref = References.AddFromFile(strFileName)
ReferenceFromFile = True

Exit_ReferenceFromFile:
Set ref = Nothing
Exit Function

Error_ReferenceFromFile:
If Err.Number = 32813 Then
'Reference already exists--drop and relink to currently selected file.
Set ref = References.Item(gcClientFile)
References.Remove ref
Set ref = References.AddFromFile(strFileName)
ReferenceFromFile = True
Else
MsgBox Err & ": " & Err.Description
ReferenceFromFile = False
End If
Resume Exit_ReferenceFromFile

End Function

Public Function RunRemote(ctlStatus As TextBox) As Boolean
On Error GoTo ErrMe
RunRemote = True

Dim cn As ADODB.Connection
Dim rsReportRows As ADODB.Recordset
Dim strReportRowSQL As String

Set cn = CurrentProject.Connection
Set rsReportRows = New ADODB.Recordset
strReportRowSQL = "SELECT ReportCode, RowKeyID, MyFlag, RefreshNow, RefreshDate"
strReportRowSQL = strReportRowSQL & " FROM tblLNK_ReportsToRows"
strReportRowSQL = strReportRowSQL & " WHERE RefreshNow=Yes"
strReportRowSQL = strReportRowSQL & " ORDER BY ReportCode;"

rsReportRows.Open strReportRowSQL, cn, adOpenKeyset, adLockOptimistic
'First report starts outside of loop...
If rsReportRows.EOF Then
MsgBox "No items were selected to refresh.", vbInformation + vbOKOnly, "Refresh status"

ElseIf RefreshDataRemote(rsReportRows, ctlStatus) Then
MsgBox "Report data was refreshed for specified reports.", vbInformation + vbOKOnly, "Process complete"

Else
MsgBox "Report data could not be refreshed; please resolve before running reports.", vbCritical + vbOKOnly, "Warning"
End If


ExitMe:
DoCmd.SetWarnings True
On Error Resume Next
rsReportRows.Close
Set rsReportRows = Nothing
Set cn = Nothing
Exit Function

ErrMe:
Call ShowErr(Err.Number, Err.Description, "RunRemote")
RunRemote = False
Resume ExitMe

End Function
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


Thanks--BoulderRidge B-)

 

[PHV]

Have you tried to play with linked tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BoulderRidge]

That could be a workaround, but truth is I need to manipulate other objects in Database A remotely as well, so even if I could get at the table sideways, then there would be some query objects that would probably have the same problem. I don't understand why the code in database A can't see the objects in database A anymore, just because I launched the code from database B...

--BoulderRidge

 

[ti976]

Just a thought, but if you are calling the refreshDataRemote() from database B, the DLookUp call could be trying to find the tblLST_Reports table in database B and not in A. This assumption is true if you don't have tblLST_Reports in database B. If this is the case, add a link as PHV suggest to tblLST_Reports and if it works okay then what I said is probably true.