Solved

MsAccess calling MySQL Stored procedures.  Most effective way

Posted on 2007-03-29
6
412 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Access VBA - allocate a colour 3 68
access vba 5 55
Why Won't My Excel Workbook Refresh The Data 2 40
VBA Ref Table, use ID#, then ref column 2 3 19
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

739 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