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.COMExcept Dim con As New ADODB.Connection mySQL = "SELECT * FROM dbo_jobitem " '& _ con.Open() rst = New ADODB.Recordset Catch ex As Exception Can anyone help. Regards, 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
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(Ob
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
'" WHERE [Custid] ='" & strTag & "'"
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
MsgBox(ex.ToString)
Finally
If (con.State = ConnectionState.Open) Then con.Close()
End Try
Joe
