Access 03 & MySQL

[mdlueck]

I was going through the DAO code for copying be table data to a temp fe table.

I was able to do the same by creating two recordsets, pointing to the fe and be tables and do the transfer. Am I missing something?

Meaning you use interpretive VBA code to transfer each field / record from one recordset object to the other? If so, ja that will work, and I knew that. I desire to not have to code any such loop code, so went in the direction of nested DAO.QueryDef objects instead.

If I use CALL procjp3("ALEC"); where firstname = 'ALEC' the correct data is returned.

Right off hand I can not spot the source of trouble. Perhaps test debugging that SP right on the Linux box itself. I have SQL scripts which I use to drive SP's right within the SQL Server tools. Rule out Access / VBA / the ODBC driver / etc... The SQL I put into DAO.QueryDef objects to execute the SP is nearly the script I use to drive the SP from the SQL Server tool suite... define / set the parameters, then EXEC the SP name passing the parameters.

 

[JohnPapa]

The following results in a list of all first names that begin with an "A",

CREATE [EMAIL="DEFINER=%60root%60@%60localhost"]DEFINER=`root`@`localhost[/EMAIL]` PROCEDURE `procJP3`(IN strParam VARCHAR(45))
BEGIN
        SELECT * FROM `actor` WHERE (`actor`.`first_name` LIKE CONCAT(strParam, '%'));
END$$

with a VBA call of

CALL procjp3("A");

 

[mdlueck]

A creative solution, John. Thanks for following up on this thread again.

 

[JohnPapa]

I believe we answered this one before, but just in case,

CALL procjp3("A");

as a pass-through query (qryStoredProc3) works fine and I can even assign in VBA

Me.RecordSource = "qryStoredProc3"

Isn't there a way to pass the parameter from VBA rather than hardcoding it in a query or using a temp file to store the result?

 

[mdlueck]

Isn't there a way to pass the parameter from VBA rather than hardcoding it in a query or using a temp file to store the result?

rrrrr????

With ADO.Command objects you use ADO.Parameters objects, and with DAO.QueryDef objects executing Stored Procedures you DEFINE/SET the Parameters before the EXEC of the SP. Both ways you may pass a variablized value from a VBA variable to the SP.

with a VBA call of

CALL procjp3("A");

So here you could pass a string variable in place of statically defining "A" to be the arg.

 

[JohnPapa]

With the ADO code posted earlier, I would need to use a temp table.

If the content of pass-through query "qrySPjp3" is

CALL procjp3("A");

and I place the folllowing on the on Open event of form

Me.RecordSource = "qrySPjp3"

I do not need to create a temp file.

The question is, can I call a specific SP (with params) from VBA, pass on the fly user-defined params and NOT have to use a temp table to store the results, which will act as RecordSource for the form?

 

[mdlueck]

The question is, can I call a specific SP (with params) from VBA, pass on the fly user-defined params and NOT have to use a temp table to store the results, which will act as RecordSource for the form?

You may use an unbound form as an Edit form for one specific record in combination with an ADO.Command / ADO.Parametters execution of the SP.

If you want a record list (multiple records) then that requires an FE temp table and downloading records with the SP into the FE temp table. (Nested DAO.QueryDef method suggested)

The difference is editing one record verses displaying multiple records.

 

[JohnPapa]

Thanks Michael for reconfirming.

It is difficult to accept that in the case that I assign

Me.RecordSource = qryName

the data appears on the form without the need for a temp table

In essence, I cannot pass a parameter to a pass-through query and see the result (multiple records) on a form without the use of a temp table. This is something that can easily be done with Access/Jet.

 

[mdlueck]

It is difficult to accept that in the case that I assign

Me.RecordSource = qryName

the data appears on the form without the need for a temp table

In essence, I cannot pass a parameter to a pass-through query and see the result (multiple records) on a form without the use of a temp table. This is something that can easily be done with Access/Jet.

I guessssss you could bind the form to the outer DAO.QueryDef directly. Personally I do not like maintaining that constant connection between the Form UI and the SQL BE DB. I prefer to "get in / get out / leave the BE DB alone", thus downloading into a FE temp table is I believe preferable.

 

[JohnPapa]

I have not gone into security issues yet with MySQL, but don't you get any db record conflicts when more than one user accesses the same record?

I guess for people who download to a temp file and go and have a coffee and then change a record, there should be some form timeout features (which I have yet to discover) that prevents them from doing this, right?

Also, I may have read in passing that with MySQL you cannot have record-level locking, is this true?

 

[mdlueck]

but don't you get any db record conflicts when more than one user accesses the same record?

Nay... My UPDATE SP's take two args to select the correct record to be updated... the record's key field(s) and the lastsavetimestamp of the record. If someone else managed to update the same record while the record was in edit mode, then the lastsavetimestamp will no longer match, and the UPDATE will fail. In that case I pop a custom box that states "perhaps someone else edited the record and saved. rollback and try again"

Also, I may have read in passing that with MySQL you cannot have record-level locking, is this true?

I believe that is true, at least with the MyISAM tables. Perhaps InnoDB has that capability.

I do no record locking what so ever, just keep track of the checked out lastsavetimestamp when editing records.

BTW: The SP's all use the SQL "CURRENT_TIMESTAMP" method of having the server insert the current time it thinks it is when record INSERT / UPDATE operations occur. The application does not rely on what time the workstations believe it is.

 

[mdlueck]

And I thought to add...

I believe my design of always relying on SQL Commands and FE temp tables for record list display has contributed GREATLY to the application's VPN readiness. I have had opportunity to work from home several times, and I find the lack of the second screen of greater impact on my work than VPN network speed.