Solved

MsAccess calling MySQL Stored procedures.  Most effective way

Posted on 2007-03-29
6
397 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
  • 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now