SQL Connection From Excel

[nirs]

hi

can i open connection from excel to a sql server
without using ODBC ?
i mean using IP address

 

[jrbarnett]

Using a DSN less ADO connection its possible to connect to SQL Server from Excel (or other Office applications).
Something like the following will do:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim cmd As New ADODB.Command
 
Dim strConnect As String
 
strConnect = "Provider=sqloledb;Data Source=server name or IP;Network Library=DBMSSOCN;Initial Catalog=databasename;User ID=username;Password=pwd;App=YourAppname"
 
cnn.Open strConnect
 
cmd.CommandText = "select * from mytable"
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdUnknown
Set rst = cmd.Execute
 
Do While Not rst.EOF
  ' or do whatever you want with the data in here
  Debug.Print rst.Fields(0).Value
  rst.MoveNext
Loop
 
rst.Close
cnn.Close

John