Getting to SQL Server without ODBC from VBA
 

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

Getting to SQL Server without ODBC from VBA
 

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

Getting to SQL Server without ODBC from VBA
 

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

Getting to SQL Server without ODBC from VBA
 

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

"Goofy" wrote:
| 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.

Getting to SQL Server without ODBC from VBA
 

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" wrote in message
...
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

"Goofy" wrote:
| 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.

Getting to SQL Server without ODBC from VBA
 

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

"Goofy" wrote:
| 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.

Getting to SQL Server without ODBC from VBA
 

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



"Dave Patrick" wrote in message
...
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

"Goofy" wrote:
| 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.

Getting to SQL Server without ODBC from VBA
 

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

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

Getting to SQL Server without ODBC from VBA
 

Ive made improvement now, thanks for your reply.



"Dave Patrick" wrote in message
...
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

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