Application.Run (2 Viewers)

[TheSearcher]

I’m trying to run a procedure in an external database from my current Microsoft Access DB.
The External database and project name is IR_Bridge.
The procedure name is RefreshClients.
RefreshClients consists of two queries: One deletes the contents of a table and another appends to it.
The procedure, when run by itself, always works perfectly.
However, when I execute the Application.Run command from the current db the query that deletes the contents of a table runs without error - but the append query throws an error stating that “The Microsoft Access database cannot find the input table or query tbl_ClientMaster”.
The table is definitely there and , as I mentioned, the procedure runs perfect on its own.
My code from the calling db is:

Sub RunExternalFunction()
    Application.Run "IR_Bridge.RefreshClients"
End Sub

Does anyone know why tbl_ClientMaster isn't recognized?

Thanks in advance,
TS

 

[theDBguy]

Is RefreshClients a public procedure in the external database? What do you mean it contains two queries? Can you show us the code for RefreshClients? How is it executing those queries? Does the external database both have code and data stored in it? Or, is the external database linked to the tables in yet another separate database?

 

[TheSearcher]

@theDBguy - thank you for responding.
Yes. RefreshClients is a public procedure in the external database. It contains two queries (see code below). The external database does have code and data. It has one table linked to a SqlServer database. It has another local table that another Access interface connects to which is used by hundreds of users that I don't wish to have access to the SqlServer database. I want them to always have a fresh copy of the client master file without actually granting them access to the SqlServer table. I hope that makes sense. Any insight you can offer would be greatly appreciated.

Public Sub RefreshClients()

Dim sql1 As String, sql2 As String

sql1 = "DELETE DISTINCTROW tbl_Clients.* FROM tbl_Clients;"
DoCmd.SetWarnings False
DoCmd.RunSQL sql1
DoCmd.SetWarnings True

sql2 = "INSERT INTO tbl_Clients ( Client_Id, Client_FirstName, Client_LastName, Res_Out, Location_Code, Client_Type ) "
sql2 = sql2 & "SELECT tbl_ClientMaster.Client_Id, tbl_ClientMaster.Client_FirstName, tbl_ClientMaster.Client_LastName, tbl_ClientMaster.Res_Out, "
sql2 = sql2 & "tbl_ClientMaster.Location_Code, tbl_ClientMaster.Client_Type "
sql2 = sql2 & "FROM tbl_ClientMaster;"
DoCmd.SetWarnings False
DoCmd.RunSQL sql2
DoCmd.SetWarnings True

End Sub

 

[Jason Lee Hayes]

Cleaner:

Public Sub RefreshClients()

    Dim sqlDelete As String
    Dim sqlInsert As String

    ' Delete all rows from tbl_Clients
    sqlDelete = "DELETE FROM tbl_Clients;"
    CurrentDb.Execute sqlDelete, dbFailOnError

    ' Insert fresh data from tbl_ClientMaster
    sqlInsert = _
        "INSERT INTO tbl_Clients (Client_Id, Client_FirstName, Client_LastName, Res_Out, Location_Code, Client_Type) " & _
        "SELECT Client_Id, Client_FirstName, Client_LastName, Res_Out, Location_Code, Client_Type " & _
        "FROM tbl_ClientMaster;"
    CurrentDb.Execute sqlInsert, dbFailOnError

End Sub

 

[sonic8]

CurrentDb.Execute

In this context it might be necessary to use CodeDb.

 

[TheSearcher]

@theDBguy - I had to link the SqlServer table (tbl_ClientMaster) to my current (calling) interface even though it isn't being used. The same exact DSN and Drivers needed to be in both databases. Now it works.

 

[theDBguy]

@theDBguy - I had to link the SqlServer table (tbl_ClientMaster) to my current (calling) interface even though it isn't being used. The same exact DSN and Drivers needed to be in both databases. Now it works.

Glad to hear you got it sorted out. Good luck with your project!

 

[isladogs]

Although the easiest solution is just to link to all tables required (as you have now done), it is also possible to create queries to external databases without linking tables using syntax such as:

SELECT Field1, Field2, Field3 FROM [Full path to external database].TableName;

For example:

SELECT
    ID,
    ErrorNumber,
    ErrorDescription
FROM
    [G:\MyFiles\ExampleDatabases\Access Error Codes\AccessErrorCodes v3.2.accdb].tblAccessErrorCodes;