Mensaje enviado por "VbNick" <mjli@mundo-r.com> el 25/02/2002
Hola Ariel, te paso un código con varias formas de abrir conexión con SQLServer
Public Sub ConnectionString()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection
Dim cnn4 As ADODB.Connection
' Abre una conexión sin usar un Data Source Name (DSN).
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd;database=pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
' Abre una conexión usando DSN y ODBC.
Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
cnn2.Open
' Abre una conexión usando DSN y OLE DB.
Set cnn3 = New ADODB.Connection
cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
cnn3.Open
' Abre una conexión que usa un DSN y argumentos individuales
' en lugar de una cadena de conexión.
Set cnn4 = New ADODB.Connection
cnn4.Open "Pubs", "sa", "pwd"
' Muestra el estado de las conexiones
MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
"cnn2 state: " & GetState(cnn2.State) & vbCr & _
"cnn3 state: " & GetState(cnn3.State) & vbCr & _
"cnn4 state: " & GetState(cnn4.State)
cnn4.Close
cnn3.Close
cnn2.Close
cnn1.Close
End Sub
Public Function GetState(intState As Integer) As String
Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select
End Function
Y a continuación un código que abre un Stored Procedure sacado de SQLMax
Public Sub TestParameter
' This code sample demonstrates the following:
' 1. Creating a stored procedure that uses input/output parameters
' 2. Creating the parameters and firing the stored procedure
' 3. Displaying the result and delete the stored procedure
Dim conn As ADODB.Connection
Dim Param As ADODB.Parameter
Dim Com As ADODB.Command
Dim strConn As String
' Assign the connection string to a variable
strConn = "PROVIDER=SQLOLEDB;SERVER=(local);User Id=" & _
"sa;Password=;DATABASE=pubs"
' Create the Connection object
Set conn = New ADODB.Connection
'Assign the connection string and provider, then open the
'connection
conn.ConnectionString = strConn
conn.Open strConn
'Create a new command object
Set Com = New ADODB.Command
'Load it with code that will create a new stored
'procedure in PUBS
Com.CommandText = "Create Procedure sp_ReturnsOutput @authorid " _
& "varchar(11),@result Varchar(20) OUTPUT As Select @result " _
& "= (Select au_fname from authors Where Au_id = @authorId)"
'Fire it to create new stored procedure
Com.ActiveConnection = conn
Com.Execute
'Now get ready to create 2 parameters: One for input, one for output.
'Create input Parameter
Set Param = Com.CreateParameter("AuId", adVarChar, adParamInput, 11)
Param.Value = "172-32-1176"
Com.Parameters.Append Param
'Create return parameter
Set Param = Com.CreateParameter("Return", adVarChar, _
adParamReturnValue, 20)
Com.Parameters.Append Param
'NOTE: When creating parameters you MUST include the SIZE of the
'parameter as well as the TYPE of value that you're dealing with.
'Load Command Object a Sql String
Com.CommandText = "sp_ReturnsOutput"
Com.CommandType = adCmdStoredProc
'Fire it.
Com.Execute
'Show Results
MsgBox "The first name of the author with an SS of " & _
Com.Parameters(0).Value & " is " & Com.Parameters(1).Value
' Clean up by deleting stored procedure
Com.CommandText = "Drop Procedure Sp_ReturnsOutput"
Com.CommandType = adCmdText
Com.Execute
conn.Close
End Sub
A ver si te sirve.
Un saludo.