Results 1 to 6 of 6
  1. #1
    Saleem13 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    3

    Invalid Procedure Call error - MS Access

    Hi,



    I have been working with two tables, MarimbaHWExtractFile & Subnet-Ranges where Site name is updated from table Subnet-Ranges is updated in table MarimbaHWExtractFile at Location field with comparison between IP_Address from MarimbaHWExtractFile and IP Range from Subnet-Ranges.
    We are using below code but getting Invalid Procedure Call error. Please help!!

    Left([MarimbaHWExtractFile]![IP_Address],(Len([MarimbaHWExtractFile]![IP_Address])-Len(Right([MarimbaHWExtractFile]![IP_Address],Len([MarimbaHWExtractFile]![IP_Address])-InStr(InStr(InStr(1,[MarimbaHWExtractFile]![IP_Address],".")+1,[MarimbaHWExtractFile]![IP_Address],".")+1,[MarimbaHWExtractFile]![IP_Address],"."))))-1)


    SQL View - UPDATE MarimbaHWExtractFile, [Subnet-Ranges] SET MarimbaHWExtractFile.Location = [Subnet-Ranges]![Site Name]
    WHERE (((Left([MarimbaHWExtractFile]![IP_Address],(Len([MarimbaHWExtractFile]![IP_Address])-Len(Right([MarimbaHWExtractFile]![IP_Address],Len([MarimbaHWExtractFile]![IP_Address])-InStr(InStr(InStr(1,[MarimbaHWExtractFile]![IP_Address],".")+1,[MarimbaHWExtractFile]![IP_Address],".")+1,[MarimbaHWExtractFile]![IP_Address],"."))))-1))=[Subnet-Ranges]![IP Range]));

    ____
    Please see the attached sheet. Thanks
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I assume that is IPv4 addressing. That nesting is a little hard to follow but I tested it in VBA immediate window and it returns the first 3 octets. The expression syntax is good so something about the UPDATE syntax or the source data is bombing.

    I always use a JOIN in updates that pull value from another table.

    Try doing a SELECT query that calcs the IP subnet extract and save that query. Then use that query object in the UPDATE and join on the IP subnet fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Saleem13 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    3
    Quote Originally Posted by June7 View Post
    I assume that is IPv4 addressing. That nesting is a little hard to follow but I tested it in VBA immediate window and it returns the first 3 octets. The expression syntax is good so something about the UPDATE syntax or the source data is bombing.

    I always use a JOIN in updates that pull value from another table.

    Try doing a SELECT query that calcs the IP subnet extract and save that query. Then use that query object in the UPDATE and join on the IP subnet fields.
    _____

    Hi,

    Actually we used to receive CSV files (old version source file) and it was working fine. However now we received same file with same data but something with new version hence may be it not working now. However both are CSV files and both have same data except new file is 3 extra columns. please suggest

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That certainly changes the picture. No idea why the new CSV is an issue. How is the CSV used in the UPDATE action? Not seeing an object in that query matching the CSV description. Do you link to the CSV or import?

    If you want to provide sample file for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Saleem13 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    3
    Quote Originally Posted by June7 View Post
    That certainly changes the picture. No idea why the new CSV is an issue. How is the CSV used in the UPDATE action? Not seeing an object in that query matching the CSV description. Do you link to the CSV or import?

    If you want to provide sample file for analysis, follow instructions at bottom of my post.
    -------------------
    We import the CSV file into MarimbaHWExtractFile. If i import old CSV file no issues. But if I import new CSV file it gives such an error at the time of running query which update location via corresponding IP Address in both Subnet and Marimba table. Please suggest. Thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have no idea why the newer CSV file is an issue. Would have to examine files. I don't understand "... via corresponding IP Address in both Subnet and Marimba table".

    What is the error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 12-08-2012, 04:56 PM
  2. Replies: 9
    Last Post: 08-10-2012, 03:10 AM
  3. Invalid Procedure Call
    By neo651 in forum Queries
    Replies: 21
    Last Post: 09-11-2011, 12:58 PM
  4. Access 2007 Append Query Invalid Procedure Call
    By forrestapi in forum Queries
    Replies: 1
    Last Post: 05-13-2011, 07:53 AM
  5. invalid procedure call
    By johnmerlino in forum Access
    Replies: 5
    Last Post: 12-13-2010, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums