Using ADO connection dialog in MS Access/VBA

Question

Hi all,

I want to use the standard ADO connection string dialog box in MS Access. How can I do that?

Answer 1

See How to bind Microsoft Access forms to ADO recordsets

Answer 2

If not already selected, check the following references in the References dialog:

Microsoft OLE DB Service Component 1.0 Type Library

Microsoft ActiveX Data Objects 2.7 Library

The following code will open the dialog box and set a connection object to the parameters provided in the Data Link Properties dialog box:

Dim dl As MSDASC.DataLinks
Dim cn As ADODB.Connection

Set dl = New MSDASC.DataLinks
Set cn = New ADODB.Connection

Set cn = dl.PromptNew
cn.Open

You can then open an ADODB recordset with the connection or retrieve properties about the connection and proceed as needed.

Answer 3

In addition to prerequisites as per John Mo, the following function will return you a full ADO connection string. It can be initiated with a default 'm_connectionString' value. Such a value can be stored in a 'connections table'.

Function connectToDababase(Optional m_connectionString As String) As String
Dim dl As MSDASC.DataLinks
Dim cn As ADODB.Connection

Set dl = New MSDASC.DataLinks
Set cn = New ADODB.Connection

If IsMissing(m_connectionString) Then
    Set cn = dl.PromptNew
Else
    cn.ConnectionString = m_connectionString
    dl.PromptEdit cn
End If

connectToDababase = cn.ConnectionString
End Function