Solved

MsAccess calling MySQL Stored procedures.  Most effective way

Posted on 2007-03-29
6
414 Views
Last Modified: 2012-06-27
I need some advice from Access and MySQL experts :>

I am slowly migrating an access application over to MySQL V5 so I can use Views, Triggers, Stored Procedures, and other enhancements to increase the speed of the operations.

With the latest MySQL (5.0.37) and the MyODBC (3.51.14) this fixes the views issue. However I am finding the runing stored procedures are taking a lot longer than I would expect.

Below is some Access code and also the Stored Proc that it calls.
Basically it is used to maintain a set of counters (id'ed by iKeyType) so that a multi user system can get the next num without duplication.

If there a better way/connection to call this procedure.
Should I use ADO instead of DAO.
Should I make a global connection so that it does not need to be created/opened every time.

I know that Stored Procs for certain tasks are the way to go.. But if they take a long time for even a simple task like incrementing a number in a record then I will have to look otherwise.

Many thanks for any help/inspiration you can provide

Dave



Access Code
----------------------------


Function GetNextNum(iKeyType As Long)
On Error GoTo GetNextNumError

    Dim MyWorkspace As DAO.Workspace
    Dim MyConnection As DAO.Connection
    Dim MyRecordset As DAO.Recordset
    Dim MySQLString As String
    Dim MyODBCConnectString As String
   
   
    Set MyWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    If Len(gDBConnectSAP) = 0 Then
        gDBConnectSAP = DLookup("connectstring", "tblODBCConn", "linkpoint =1")
        gDBConnectINNOV = DLookup("connectstring", "tblODBCConn", "linkpoint =3")
    End If
    MyODBCConnectString = "ODBC;DSN=" & gDBConnectSAP & ";"
    Set MyConnection = MyWorkspace.OpenConnection("Connection1", dbDriverNoPrompt, , MyODBCConnectString)
    MySQLString = "call next_rec('" & iKeyType & "');"
    Set MyRecordset = MyConnection.OpenRecordset(MySQLString, dbOpenDynamic)
   
    With MyRecordset
           GetNextNum = MyRecordset(0)
    End With
   
    MyRecordset.Close
    Set MyRecordset = Nothing
   
    MyConnection.Close
    Set MyConnection = Nothing
   
    MyWorkspace.Close
    Set MyWorkspace = Nothing

GetNextNumExit:
    Exit Function

GetNextNumError:
    MsgBox "Error in query_run."
    Resume GetNextNumExit
End Function





Stored Proc
------------------------

DELIMITER $$

DROP PROCEDURE IF EXISTS `sap2k_dat`.`next_rec`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `next_rec`(in _param1 INT)
BEGIN
  DECLARE nextnum INT;
start transaction;
  if (select count(*) from next_id where keytype = _param1) =0 then
      insert into next_id values (1,_param1);
  else
      update next_id set id1 = id1 + 1 where keytype = _param1;
  end if;
  select id1 into nextnum from next_id where keytype = _param1;
  select nextnum;
commit;
END$$

DELIMITER ;
----------------------------------------------------------------
0
Comment
Question by:CVSmarc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18820772
Hi,

I do not think that you are doing this the best way - if you use an auto number field and then just run

select last_insert_id();

after you have inserted a record - you will not get duplicates, then you can use the value returned everywhere else, this also runs very quickly - and is completely dependant on the session with the database, so other users will not impact on it.
0
 
LVL 4

Author Comment

by:CVSmarc
ID: 18820820
Ok - this is a specific example.. But I am looking at stored procedures in general.

Also - in this application there are several counters required such as Invoice Numbers, Order Number which are distinct from the record ID so counters are equired.

Also - if you are designing a database that will be used by a lot of people AND it has the possbility to be moved to a new location/server/platform then using your own auto key generation is perfectly acceptable. If this key is also used to link records together then it means that you are not reliant on the database to provide the key.. or if when rebuilding/restoring the DB thet system will not reset the autonumber back to 0 or 1 on a table.

Still looking for the best way to call a stored proc thou.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 18820923
Hi,

with your autonumber then it does not revert when rebuilding / restoring as you would have the previous details there - it then defaults to the next available number,
if you pull the most recent value then there is not a problem of tying values together.

with respect to the stored procedure I have not been able to get them to return results through odbc - you can however use a function to perform the purpose you have outlined here - which you can then return by doing select function_name() which will pull the result returned, provided it is a single column, otherwise if you get your stored proc to write to a temp table you can then return those results.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Author Comment

by:CVSmarc
ID: 18820952
Hi Raynard1

Ok - I understand this about stored procedures as I have used them before in a DB environment.

My question here is calling them from MSAccess efficiently and quickly re DAO or ADO.

The example about can talke about a second to run which is not very time efficient so I am wondering how I can improve this.

hence my questions

1.  If there a better way/connection to call this procedure.
2.  Should I use ADO instead of DAO.
3.  Should I make a global connection so that it does not need to be created/opened every time.

0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 18820991
how long are normal queries taking? if you can disable resolving names of servers when connecting on mysql then it should not take nearly as long. ADO is generally faster than DAO - however global connections have their own problems, there should not be problems in opening and closing connections throughout your session. also is next_id indexed for keytype?
0
 
LVL 4

Author Comment

by:CVSmarc
ID: 18833794
Hi Raynard1
Ok - I have done some experiments and found that it was the issue of of the connection creation.
I have reworked a few things and the SP seems to be running much bettter.

If you do know about SP and OUT parameters I am at a loss to get VB to return back one of these.

Many thanks and I'll give you the points for helping
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question