Will Access database engine handle this?

[EagleKeeper]

1) Record Locking.

Multiple external applications will be using the ODBC connection to get to the database file.

When Access is being used, the documention indicates that the concern over record locking can be handled. However, it also implies that when using ODBC, the NoRecordLock is used.

I can develop code to go around these limitations, however, prefer not to.

2) Generate stored procedures that I can trigger via an ODBC connection. When certain datafields are changed, logging information needs to be captured.


Or can MySQL or another SQL style free database easily impliment the record locking and triggers.

 

[Drakkon]

mysql handles locking and triggers...not sure if its to the extent u will want it...but if u look at their tutorials should get a decent idea of if it might work for you
im sure it would be a much better solution than access

 

[EagleKeeper]

Thanks for the feedback

 

[Nothinman]

I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

 

[nsafreak]

I would recommend MySQL. We use a similar setup with Access where I work for tracking some information, we are migrating away from this though THANK GOD, and it loves to crash.

 

[KLin]

Originally posted by: Nothinman
I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

Access uses record level locking.

 

[Zugzwang152]

Originally posted by: nsafreak
I would recommend MySQL. We use a similar setup with Access where I work for tracking some information, we are migrating away from this though THANK GOD, and it loves to crash.

i'm inclined to agree, although i'm somewhat hesitant to even mention "mysql" what with all the inhumane bashing its been getting on this forum lately.

 

[kamper]

There's nothing wrong with mysql, it's just that there's something that people like better But that other something's windows port is still very young so I'd probably take mysql here too.

 

[EagleKeeper]

Originally posted by: KLin

Originally posted by: Nothinman
I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

Access uses record level locking.

I know that the Access application itself will perform record locking and handle stored procedures when running VBA applications.

Remember I am only intending on using Access as the back-end database. MDB file format.

 

[MrChad]

Originally posted by: EagleKeeper

Originally posted by: KLin

Originally posted by: Nothinman
I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

Access uses record level locking.

I know that the Access application itself will perform record locking and handle stored procedures when running VBA applications.

Remember I am only intending on using Access as the back-end database. MDB file format.

How many concurrent users will your application need to scale to? How much data do you need to handle?

 

[EagleKeeper]

Originally posted by: MrChad

Originally posted by: EagleKeeper

Originally posted by: KLin

Originally posted by: Nothinman
I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

Access uses record level locking.

I know that the Access application itself will perform record locking and handle stored procedures when running VBA applications.

Remember I am only intending on using Access as the back-end database. MDB file format.

How many concurrent users will your application need to scale to? How much data do you need to handle?

Anticipate 3-4 concurrent users at any one time. Very light database I/O load.
Database sizing has not been done at this point. Detailed confinguration information from the field equipment (new product) will not be available until 6 months from now.

Expect 100-200 data parameters in the base configuration mulitplied by anywhere from 50 to 3000 field units. Each parameter wil have 5-6 fields associated with it.

One of the reasons that I am planning on using ODBC is to allow scaling up of the database(s) as needed if Access can not handle the load.

 

[Nothinman]

There's nothing wrong with mysql

Sure there is, it's not ACID compliant, no stored procedures, no views and probably more that I don't know about since I hate databases.

 

[kamper]

Originally posted by: Nothinman

There's nothing wrong with mysql

Sure there is, it's not ACID compliant, no stored procedures, no views and probably more that I don't know about since I hate databases.

Then keep your damn opinion to yourself. It get's really tiring sometimes reading as you mindlessly bash products that don't fit your exlusive model of perfect software. Do you suffer because you have to program with mysql and are worse off for lack of these features?

 

[Nothinman]

Then keep your damn opinion to yourself. It get's really tiring sometimes reading as you mindlessly bash products that don't fit your exlusive model of perfect software. Do you suffer because you have to program with mysql and are worse off for lack of these features?

Those 'features' are basic requirements for a real RDBMS. Sort of like how division and subtraction are basic features of a calculator. If you had one that could only do addition and multiplication sure you could still call it a calculator, but it's not a very good one.

 

[kamper]

So you're on a campaign to tell the world that mysql doesn't live up to the title of RDBMS? I'm sure the people who use it are well aware of the features and will choose an alternative if mysql isn't good enough. I don't see how any of this should bother you.

I wouldn't equate acid and stored procs to multiplication and division. Maybe more like exponents and logs. There are lots of calculators out there that only do the four basic operations and they are useful, in their limited scope.

 

[Nothinman]

So you're on a campaign to tell the world that mysql doesn't live up to the title of RDBMS?

Considering that it's attempting to be one, yes people should be aware of it's limitations. Hell it doesn't even do foreign key checks unless you use InnoDB tables and the last time I looked InnoDB tables weren't even the default.

I'm sure the people who use it are well aware of the features and will choose an alternative if mysql isn't good enough.

Actually I would guess that most people who use it are using it simply because it's what their hosting provider gave them.

I don't see how any of this should bother you.

Proliferation of bad software should bother everyone.

 

[statik213]

Originally posted by: kamper
So you're on a campaign to tell the world that mysql doesn't live up to the title of RDBMS? I'm sure the people who use it are well aware of the features and will choose an alternative if mysql isn't good enough. I don't see how any of this should bother you.

I wouldn't equate acid and stored procs to multiplication and division. Maybe more like exponents and logs. There are lots of calculators out there that only do the four basic operations and they are useful, in their limited scope.

agreed.... mysql is good at what it does... I use it plenty and haven't had the need for all the funky things that SQL server, Oracle or postrgresql offer.... but then again I make sites that only attract a few hundred visitors a day...

 

[Descartes]

Originally posted by: Nothinman

There's nothing wrong with mysql

Sure there is, it's not ACID compliant, no stored procedures, no views and probably more that I don't know about since I hate databases.

ACID transactions have been in MySQL for many years. MySQL 5.0 has stored procedures and views.

 

[Descartes]

Rather than flame MySQL I'll try to answer your questions:

Originally posted by: EagleKeeper
1) Record Locking. 1) Record Locking.

Multiple external applications will be using the ODBC connection to get to the database file.

When Access is being used, the documention indicates that the concern over record locking can be handled. However, it also implies that when using ODBC, the NoRecordLock is used.

I can develop code to go around these limitations, however, prefer not to.

What will you be using to access the ODBC driver? VB, C++, .NET? How you implement solutions to concurrency problems is vastly different between platforms, but Access has suitable facilities for this, and ODBC has the ability to employ them.

2) Generate stored procedures that I can trigger via an ODBC connection. When certain datafields are changed, logging information needs to be captured.

Access doesn't have stored procedures or triggers; however, they do have queries (yeah, almost functionally analogous to a sproc, but there are differences) and the ability to write VBA. The unfortunate problem with using VBA is that you'd need to install the Access Runtime on the end-user's machine in order to make use of them, and that's a real pain in the arse. You could of course simulate triggers at the application level by implementing it yourself.

You might also look at MSDE since you will have all the facilities of SQL Server with a simplified deployment model.

 

[Nothinman]

ACID transactions have been in MySQL for many years. MySQL 5.0 has stored procedures and views.

Only if you used the InnoDB table format, which AFAIK isn't the default. And InnoDB has some odd restrictions like no FULLTEXT indexes, AUTO_INCREMENT may reuse old values for new rows once you restart the daemon, 'show table status' doesn't give accurate statistics, 'load table from master' for replication doesn't work.

And MySQL 5 is still in beta, so IMO that doesn't count yet.

 

[Apathetic]

Originally posted by: EagleKeeper

Originally posted by: MrChad

Originally posted by: EagleKeeper

Originally posted by: KLin

Originally posted by: Nothinman
I belive Access locks the entire table for writes, actually think MySQL does the same thing unless you use the InnoDB table format. PostgreSQL will probably do all of what you want, but it's harder to setup, not as bad as Oracle but worse than MySQL or MS SQL, since it's designed to be a real RDBMS.

Access uses record level locking.

I know that the Access application itself will perform record locking and handle stored procedures when running VBA applications.

Remember I am only intending on using Access as the back-end database. MDB file format.

How many concurrent users will your application need to scale to? How much data do you need to handle?

Anticipate 3-4 concurrent users at any one time. Very light database I/O load.
Database sizing has not been done at this point. Detailed confinguration information from the field equipment (new product) will not be available until 6 months from now.

Expect 100-200 data parameters in the base configuration mulitplied by anywhere from 50 to 3000 field units. Each parameter wil have 5-6 fields associated with it.

One of the reasons that I am planning on using ODBC is to allow scaling up of the database(s) as needed if Access can not handle the load.

That's good because Access does NOT scale well at all. I would HIGHLY recommend you use either MySQL or Postgres from the start.

Dave

 

[EagleKeeper]

Ok - Now we are getting some answers to my questions.

As I am understanding it and based on my research.

1) Access will not support recording locking as a back-end database using ODBC.
2) Stored Procedures/Triggers will not exist - I will have to impliment them within the application.

in Response to Descartes:
I will be using VC++ for the applications that will interface with the ODBC.
Because of many different overall functionalities required for the application (field interfaces, third party applications, concurrent user interfaces, etc) I do not even want to think about writing a VBA within Access.

Access is being used as a MDB database file only using the ODBC connections. Having the Access applicaiton on my desktop makes it eay to play with the schema and test out queries before implimenting them within the VC++ application code.

On other projects, I have easily moved from Access to MySQL and SQL Server with no difficulty; just imported the Access database using their tools; repointed the ODBC connection and up and running.


For all that have tried to helped, Thanks.

I was not aware of some of the details of the issues that were brought up and will make sure that what ever I migrate to will be properly configured accordingly.

 

[SagaLore]

use FoxPro. :Q

 

[EagleKeeper]

Originally posted by: SagaLore
use FoxPro. :Q

Why?

 

[Bulldog13]

Originally posted by: Nothinman

Proliferation of bad software should bother everyone.

ONLY THE TERRORISTS WIN !