MySQL vs. Access

  • (2 Pages)
  • +
  • 1
  • 2

23 Replies - 4909 Views - Last Post: 04 June 2013 - 09:21 AM Rate Topic: -----

#1 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

MySQL vs. Access

Posted 04 June 2013 - 03:50 AM

I've built a database in MySQL that someone else wants to use. That person has no knowledge of queries or anything to that effect and thus I have been spending hours upon hours on building a user friendly interface on top of it with C#. However, even though I'm very far along with it, I'm afraid it might be too time consuming to finish as my schedule is now.

So my question is this: This is a database that only 2 people are going to use. Can I connect the MySQL database to Access and host it online so that they can both work with it? Is it difficult to do/would I need to design it specifically for Access?

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL vs. Access

#2 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

Re: MySQL vs. Access

Posted 04 June 2013 - 04:08 AM

Another thing: I recently came across a thread citing numbers of problems for using MySQL as a back end for Access, does anyone here have experience with that? Would I be better off making the database in Access, and if so, could I still host it online?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 04:36 AM

Access is a desktop application - a file-system database. It is not designed to work with an online database. While it is technically possible for Access to act as a front-end to a hosted MySql database, it is not recommended (and complicated)!

If you want to build the whole thing in Access then you would need to use SharePoint. This costs money and needs to be maintained.

ASP.NET could be used to share Access data across the internet, but this, by default, creates a website rather than a (desktop) application. But if you go this route you should stick with MySql (or SQL Server) rather than Access.

Is it Access 2003? If so, then a possibility is to create a Replica. That is, create the whole thing in Access, create and give a replica to your friend. Then you would both be able to use the data and later synchronise the data. But I suspect this is not what you are after.

Sorry that I don't have a simple solution for you.
Was This Post Helpful? 1
  • +
  • -

#4 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: MySQL vs. Access

Posted 04 June 2013 - 04:37 AM

MySQL and Access are both separate RDBMS; so a database created in MySQL will not be compatible with a database created in Access.

Usually RDBMS's can export their database schema so it can be imported in another RDBMS. However this relies on small implementations of the SQL standard in the RDBMS. Unfortunately, Access does not implement the SQL standard in the same way MySQL does so this feature is not appropriate.

Access is a very poor multi-user database, it doesn't really fulfill ACID. My advice would be to use MySQL where possible and only use Access when you absolutely positively need to (i.e. one user or there is only going to be two users using the database lightly). The problem is MySQL doesn't really have any good frontends like Access does. PHPMyAdmin is the one I've used the most.

yes your could still host it online, but you'd need a way to connect your front-end to the Access database engine
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 04:51 AM

View Postandy_pleasants, on 04 June 2013 - 11:37 AM, said:

Usually RDBMS's can export their database schema so it can be imported in another RDBMS. However this relies on small implementations of the SQL standard in the RDBMS. Unfortunately, Access does not implement the SQL standard in the same way MySQL does so this feature is not appropriate.

It is still possible to export the schema definition and data between MySQL and Access. It just requires a good text-editor and knowledge of the syntax differences. There probably exist tools or plugins to do this automatically (to some extent).
Was This Post Helpful? 1
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 05:01 AM

http://dev.mysql.com...ess-import.html

Simples.
Was This Post Helpful? 1
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5818
  • View blog
  • Posts: 12,669
  • Joined: 16-October 07

Re: MySQL vs. Access

Posted 04 June 2013 - 05:09 AM

First, Access is a toy. Used as a "database" it's pretty much limited to a single user.

However, Access is also an odd kind of user front end. Regular users can often get the little beast to do amazing things. Users love Access.

The solution is to give the user an Access file with all the tables actually being links to a real database. The data will then be handled where is should be and the user has their Access warm fuzzy.

You can do this with MySQL. You can do this with any database that supports some kind of windows connectivity.

I will occasionally, more often than I'd like, get the "we need to share an Access database" request. After I fly into my "don't use Access, never use Access, Access is evil from the depths of hell" rant, I explain our standard protocol for the situation. All Access data with be exported into a SQL Server database. The tables will then be dropped in Access and replaced with server links with identical names. The user list will be put into an Active Directory group set up with full permission to the SQL Server tables. No, I refuse to support fine grained permissions ( though I could ) because it's bloody Access. Be happy the data is now backed up and supports more than one user.

Good luck.
Was This Post Helpful? 2
  • +
  • -

#8 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

Re: MySQL vs. Access

Posted 04 June 2013 - 05:46 AM

View Postandrewsw, on 04 June 2013 - 04:36 AM, said:

Access is a desktop application - a file-system database. It is not designed to work with an online database. While it is technically possible for Access to act as a front-end to a hosted MySql database, it is not recommended (and complicated)!

If you want to build the whole thing in Access then you would need to use SharePoint. This costs money and needs to be maintained.


They're using Access 2010, and well, there's no data in the MySQL database except for a few testing records, so that's not an issue. I think this might be what I 'ought to do. One of them has both Access and Sharepoint on their computer, and purchasing another two copies might not be such an issue for them. Unless it costs extra to use Access with Sharepoint?
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 05:52 AM

View PostTenderfoot, on 04 June 2013 - 12:46 PM, said:

Unless it costs extra to use Access with Sharepoint?

I don't know, but I doubt it. Good luck.
Was This Post Helpful? 1
  • +
  • -

#10 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

Re: MySQL vs. Access

Posted 04 June 2013 - 05:56 AM

View Postbaavgai, on 04 June 2013 - 05:09 AM, said:

First, Access is a toy. Used as a "database" it's pretty much limited to a single user.

However, Access is also an odd kind of user front end. Regular users can often get the little beast to do amazing things. Users love Access.

The solution is to give the user an Access file with all the tables actually being links to a real database. The data will then be handled where is should be and the user has their Access warm fuzzy.

You can do this with MySQL. You can do this with any database that supports some kind of windows connectivity.

I will occasionally, more often than I'd like, get the "we need to share an Access database" request. After I fly into my "don't use Access, never use Access, Access is evil from the depths of hell" rant, I explain our standard protocol for the situation. All Access data with be exported into a SQL Server database. The tables will then be dropped in Access and replaced with server links with identical names. The user list will be put into an Active Directory group set up with full permission to the SQL Server tables. No, I refuse to support fine grained permissions ( though I could ) because it's bloody Access. Be happy the data is now backed up and supports more than one user.

Good luck.


Uh-oh. I believe I'll need to study a little bit of basic Access before I completely understand that. But you recommend SQL Server for this? It'd take me a second or two to recreate the same database with SQL Server, but that's still something they don't own so it'd have to be the express edition, but maybe that's not a problem. But you'd recommend using SQL Server with Access, and then hosting the database online?

I still might end up with Access/JET and Sharepoint but it may very well depend on the costs and, well, how practical the interface is. I know with 99.9% certainty that only 2 people will have to use the database, and that it has roughly 10.000 records that I will place into it from excel to start with. They add about, I'd guess.. ~200 records monthly maximum. So Jet should be able to handle that for a while (as far as I know).

This post has been edited by Tenderfoot: 04 June 2013 - 05:57 AM

Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 06:07 AM

I've never used SharePoint so someone may correct me, but I believe it defaults to SQL Server as its back-end. You can use Access 2010 to build front-end forms and then upload them to the SharePoint Server. Access 2010 is closely allied with SharePoint and there are menu (tab) options to upload database objects to it.
Was This Post Helpful? 1
  • +
  • -

#12 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 06:13 AM

Very useful page @MSDN
Was This Post Helpful? 0
  • +
  • -

#13 Tenderfoot  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 11
  • View blog
  • Posts: 160
  • Joined: 21-March 12

Re: MySQL vs. Access

Posted 04 June 2013 - 06:17 AM

View Postandrewsw, on 04 June 2013 - 06:07 AM, said:

I've never used SharePoint so someone may correct me, but I believe it defaults to SQL Server as its back-end. You can use Access 2010 to build front-end forms and then upload them to the SharePoint Server. Access 2010 is closely allied with SharePoint and there are menu (tab) options to upload database objects to it.


That sounds great, I wouldn't mind building the same database in MS SQL at all. Only problem is that I've never looked into either SharePoint or Access, but I'm going to, just wanted to make sure I wasn't completely wasting my time.
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3458
  • View blog
  • Posts: 11,707
  • Joined: 12-December 12

Re: MySQL vs. Access

Posted 04 June 2013 - 06:29 AM

I believe you can just build the entire thing in Access, then upload it to SharePoint. But maybe its better to build SharePoint Lists (SQL Server tables?), link to them from Access, then design your front-end. Not sure. That page I linked to provides a number of options.

Good luck. Andy.
Was This Post Helpful? 1
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9187
  • View blog
  • Posts: 34,489
  • Joined: 12-June 08

Re: MySQL vs. Access

Posted 04 June 2013 - 08:00 AM

Woah..woah..woah. Hold up. Doing the solution end run around to installing Sharepoint is not a right solution. You do not want to install an enterprise wide collaboration environment just to share a dinky mysql database (or access for that matter) and two people. That would be akin to building a SuperWalmart so you have a place to a morning cheese danish.

Where's this mysql database you have already built that someone wants to use? Is this on a box on the network? You can easily enable network access to your sql installs and just have the app point to it.

Example:

http://msdn.microsof...=sql.90%29.aspx
http://rclermont.blo...ork-access.html
Was This Post Helpful? 3
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2