Get Current Database

In a back-end database environment, we almost never create table or query in Access database that executed as the current application. Access database that executed as the current application, also known as front-end database, is almost never used to store a table or query. In most case, the table or query stored in the front-end database is temporary in nature, so that later will be deleted soon after no longer needed.


However, we still need the table or query stored in the front-end database, especially when editing data that involve both front-end and back-end database. In such this situation, we need local database name (in this case - the front-end database), in which we create query that involve back-end database.

To get the local database name, we use the following function:

Function getCurrentDbs() As String
On Error GoTo Err_Msg
  getCurrentDbs = "[Database=" & CurrentProject.Path & "\" & CurrentProject.Name & ";MS ACCESS]"
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function getCurrentDbs, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The above function use CurrentProject object with Path property as the database folder path name and Name property as the database name that is being opened. Besides that, the connection string parameter has MS Access as the value. This connection string indicate database provider name, i.e MS Access.

The string value of getCurrentDbs function is almost similar with getExternalDbs function. They consist both database file path name and its connection string. The difference is, getCurrentDbs shows the string value of database being open in a front-end. Meanwhile, getExternalDbs shows the string value of external database in a back-end.

Implementation:

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

?getCurrentDbs
[Database=D:\Access\MeAccess.accdb;MS ACCESS]

The following example show

Function fnExample()
  Dim rs As DAO.Recordset
  Dim strSql As String
  'using external database as shown in the letDbsOpen function below
  letDbsOpen
  'using existing table/query stored in external database and create query combined with table in the current database
  strSql = "SELECT * FROM qryVoucherTemp as q3 " & _
                 "INNER JOIN  (SELECT vouchId as vouchIds, chkSelected FROM " & getCurrentDbs & ".tblTempVoucherTemp AS q2) as q1 ON q1.vouchIds = q3.vouchId " & _
                 "WHERE q1.chkSelected = True"
  Debug.Print strSql
  'set the recordset using strSql and connected to external database
  Set rs = daoDbs.OpenRecordset(strSql)
  rs.Close
  Set rs = Nothing
  letDbsClose
End Function

The above function combines the query named qryVoucherTemp and is stored in external database (which is back-end database) with temporary table named tblTempVoucherTemp and is stored in local/current database (which is front-end database) using SQL statement set by strSql string variable. The strSql is set to be recordsets. In the following statement:

Set rs = daoDbs.OpenRecordset(strSql)

there are two databases involved:

1. Using getCurrentDbs function, we create query based on table stored in the current database (front-end)
2. Using object daoDbs setting, we create query based on existing query named qryVoucherTemp stored in the external database (back-end)

The result of Debug.Print strSql in the above function is as follows:

SELECT * FROM qryVoucherTemp as q3 INNER JOIN  (SELECT vouchId as vouchIds, chkSelected FROM [Database=D:\Access\MeAccess.accdb;MS ACCESS].tblTempVoucherTemp AS q2) as q1 ON q1.vouchIds = q3.vouchId WHERE q1.chkSelected = True 

In the strSql, getCurrentDbs function equal with [Database=D:\Access\MeAccess.accdb;MS ACCESS].