question

Amir-1400 avatar image
0 Votes"
Amir-1400 asked thedbguy commented

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

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")


office-vba-devoffice-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

thedbguy avatar image
0 Votes"
thedbguy answered thedbguy commented

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...

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear Dbguy,

Thanks for your reply, but I don't know where in my code I should use that code. In DLookup() or in another place!!

0 Votes 0 ·

Okay, how about this?

Take the SQL statement I gave you and create a query out of it. Then, you can use DLookup() against that query.

1 Vote 1 ·