Accessing SQL Server 2000 remote data using ADODB objects in VB.NET RRS feed

  • Question


    For reasons too convoluted to get into, I need to pull data from a SQL Server 2000 database that is in a remote location into an ADODB Recordset within a VB.NET (2003) application.  Here is my code so far:



    Dim myRecordset As ADODB.Recordset

    Dim myConnectionString As String = "Provider=SQLOLEDB;" & _

    "Data Source=myRemoteServer;" & _

    "Initial Catalog=myDatabase;" & _


    Dim myADOSQLConnection As New ADODB.Connection

    Dim mySQLQuery As String = "SELECT * FROM myTable"


    myRecordset.Open(mySQLQuery, myADOSQLConnection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)



    What am I doing wrong here?


    BTW...  I know many of you will suggest I update to 2.0, or use datasets, or use a SqlConnection object... thanks, I thought of all of that, I can't do it now.  That's the next step, but I have to get this application to IJW status.


    Friday, September 28, 2007 4:24 PM

All replies

  • Never mind... I figured out that I was missing the keyword "New" when I declared the ADODB.Recordset.


    Should have said this:


    Dim myRecordset As New ADODB.Recordset


    Oh well.


    UPDATED:  This wound up only allowing me to create the connection... I still didn't retrieve any records.


    So, with this correction, why am I still not able to create my recordset?

    Friday, September 28, 2007 5:00 PM