Results 1 to 5 of 5
  1. #1
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659

    Call MySQL Stored Procudure

    I saw a previous entry from Minty about how to do this. but so far haven't been about to get this to work. The plan was to use the dynamic stuff after this worked.

    I have Store Procedure in MySQL like this

    Code:
    CREATE DEFINER=`root`@`%` PROCEDURE `ReNumberInventoryReceiptLi`(
     IN documentID varchar(25))
    BEGIN
    set @x = 0;
    set @t1 = concat("update tblInventoryReceiptLi set li = @x:=@x+1 where ID = '",DocumentID,"' order by li");
    Prepare stmt from @t1;
    execute stmt;
    deallocate prepare stmt;
    END
    I am able to call it in MySQL Workbench with
    Code:
    call cyclodata.ReNumberInventoryReceiptLi("TEST");
    but in access pass through
    Code:
    EXEC cyclodata.ReNumberInventoryReceiptLi("TEST");
    gives me an error. telling me to check my MySQL manual.



    did I miss something?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I found some links using Google with
    example ms access calling mysql stored procedure
    but unsure if any are applicable to you (I don't use MySql).

  3. #3
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This link describes some techniques to use with MySql - like Orange I don't use it so can't really assist.
    https://stackoverflow.com/questions/...ontend-via-vba
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried the same syntax in Access? (using Call instead of Exec)?

  5. #5
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    @Minty/Orange, Googled lots but haven't been able to get anything functional.
    @Gicu, yes. I didnt get anywhere with that either

    Thanks for the thoughts. I will back burner this one. I have vba that works to do this, I just wanted to move it to the database.

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

Similar Threads

  1. how to call stored Query
    By mks123 in forum Programming
    Replies: 6
    Last Post: 05-10-2015, 10:25 AM
  2. MS SQL or MySQL?
    By Historypaul in forum SQL Server
    Replies: 6
    Last Post: 12-14-2014, 01:00 PM
  3. Execute MySQL Stored Procedure with Access 2010 VBA?
    By DanielHofer in forum Programming
    Replies: 5
    Last Post: 01-23-2012, 01:08 PM
  4. Access to SQL or MySql
    By Randy in forum Access
    Replies: 6
    Last Post: 08-05-2011, 08:38 AM
  5. Use VBA to call stored SQL from table
    By hcruz in forum Programming
    Replies: 2
    Last Post: 05-02-2011, 08:26 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