Connection String Problem 1

[Pizarro]

Thank you for looking into my question/problem!
Here is my senario:
I have the full SQL 200 on my desktop. Server name is BLK00FINA00129.
From my ADP named Cumes.adp I am connected to SQL database named Cume2SQL.
the following is what I am trying to do which is run a stored procedure from a form by pressing a button for example: The connection string is what I am missing. If you can help I would be in your debt, I have been left to my own devise to figure all this out. But with your help I can succeed. Thanks Regards

Private Sub DoSomething
Dim oConn as ADODB.Connection
Dim oComm as ADODB.Command


Set oConn = New ADODB.Connection
Set oComm = New ADODB.Command
oConn.ConnectionString = "Put connection string here"
oConn.Open
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdStoredProc
oComm.CommandText = "Append Tables"
oComm.Execute

 

[MarkWright]

Hello Pizarro,
I have access 2000 with SQL server 2000 and I use:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim tbl As New ADODB.Recordset
Dim dbcmd As ADODB.Command

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =

Set rst = cnn.Execute("Select * From")
Set dbcmd = New ADODB.Command
Set dbcmd.ActiveConnection = CurrentProject.Connection

Hope this helps you out.

Mark

 

[cmmrfrds]

Here is an example of setting up an SQL Server OLEDB connection to the pubs database on the server bigtuna with login of sa and no password.

connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"

Notice the provider is SQLOLEDB.1, which is the native sql server provider. There are others you can use for sql server, but this is probably the best option.

 

[Pizarro]

Thanks for the reply. I have gotten so far with what I have
down below.
1.) I am getting a time out erro message and 2.) Stored Procedure2 is not executing.

If you have any ideas they would very welcomed!

Thanks again and Regards

Private Sub Command0_Click()

Dim oConn As ADODB.Connection
Dim oComm As ADODB.Command
Set oConn = New ADODB.Connection
Set oComm = New ADODB.Command
oConn.Open _
"Provider=Microsoft.Access.OLEDB.10.0;Data Source=BLK00FINA00129;Integrated Security=SSPI;" & _
"Initial Catalog=Cume2SQL;Data Provider=SQLOLEDB.1"
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdStoredProc
oComm.CommandText = "StoredProcedure2"
oComm.Execute "StoredProcedure2"

End Sub

 

[cmmrfrds]

ADO has an error collection and there may be multiple errors in the collection, it is best to loop through to make sure there is not an underlying error that is not displayed.

Private Sub Command0_Click()
On Error GoTo ErrHandler



at the end of your sub put an error handler

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
End Sub

I have some suggestion, but need to be at home to look at some material. If I get to it, I will check on your thread tonight. Please run the error routine and paste in all the error messages.

 

[Pizarro]

Thanks for your reply and yes I have added your code and I did not get anything - I'll check back later - Thanks again for all your help!

 

[cmmrfrds]

Did you do a "Control G" and look in the immediate window to see if the bebug messages printed?

 

[cmmrfrds]

I recommend checking your connection by setting it up with the udl wizard. Test the connection from the wizard.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft sql server provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[Pizarro]

Here what finally worked:
Many thanks to all and a specials thanks to cmmfrds!


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=BLK00FINA00129;Persist Security Info=True;" _
& "Initial Catalog=Cume2SQL;Trusted_Connection=Yes;Data Provider=SQLOLEDB.1"
oConn.Open
Set oComm.ActiveConnection = oConn
oComm.CommandType = adCmdStoredProc
oComm.CommandText = "StoredProcedure1"
oComm.Execute
oComm.CommandText = "StoredProcedure2"
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