Code from snippets does not compile 2

[PavelGur]

I took the following code from VBA snippets. I modified the names and tried to debug it. I've got compile error on the connection line: Invalid use of keyword new.
Does it mean that Connection keyword does not exist anymore or I need to modify something for Office XP?
Thanks Pavel.
Here is the code:
Sub Accessconn()
Dim conn As New Connection
Dim rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&
Set ws = ThisWorkbook.Worksheets("Price")
conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
"Data Source=NST;"
'"Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"
sql = "SELECT Stock " & _
"FROM STGEN WHERE Direction = -1 ORDER BY Stock"
rec.Open sql, conn
While Not rec.EOF
i = i + 1
ws.[a1].Cells(i) = rec!Stock
rec.MoveNext
Wend
rec.Close: conn.Close
End Sub

 

[rmikesmith]

Replace

  Dim conn As New Connection
  Dim rec As New Recordset

with

  Dim conn As New ADODB.Connection
  Dim rec As New ADODB.Recordset

You also need a reference to the Microsoft ActiveX Data Objects x.x Library


Regards,
Mike

 

[PHV]

Have you referenced the Microsoft ActiveX Data Object library ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886