Changing Connection - proving SQL login and password

[cupofteawithsugar]

Hi All,

I have Windows Authenication for my users using the ADP project which works fine for all tasks except one.

I have a DTS package and for this I have to change the ADP connection to an SQL login. I would then in the VB code like to execute the DTS package. I need this to be done all behind the form so the users sees nothing.

I found this code on the forum


On Error GoTo ErrHandler
Dim oConn As ADODB.Connection
Dim oComm As ADODB.Command
Set oConn = New ADODB.Connection
Set oComm = New ADODB.Command
'====
oConn.ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;;Data Source=servername;Persist Security Info=True;" _
& "Initial Catalog=Database;Trusted_Connection=Yes;Data Provider=SQLOLEDB.1"
can you change this to have sa password and username?
oConn.Open
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdUnknown
oComm.CommandText = "DTSpacakgename" would this work?
oComm.Execute


Exit Sub
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; err.Description
For Each er In cn.Errors
Debug.Print "err num = "; err.Number
Debug.Print "err desc = "; err.Description
Debug.Print "err source = "; err.Source
Next

Any ideas or a point in the right direction would be great.

Thanks

 

[cmmrfrds]

Check this site for connection strings.

http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx