MySQL Function - Return Value via ODBC

Question

In an MS Access 2007 application, I need to generate a document name of [Date].[SequenceNumber], eg 2010-12-07.013, where each day the SequenceNumber resets to 1.

To maintain data integrity, I'd like to create a function in MySQL that returns the sequence number. I set up a 'sequence' table:

CREATE TABLE `doc_sequence_number` (
  `SequenceDate` date NOT NULL,
  `SequenceNumber` int(11) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`SequenceDate`,`SequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The MySQL function should create a new record, using the server's Date, and the Sequence Number incremented by 1, and return the SequenceDate and SequenceNumber fields.

Can anyone suggest how such a Function should be written (this is my first MySQL function).
How can I call and get the return value(s) via ODBC of such a function, using VBA?

As this is new to me, I'd appreciate any suggestions on how to accomplish this. MTIA.

And you are looking to use vba to take a record from ms sql and enter a new record into mysql? — Matt Phillips
– Matt Phillips, Commented Dec 7, 2010 at 2:18
I'm uploading docs to a server for storage. They are related to a record in a separate docs table. As I upload the doc (in pdf format), I have to rename them. So I thought the MySQL Function could provide a new filename, being Date.SequenceNumber — maxhugen
– maxhugen, Commented Dec 7, 2010 at 2:49

Kevin Ross · Accepted Answer · 2010-12-07 08:16:34Z

1

I don’t have much experience with MySql but if I was doing it in MSSQL I would look to have a table with a regular auto incrementing ID. This table would then have its records cleared and the sequence reset to 1 each day. I would then setup a stored procedure to insert a record into this temp table and read back the ID. Append that onto the servers date and that is your filename.

As for returning values in VBA, have a look at this link about MSSQL, they should be fairly similar

http://bytes.com/topic/access/answers/205120-get-stored-procedure-output-value-back-vba

answered Dec 7, 2010 at 8:16

Kevin Ross

7,2253 gold badges24 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

maxhugen Over a year ago

Thanks Kevin, the auto increment idea is good, and that link looks very useful! Cheers.

Collectives™ on Stack Overflow

MySQL Function - Return Value via ODBC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related