Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MsAccess calling MySQL Stored procedures.  Most effective way

Posted on 2007-03-29
6
Medium Priority
?
424 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

636 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