Executing a parameterized stored query in access from vb


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Executing a parameterized stored query in access from vb

  1. #1
    Fred Guest

    Executing a parameterized stored query in access from vb


    'get last run date first
    dteLastRunDate = GetLastRunDate

    'store query in access
    Set qdfHealthEd = dbsHealthEd.QueryDefs("qryExportToAmisys")
    Set prmHealthEd = qdfHealthEd.Parameters!LastRunDate
    prmHealthEd = dteLastRunDate
    Set rstHealthEd = qdfHealthEd.OpenRecordset(dbOpenDynaset)

    We've migrated to Access 2000 and I'm trying to convert the above DAO query
    to ADO standards but I can't seem to figure it out. The Microsoft site has
    some examples but they don't seem to apply...any suggestions???

  2. #2
    Paul Little Guest

    Re: Executing a parameterized stored query in access from vb

    Look at ADO's Command object. You'll have to append a parameter to the
    Parameters collection before calling the Execute method.
    --
    Paul Little, Senior Programmer/Analyst
    SVi Retail Systems
    San Diego, CA

    "Fred" <fnegrete@txbirchdavis.com> wrote in message
    news:391b21b7$1@news.devx.com...
    >
    > 'get last run date first
    > dteLastRunDate = GetLastRunDate
    >
    > 'store query in access
    > Set qdfHealthEd = dbsHealthEd.QueryDefs("qryExportToAmisys")
    > Set prmHealthEd = qdfHealthEd.Parameters!LastRunDate
    > prmHealthEd = dteLastRunDate
    > Set rstHealthEd = qdfHealthEd.OpenRecordset(dbOpenDynaset)
    >
    > We've migrated to Access 2000 and I'm trying to convert the above DAO

    query
    > to ADO standards but I can't seem to figure it out. The Microsoft site

    has
    > some examples but they don't seem to apply...any suggestions???




  3. #3
    Shelly Rosenfeld Guest

    Re: Executing a parameterized stored query in access from vb


    I hate this migration to ADO too, but you do deserve a clearer
    response.

    You don't HAVE to append a parameter, it's just wiser (like, why query the
    database to get the parameter, type, direction etc, and also increase
    network
    traffic with this polling etc.).
    Upon running the "Execute" you still must create a recordset by opening
    the command object just executed.

    HTH,
    Sheldon

    Paul Little <PLittle@SViRetail.com> wrote in message
    news:391b45dc$1@news.devx.com...
    > Look at ADO's Command object. You'll have to append a parameter to the
    > Parameters collection before calling the Execute method.
    > --
    > Paul Little, Senior Programmer/Analyst
    > SVi Retail Systems
    > San Diego, CA
    >
    > "Fred" <fnegrete@txbirchdavis.com> wrote in message
    > news:391b21b7$1@news.devx.com...
    > >
    > > 'get last run date first
    > > dteLastRunDate = GetLastRunDate
    > >
    > > 'store query in access
    > > Set qdfHealthEd = dbsHealthEd.QueryDefs("qryExportToAmisys")
    > > Set prmHealthEd = qdfHealthEd.Parameters!LastRunDate
    > > prmHealthEd = dteLastRunDate
    > > Set rstHealthEd = qdfHealthEd.OpenRecordset(dbOpenDynaset)
    > >
    > > We've migrated to Access 2000 and I'm trying to convert the above DAO

    > query
    > > to ADO standards but I can't seem to figure it out. The Microsoft site

    > has
    > > some examples but they don't seem to apply...any suggestions???

    >
    >




  4. #4
    Tim Guest

    Re: Executing a parameterized stored query in access from vb


    Take a look at the CreateParameter method off of the ADO Command object.
    That should get you started. I am using it with Access 2000 also and it
    works fine...........

    "Fred" <fnegrete@txbirchdavis.com> wrote:
    >
    >'get last run date first
    >dteLastRunDate = GetLastRunDate
    >
    >'store query in access
    >Set qdfHealthEd = dbsHealthEd.QueryDefs("qryExportToAmisys")
    >Set prmHealthEd = qdfHealthEd.Parameters!LastRunDate
    >prmHealthEd = dteLastRunDate
    >Set rstHealthEd = qdfHealthEd.OpenRecordset(dbOpenDynaset)
    >
    >We've migrated to Access 2000 and I'm trying to convert the above DAO query
    >to ADO standards but I can't seem to figure it out. The Microsoft site

    has
    >some examples but they don't seem to apply...any suggestions???



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center