Post reply

MS Access ADE for SQL server

  • July 11, 2005 at 9:43 am

    #68156

    I have built an Access form in an ADE database project linked to SQL server 2000.

    I would like to refresh or requery the underlying data set and return to the selected record on the form.

    Eg.  If i were working on record 23/100 and initiated the refresh command to retrieve the latest changes to I would like to return to the current record 23/100.  Currently the form returns to the first record, which is very annoying.

    I am using the Me.Recalc method (which works perfectly in an Access 2000 mdb, but not when linked to SQL server).

    I believe the bookmark property can provide a workaround but seems a tad confusing.

    Any suggestions/code samples???

    Many thanks in advance to whoever helps.

  • July 11, 2005 at 9:56 am

    #572985

    The bookmark property can work, but if a record is added in font of the selected row and you refresh, then the bookmark won't be pointing back to the correct record. I think that the safest approach is to save the primary key value in a variable, then do a recordset.find value = MyKeyValue.

  • July 11, 2005 at 11:22 am

    #573017

    Remi is right if you are in a multi-user environment. But if you are in a single user environment, the bookmark technique works well:

    1. Before saving the record, save the current value of the <Formname>.Bookmark in a variable

    2. Save the changes to the data

    3. Set the <Formname>.bookmark to the saved "old" bookmark value. This will cause the form to move to the record you were working on.

    Regards,

    Lee H. Fasoli

  • July 11, 2005 at 11:35 am

    #573025

    One other thing... Make sure that the bookmark variable is of variant type. I've had problems when I set it to int sometimes.

  • July 12, 2005 at 2:20 am

    #573259

    thanks guys for the reply. 

    I think Im looking for something more like this.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;97181#toc

    But I need to find it for Visual Basic v6 and above.

    Any thoughts.

  • July 12, 2005 at 2:57 am

    #573275

    Ive fixed it.  Special Thanks to Remi.  I used the Primary Key and the Me.Find method.

    Many thanks.

  • July 12, 2005 at 4:56 am

    #573294

    Ya that link is using DAO... not that much different from ADO but you still got to find the right method name .

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply