Get External Database

In some circumstances when opening a front-end database, we may use a record source of a form that bound to a back-end database. MS Access does provide linked table that can be bound to a record source of a form or report, either through a table as is or a query. The problem is, that linked table is displayed on Access Navigation pane. Though it may be hidden, using external database in the form of linked table is risky. Nevertheless, we do not want any table that comes from back-end database is displayed on Access Navigation pane, in order avoiding from user who may open the linked table directly from Access Navigation Pane.

To avoid using linked tables, we can connect to an external database that will be bound to a form by writing VBA code. If an Access database that has .accdb extension is to be converted into .accde, then writing VBA code to later bound to a record source becomes a good choice and is secured. Users do not know, what table is opened. We only need to add database source and its connection string to be used in creating table or query bound to a form's record source. The database source and its connection string are set using getExternalDbs function. The output of this function is a string value indicates the database file path name and its connection string. The getExternalDbs function is as follows:

Function getExternalDbs() As String
On Error GoTo Err_Msg
  getExternalDbs = "[Database=" & getDbsLocation & ";" & getDbsConString & "]"
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getExternalDbs, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

In the above function, the database source is a file path set by getDbsLocation function, while the connection string is set by getDbsConString function.

Implementation:

If the getExternalDbs is executed under Immediate Window, the result will be:

?getExternalDbs
[Database=D:\Access\Database_be.accdb;MS ACCESS;PWD=abcd]

Supposed that we use a back-end database which has a table named tblPrimaryAccounts. To load data in front-end database, we write a SQL statement using VBA code as follows:

?"SELECT * FROM " & getExternalDbs & ".tblPrimaryAccounts"
SELECT * FROM [Database=D:\Access\Database_be.accdb;MS ACCESS;PWD=abcd].tblPrimaryAccounts

Or

?"SELECT * FROM tblPrimaryAccounts IN '' " & getExternalDbs 
SELECT * FROM tblPrimaryAccounts IN '' [Database=D:\Access\Database_be.accdb;MS ACCESS;PWD=abcd]

As for a trial, we can create a query in front-end database. Just click Create > Query > Design View. Next, switch the view to SQL View, and then, copy the above SQL Statement and paste it into the SQL View. We can see the result by switching to Datasheet View.

We can also bind the above SQL Statement in a form's or report's record source. If it is bound to a form, then the SQL statement must be created when the form is opened, utilizing the form's Open event. Besides, binding SQL Statement to a form's record source can be triggered using button's Click event, text box's AfterUpdate event, etc.

If it is bound to a report, the above SQL Statement is created at the time the report is opened, using report's Open event. As for the information, a report in MS Access database other than .adp extension, which no table or query in front-end database, could not use recordsets collection. Therefore, to display a report, we should use the above SQL Statement, that involves external database, as the report's record source.