|
The information in this article applies to:
- Microsoft Excel 2000 and above.
External References:
- Microsoft ActiveX Data Objects x.x Library
- Microsoft OLE DB Service Component 1.0 Type Library
| Task |
| |
One of the more important steps when accessing databases via ODBC and ADO is to create the connectionstring correct. In this article code is presented for creating strings for setting up the connections.
In addition a free add-in is available for download which include as well a procedure that automatically place the string into the clipboard. When installing the add-in a new button is available on the standard commandbar in the VB-editor. |
| |
|
| Code |
| |
Option Explicit
Sub Create_Connectionstring()
Dim objDL As MSDASC.DataLinks
Dim cnt As ADODB.Connection
Dim stConnect As String
'Instantiate the objects.
Set objDL = New MSDASC.DataLinks
Set cnt = New ADODB.Connection
On Error GoTo Error_Handling
'Show the Data-link wizard
stConnect = objDL.PromptNew
'Test the connection.
cnt.Open stConnect
'Print the string to the Immediate Windows.
Debug.Print stConnect
'Release the objects from memory.
ExitHere:
cnt.Close
Set cnt = Nothing
Set objDL = Nothing
Exit Sub
Error_Handling:
'If the user cancel the operation.
If Err.Number = 91 Then
Resume ExitHere
End If
End Sub |
| |
|
| Comments |
| |
An easy and simpel way for creating connectionstring.
If connecting to the database seems to take time and if you regular get an errormessage that due to timeout it was not possible to connect to the database then use the following approach, which will assure no timeout-message:
cnt.CommandTimeOut = 0 |
| |
|
| Last Reviewed: |
2004-08-21 |
| Keywords: |
ADO, Connectionstring |
|
