Open another access database from the current database and user their data

Question

I have an Access program that is as FE and the BE is SQL database. In startup of the program and for getting information for relinking Access tables to SQL, I want to open another access database in my PC that has some information of the server. How can I open the second access database from vba and get the server info and connection properties from one of its table.

I wrote the following code but I don't know how to refer to the second database in my DLOOKUP !!

Public Function RefreshTableLinks() As String
'On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.TableDef
Dim strConnection As String
Dim strNotUpdated As String
Dim intUpdated As Integer
Dim ODBC As String
Dim strServer As String
Dim Port As Integer
Dim strDataBaseName As String
Dim strUserName As String
Dim strPassword As String
Dim TrustedConnection As Boolean
Dim dbConnectionInfo As DAO.Database
Dim wsAccess As Workspace

'Open the data base that hold the connection information
Set wsAccess = DBEngine(0)
Set dbConnectionInfo = DBEngine.OpenDatabase("J:\Kraseh\ConnectionInfo.accdb", True, True)
'Get the necessary information from database

Debug.Print DBEngine(0)(1).Name
strServer = Trim(DLookup("Server", dbConnectionInfo.TableDefs(tblConnectionInfo), "ID=1"))
Port = DLookup("Port", "tblConnectionInfo", "ConnectionID=1")
strDataBaseName = Trim(DLookup("DataBaseName", "tblConnectionInfo", "ID=1"))
strUserName = Trim(DLookup("UserName", "tblConnectionInfo", "ID=1"))
strPassword = DLookup("Password", "tblConnectionInfo", "ID=1")
TrustedConnection = DLookup("TrustedConnection", "tblConnectionInfo", "ID=1")

Answer 1

Hi. There are many ways to do that, but the easiest way would be to use the IN operator when you create a query to return the value from the external table.

For example:

SELECT * FROM tblConnectionIfo IN('J:\Kraseh\ConnectionInfo.accdb') WHERE ID=1

Hope that helps...