CODEWITHSUNDEEP
ms-accessado
Levend
Levend

Reputation:

Using ADO connection dialog in MS Access/VBA

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

Upvotes: 2

Views: 5040

Answers (3)

Philippe Grondier
Philippe Grondier

Reputation: 11138

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

Upvotes: 1

John Mo
John Mo

Reputation: 1326

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.

Upvotes: 1

Galwegian
Galwegian

Reputation: 42227

See How to bind Microsoft Access forms to ADO recordsets

Upvotes: -1

Related Questions