Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    In Parameter Method (Access 97)

    Hi

    On using the microsoft method (ie http://support.microsoft.com/default.aspx?...b;en-us;100131) for a query, which a handy piece of code, I have run into a problem. See attached for the error message. I presume (and seeking confirmation from the forum) that there is a size limitation causing this. The problem is that each record is a 5 or 6 digit id and when trying to update 50 or more records the error message occurs.

    If my assumption is correct, is there a work around. Off the top of my head, I guess I could loop through the parameter in to produce smaller bite sizes, although this is a rather complicated method of doing things.

    Thanks & Regards
    WTH
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: In Parameter Method (Access 97)

    I don't understand what you are trying to do, so you will have to explain more on what you are trying to do for my benefit.
    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: In Parameter Method (Access 97)

    Since I can't edit your post to activate the link without removing the attachments, here's an active link to the referenced article: http://support.microsoft.com/default.aspx?...kb;en-us;100131.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    <P ID="edit" class=small>(Edited by charlotte on 28-Jan-03 19:04. to activate link to prior post)</P>Hi

    No problem.

    In an earlier post (see http://www.wopr.com/cgi-bin/w3t/showthread...p;Number=214013) I attached a sample DB. In the form there is a multiple select list box. On selecting appropriate records in the list box, and hitting the update records button, it runs a query (based on the in parameter method) to update records.

    The error message occurs when running the query where a large number of records have been selected. Hope this helps clarify the problem.

    Regards

    WTH

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: In Parameter Method (Access 97)

    How large a list of parameters are you trying to work with? A query is just a long SQL string and there are limits on how many characters it contains. The Parameter In method isn't intended for dozens or hundreds of values, it's for relatively short lists.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    Hi

    Thanks

    In answer to your question - Lots!

    For example, there may be up to 300 records requiring updating meaning approximately 1800 characters in the in parameter clause part, plus the characters in the rest of the sql statement. This is obviously the problem as alluded to by you and my earlier post. So, I presume an alternative would be to use a loop command, but some pointers on the most efficient way of doing this would be much appreciated.

    Regards

    WTH

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: In Parameter Method (Access 97)

    In theory, you can have around 64000 characters in a SQL statement. In practice, your query becomes unmanageable before you get close to that size. The thing that may be tripping you up is that you are limited to 255 characters in a parameter. You could either write the selections to an array and then use code to loop through the elements of the array and do your update. for each one or you could dispense with the array and just use the items in the ItemsSelected collection of the listbox. Here's how you might populate the array and then loop through it. I assumed for these purposes that the values in the first column of your listbox were what you wanted to use.

    <pre> Dim arrSelected() As String
    Dim varItem As Variant
    Dim intLoop As Integer

    <font color=448800>' Loop through itemsselected and populate an array</font color=448800>
    For Each varItem in List0.ItemsSelected

    Redim Preserve arrSelected(intLoop)
    arrSelected(intLoop) = List0.Column(0, varItem)
    <font color=448800>' increment the array index </font color=448800>
    intLoop = intLoop + 1

    Next varItem

    <font color=448800>' Loop through the array and do something</font color=448800>
    For intLoop = 0 to Ubound(arrSelected)
    ... Code to execute a query or whatever goes here
    Next intLoop </pre>

    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    Hi

    Many thanks for the advice. I'll give it a go tomorrow.

    Regards
    wth <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: In Parameter Method (Access 97)

    What you could do is to populate a temporary table with the selected items in the list box then use that table as a join to your table in the update query, then it would not matter how many you were trying to update.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    Hi Charlotte

    Code worked great, thanks for the advice as usual. As it seems to be a bit slower in running than the other method, I will only use it where the number of records is greater than 25 and use the original method for records less than 25.

    Thanks & Regards
    WTH

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: In Parameter Method (Access 97)

    You can't really tell how the two methods compare on a large number of arguments because you can't use the other method with a large number of arguments. However, I would not be surprised if it were slower that the Parameter In method because you have to build and run a query or code for each argument instead of updating a group all at once. There are probably ways to speed the whole thing up, but that's part of learning to optimize your code.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    Hi

    I did the split between the two methods based on an informal time comparison for a low number of records comparing the two methods, which indicated the in parameter method was the quicker method. Not very scientific.

    But I agree there is always a better way of doing things, I just got to think of it first!!

    Regards
    WTH

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: In Parameter Method (Access 97)

    Well, one way would be to select a number of items in the array and use them to build the IN list. For example, take 25 items at a time from the array and build the IN list for the parameter. Execute that, then take the next 25, etc.
    Charlotte

  14. #14
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: In Parameter Method (Access 97)

    Hi

    Definitely worth a look. Thanks for the suggestion.

    Regards

    WTH

Posting Permissions

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