Getting to SQL Server without ODBC from VBA

[Goofy]

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

 

[Dave Patrick]

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

:
| 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
|
|

 

[Goofy]

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]

That doesn't help much. Can you post your code, exact text of error, and on
which line?

When you;
Tools|References was this available? 'Microsoft ActiveX Data Objects 2.x
Library'
Can you see them in Control Panel|Admin Tools|Data Sources ?

--

Regards,

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

:
| 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.

 

[Goofy]

Actually Dave, it did work, it was me that was in error. However, it does
not really seem to be much faster, but then we are talking vba here so that
is probably the bottleneck.

I know that on ODBC, you cant use named parameters, but are you able to do
this using OLEDB ?

Cheers for your help.

 

[Dave Patrick]

What is slow? It may be the way you're coding. Did you mean to pass a
variable to your SQL? Absolutely.

--

Regards,

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

:
| Actually Dave, it did work, it was me that was in error. However, it does
| not really seem to be much faster, but then we are talking vba here so
that
| is probably the bottleneck.
|
| I know that on ODBC, you cant use named parameters, but are you able to do
| this using OLEDB ?
|
| Cheers for your help.

 

[Goofy]

OK, well to put this in context, I am generating about 7,000 records in one
hit. This takes approximately 90 seconds.

 

[Dave Patrick]

Still doesn't help. Can you post the code?

--

Regards,

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

:
| OK, well to put this in context, I am generating about 7,000 records in
one
| hit. This takes approximately 90 seconds.

 

[Goofy]

Ive made improvement now, thanks for your reply.