Change Data Link Properties programmatically

Posted on 2008-01-31
Medium Priority
Last Modified: 2008-01-31
Is it possible to change the data link properties or the connection string for a .adp when the file is opened?

I would like to user NT integrated security in one location and a SQL server user account in another.

Many thanks
Question by:dchoxford
1 Comment
LVL 28

Accepted Solution

TextReport earned 2000 total points
ID: 20788424
I use the sample code below.
Cheers, Andrew
Function ChangeADPConnection(strServerName As String, strDBName As String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1;Data Source=" & strServerName & ";Initial Catalog=" & strDBName
If strUN <> "" Then
    strConnect = strConnect & ";user id=" & strUN
    If strPW <> "" Then strPW = Chr(34) & Chr(34)
    strConnect = strConnect & ";password=" & strPW
Else  'Try to use integrated security if no username is supplied.
    strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function

Open in new window


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question