Connect to SQL server?

[simran1]

Hi,
How do i connect to SQL Server from VB application?
I want to open table in SQL Server.

 

[Foada]

I don't know how you are trying to connect, but here is an example using ADO.


Option Explicit

Private hDB As New ADODB.Connection
Private rsData As ADODB.Recordset

Private Const sDBConnection As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=testDB;Data Source=CT4"

Private Sub Command1_Click()
ConnectDB
End Sub

Private Function ConnectDB() As Boolean
Dim sSQL As String

On Error GoTo ErrHnd

sSQL = "Select * From Employees Where DeptID = 1 Order By LastName Asc"

hDB.ConnectionString = sDBConnection 'Open The Connection
hDB.Mode = adModeRead
hDB.Open

Set rsData = New ADODB.Recordset
rsData.CursorLocation = adUseClient
rsData.Open sSQL, hDB, adOpenStatic, adLockBatchOptimistic
Set rsData.ActiveConnection = Nothing
hDB.Close

Do While rsData.EOF <> True
List1.AddItem RTrim$(rsData(&quot;FirstName&quot) & &quot; &quot; & RTrim$(rsData(&quot;LastName&quot)
rsData.MoveNext
Loop

rsData.Close
Set rsData = Nothing
ConnectDB = True

Exit Function

ErrHnd:
ConnectDB = False
End Function

Hope this helps. If you choose to battle wits with the witless be prepared to lose.

 

[simran1]

Hi,

I was trying to get &quot;DSN-Less&quot; ODBC Connections in my VB application.

 

[russellbcopeland]

Thats basically it though I prefer to not use a hard coded connection. You can set tour connection

sDBConnection = &quot;File Name=\\pathtofile\somefile.udl;&quot;

I usually keep the udl in the same place as the app.. that way you can change your data source without changing code.. assuming your new data source is still compatable with your code etc.. makes it easy to point to test data or cwitch to an alternate server after a server crash etc...all with no code changes. If you pull this file from the same location for all users then you just change it once and redistribute nothing across your network.

To make the .udl file just create a blank text file and name it with a .udl extension.. from there dbl clicking on that file should open up some properties that you can set.. much like setting up a DSN.