Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    ADO - Add/Update (VBA 6)

    I want to update a record, if that record exists, or add a record if it does not exists. What is the most efficient method to determine whether a record exists?

    I select the record with the following:

    strSQL = "Select * from tblQuick where Name = '" & sSelectedName & "' "

    If a record where field Name = sSelectedName is found, rs.RecordCount returns -1.

    Thanks.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO - Add/Update (VBA 6)

    Check rs.EOF -- true means the recordset is empty.

    Also, database-related coding questions are better posed in the Access forum. (Well, if you want a quicker response. <img src=/S/wink.gif border=0 alt=wink width=15 height=15> )

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: ADO - Add/Update (VBA 6)

    Does it return -1 or 1? I seem to recall that in my experience recordcount is not accurate until you move to EOF

    You have a choice of running a single query to build a recordset and then do a count and if 0 then add, populate and save OR do a DCount and then an append query. Either way works but it might be easier with the second method since you don't need lots of recordset lines

    If DCount( "Name", "tblQuick", "Name = '" & sSelectedName & "'") = 0 then
    'run SQL Append query here
    End if
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO - Add/Update (VBA 6)

    Thanks. The EOF test works well.

    I'll follow your advice about posting in the Access forum. When I posted my first question here (VB/VBA), I had a reply in about 15 seconds. I realize that was not typical!

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO - Add/Update (VBA 6)

    The return is always -1, regardless of how many records. I tried rs.MoveLast, but that didn't help. It raised an error, "Either BOF or EOF is True." Checking the value of EOF seems to be the most efficient test.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: ADO - Add/Update (VBA 6)

    I see that I the return -1 for rs.Count is because of the cursor type. I added adOpenKeyset, and that resolved the problem. I just re-read the article which you recommended several days ago. At the time, it didn't mean much to me. Now I'm starting to understand things.

    I read yesterday that the Jet Engine is slated to be discontinued. That's my provider for the on-the-fly database which I create and then work with. Where will this leave me? Or is that a question that can't be answered until Microsoft lets us know?

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO - Add/Update (VBA 6)

    I think Microsoft stopped distributing the Jet provider as part of the MDAC package a while ago. However, as long as Jet is used in MS Office, it's difficult to picture any sudden end to support. By the time you have to change over, I think you will have found something even better. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •