Thanks for your reply Dave, I did try the "provider=SQLOLEDB before and it
still came up with an ODBC message telling me the driver was not found.

"Dave Patrick" wrote in message
...
See if this helps. Don't forget to; Tools|References and check the box for
'Microsoft ActiveX Data Objects 2.x Library'

Use the highest version that will still support your clients.

Public Sub testread()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1

'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!LastName
Sheets("Sheet1").Range("B" & i) = rs1!FirstName
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub

Public Sub testwrite()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
i = 1

'Use for Access (jet)
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Program Files\Microsoft Office\" _
& "OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False"

'Use for SQL server (OLEDB)
strConn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=Northwind;" _
& "Data Source=PE1600"


strSQL1 = "SELECT LastName, FirstName " _
& "FROM Employees; "
Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenDynamic, adLockOptimistic
For i = 1 To 3
rs1.AddNew
rs1!LastName = Sheets("Sheet1").Range("A" & i)
rs1!FirstName = Sheets("Sheet1").Range("B" & i)
i = i + 1
rs1.Update
Next
rs1.Close
cnn.Close
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Goofy" wrote:
| Excel 2003 VBA
| Microsoft SQL Server
|
| I want to know if I can connect directly to the SQL Server without
having
to
| go through the ODBC driver which slows things down quite alot.
|
| Cheers
|
|