Mars Connection Problem

I am trying to make a connection to an SQL database held on myserver I am able to connect through a machine data source using access using the credentials as below the attempt fails at con.open with following error:

System.Runtime.InteropServices.COMException was caught
ErrorCode=-2147467259
Message="Named Pipes Provider: Could not open a connection to SQL Server [53]. "
Source="Microsoft SQL Native Client"
StackTrace:
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at Client_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:\Documents and Settings\Robert\My Documents\Visual Studio 2005\Projects\ Client Access\ Client Access\JobRequest.vb:line 392

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sPassword As String, sUserID As String
sPassword = "abcde"
sUserID = "cClient"
Try
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(myserver);" _
& "Database=transportRecs;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "UID=" & sUserID & ";" _
& "PWD=" & sPassword & ";" _
& "MARS Connection=True;"
Dim mySQL As String

mySQL = "SELECT * FROM dbo_jobitem " '& _
'" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset
With rst
.ActiveConnection = con
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
.MoveLast()
.MoveFirst()
.MoveLast()
.MoveFirst()
Debug.Print(.RecordCount)
End With

Catch ex As Exception
MsgBox(ex.ToString)
Finally
If (con.State = ConnectionState.Open) Then con.Close()
End Try

Can anyone help.

Regards,
Joe

The connection string is pooched. Use a UDL to write the connection string for you.

1. Open notepad

2. Save the blank file as test.udl to your desktop

3. Open the UDL and create and test your connection string.

4. Close and open in notepad

5. Copy the generated connection string

Adamus

|||

Hi Adamus Turner

I have follwed the instructions supplied. The connection is good using the connection string below:

con.ConnectionString = "Provider=MSDASQL.1;Password=abcde;Persist Security Info=True;User ID=cClient;Mode=ReadWrite;Extended Properties= DSN=TransportDB_Comp1;Description= ;UID= cClient;PWD=abcde;APP=Microsoft? Windows? Operating System;WSID=MIDLAPTOP2; transportRecs;Network=DBMSSOCN" & _

"MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT dwjobitemid FROM dbo_jobitem"

'";" '" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset

With rst

.ActiveConnection = con

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenStatic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

.MoveLast()

.MoveFirst()

.MoveLast()

.MoveFirst()

MsgBox(.RecordCount)

End With

When the connection attemps to open a record set (.open(mySQL) an error is called, Invalid object name? I know the table exists?

Regards

Joe

|||

Is the table name dbo_jobitem or dbo.jobitem?

It's a strange convention to use an underscore.

Adamus

|||

Also, do not post logins and passwords to databases.

Moderator, please remove login and password from connection string.

Adamus

|||

Hi Adamus

I have managed to sort out the problem, the issue was not with the connection string after I had used your advice the connection was good. The database tables when viewed on the sql server all had names starting “dbo_” I had attempted to look at the database schema.tables using “select * from information_schema.tables” out of vbnet but could not work out how you returned recordset!table_name in vb.net> I did managed to achieve this using vba. The table names returned were all shown minus the “dbo_” prefix I altered my sql to match and bingo.

Any user names, passwords etc used in my examples are all fictitious. And have been used for illustration only. Many thanks for your assistance with my postings

Regards

Joe