Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: Ms access to mySQL

    Hello,

    I have an MS Access databse which im looking to convert to mySQL. the problem is the database currently uses VB code on some of the forms and im unsure whether they can also be imported and if this would cause some problems. I am trying to avoid having to redesign all the forms etc but im lost as to what would be the best option.

    Thanks in advance

    ps. im new to databases and have never done anything like this before so not too technical please

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you create a user interface with MySQL? My understanding was that it is just the back end, and you would still need a front end user interface, be it Access, VB, web page, etc. My guess would be that you would simply move the data tables to MySQL and then link to them in Access, continuing to use it as the user interface.
    Paul

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    non-techincal:
    keep your forms in Access as your user interface (aka frontend)
    use MySQL for what it is - a fast multiuser database engine (aka backend)

    move your tables to (most likely: create your tables anew in) MySQL and talk to the backend data from frontend Access.

    if this is your first pass, try linking the MySQL tables into Access so the whole thing just behaves like a grown up Access application that works with more than a handfull of users.

    when you have some time later on, investigate unbound forms in the Access frontend talking to the MySQL backend engine. with even a modest server running MySQL it should be good for hundreds of concurrent users.

    izy

    LATER: Paul - I just type slower than you, but we are saying the same thing
    Last edited by izyrider; 11-26-08 at 15:15.
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's some steps which hopefully will be helpful:

    1. Install MySQL onto your server or designated computer (I recommend installing onto a computer which has a server operating system such as MS Server 2000, 2003 or 2008.) Installing MySQL (or SQL Server) onto an XP or Vista operating system can result in future problems (ie. other user connection issues.)
    2. Setup your permissons on MySQL. Also make sure your "client" computers (where the mdb resides or other computers) can see/connect to the MySQL box.
    3. Create your database, tables, fields, and relationships on MySQL (same table/field names as what is currently in your MSAccess tables.)
    4. Create an ODBC DSN connection to the MySQL database using the ODBC utility (ie - Control Panel -> Administrative -> Data Sources (ODBC)). You need to do this in order to establish a connection from MySQL to the front-end for that computer (ie. MSAccess or whatever front-end you choose.) Note - the last step in configuring an ODBC connection is "Test Connection". If this fails, check your permissions to the MySQL computer and in MySQL. You must get a "Connection OK" response at this step or you won't get any further in the next steps. Note also - Name your new ODBC DSN name something simple and WITHOUT spaces or other odd characters!! (ie. MySQLPurchaseOrderODBCDSN) Spaces here are actually ok but get in GOOD habits now and avoid spaces or other odd characters! If your MSAccess tables/fields were created with spaces or other odd characters, now's the time to change them! (and your associated vba code in the mdb!) Note also: Each computer that's connecting to MySQL will need to have an ODBC DSN created on it. MAKE SURE that the ODBC DSN names created on each computer to connect to the MySQL database is EXACTLY the same on each computer. (ie. don't call the ODBC DSN to the same database XXX on 1 computer and then XYZ on another computer or you WILL have issues.) Also note: ODBC connections to a database backend are not as forgiving when it comes to using RESERVED words such as vba code is. So make sure you don't use any RESERVED words for your table/field names (such as a field name called: Date)!! Again, NOW is the time to get into good naming conventions!!
    5. Make a "copy" of your mdb and then open it. Delete the tables in the mdb. For now, you'll link the tables into your mdb (via the ODBC DSN you created.) Later, you can worry about writing vba code to connect directly to MySQL (ie. DSN-Less (not using ODBC) connections). Go to File -> Get External Data -> Link Tables. Change the "File Type" to ODBC Database(s). You should see your newly created ODBC DSN name (if you don't go back to step 4.) After selecting the ODBC DSN name, you should see a list of all your tables on MySQL for that db. Note: When you link your tables, you'll probably see dbo_MyTableName after linking. After you've linked all the tables, you can right-click on the table in the mdb and change the name from dbo_MyTableName to MyTableName (so your vba code works using the same table names.) I personally create all my tables in MSAccess first (with a dbo_ name) and then "upsize" them to SQL Server using the MSAccess Upsizing wizard. I don't think there is an upsizing wizard for MySQL though. After you've linked your tables, open the tables in the mdb to test. If you get any errors when opening the table(s) to view the data, check your ODBC connection as well as your permissions on MySQL. This is usually where problems occur.
    6. Now open your mdb as your normally would. You don't need to worry about any of your vba code working because as Izy mentioned above, linking the tables makes it so the mdb app is treated just as if you had MSAccess tables versus MySQL tables (but as a grown-up.) Just make sure that after you link the tables, the table names are the same as they were when they were MSAccess tables (as well as any field names.)
    7. To have other user's computer access the mdb, just make sure that each computer using that mdb has an ODBC DSN name as in step 4. (see utilities/vba code in the MSAccess code bank to have an ODBC DSN automatically created when the user opens the mdb.)

    Note: as you finish designing your vba code in MSAccess, if you need to make table changes, just make the changes on MySQL to your tables. Make sure though when your done making "ANY" table/field/relationship changes in MySQL, you then get into the mdb file and "Refresh" the linked tables (or simply delete and re-link the tables as in step 5.) You can delete "linked" tables in your mdb without affecting any data. Most often though developers make changes to the Server database (MySQL or SQL Server) and then forget to "Refresh" or "Re-Link" the tables in the mdb and then have problems.

    Last notes: MySQL (or SQL Server) is JUST a backend server to house tables and data. You STILL need a front-end interface to access/edit the data (whether it be a web interface, VB, or MSAccess, etc...). MSAccess is GREAT for a front-end and works extremely well as a front-end to a backend server housing the data.
    Last edited by pkstormy; 11-26-08 at 17:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pkstormy
    (but as a grown-up.)
    what the... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by r937
    what the... ?
    I was trying to relate "figuratively" that MSAccess tables compared to SQL Server (or MySQL) are not as matured or as rich in features and (what I "think" Izy was trying to relate, not trying to put any words into his email), MySQL/SQL Server is more of a "professional" application (ie. grown-up).

    Maybe (like a grown-up) was not the correct way to word it. You know me though Rudy, I always tend to get long-winded.
    Last edited by pkstormy; 11-26-08 at 20:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    MS Access is quite professional, and it's been around longer than MySQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I know! I'm one of the "biggest" MSAccess fans around (been using it since version 1.0). But compared to SQL Server tables, MSAccess tables are lacking in several features (ie. backups, tranlogs, security, etc...)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I by NO means wanted to imply that MSAccess is not professional! (it's been my source of putting food on my plate for years upon years.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    For the record, I'm also not a MySQL fan (but to each his own.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I prefer SQL Server too, but the cost difference often eliminates it.

    MySQL: $0.
    SQL Server: $OverTheTop

    Which to choose.........
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hey ST, cmon - you know SQL Server Express is free. How it compares to MySQL though is something I don't know. Still though, when I used MySQL (years ago) and compared it to SQL Server 7.0 (at the time), SQL Server was 10 times easier to use and integrated so nicely with MSAccess. It was well worth the price (you either pay for something easy to use or pay $ in time learning/dealing with issues/etc.. with another product.) I spent more time learning MySQL and dealing with all the issues than we did for the SQL Server license (which took me a few days to learn and get up and running - MySQL took me a few weeks to learn and a few weeks to get up and running.) I had lots of problems with MySQL (whatever version they had in 2000) where code had to be written just to create a table/fields (not to mention ODBC and other issues.) That all depends though on what environment you're working with. Our environment was a cross between Microsoft-Linux-Unix-Citrix. (to be fair, SQL Server didn't like the Linux/Unix environment but worked very well with Citrix. MySQL worked nicely with Unix/Linux but had issues with Microsoft, Citrix and Terminal Server.) (Still GUI versus non-GUI). SQL Server 2000 can be purchased pretty cheaply these days (if you find it around somewhere) and I personally like version 2000 over version 2005 (which I feel is "bloated").

    Now given MySQL has greatly improved over the years. Still though my philosophy on $ and time remains.
    Last edited by pkstormy; 11-26-08 at 21:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Nov 2008
    Posts
    2
    Thanks for all the quick replys!

    Im on a limited budget and cant afford to pay for any additional software so for my next question... would you recommend using SQL server express over mySQL taking into account my limited knowledge?

    my intention is that users can add and update records over the internet however i want to use additional queries and reports etc that will be acessed locally.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what language will you be using to write the web pages?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pkstormy
    Hey ST, cmon - you know SQL Server Express is free. How it compares to MySQL though is something I don't know. Still though, when I used MySQL (years ago) and compared it to SQL Server 7.0 (at the time), SQL Server was 10 times easier to use and integrated so nicely with MSAccess. It was well worth the price (you either pay for something easy to use or pay $ in time learning/dealing with issues/etc.. with another product.) I spent more time learning MySQL and dealing with all the issues than we did for the SQL Server license (which took me a few days to learn and get up and running - MySQL took me a few weeks to learn and a few weeks to get up and running.) I had lots of problems with MySQL (whatever version they had in 2000) where code had to be written just to create a table/fields (not to mention ODBC and other issues.) That all depends though on what environment you're working with. Our environment was a cross between Microsoft-Linux-Unix-Citrix. (to be fair, SQL Server didn't like the Linux/Unix environment but worked very well with Citrix. MySQL worked nicely with Unix/Linux but had issues with Microsoft, Citrix and Terminal Server.) (Still GUI versus non-GUI). SQL Server 2000 can be purchased pretty cheaply these days (if you find it around somewhere) and I personally like version 2000 over version 2005 (which I feel is "bloated").

    Now given MySQL has greatly improved over the years. Still though my philosophy on $ and time remains.
    Agreed in principle, however, it is the customer who decides. I would much prefer to use SQL Server too.

    SQL Server Express has those limitations too, which doesn't help, although I can't remember what they are either.

    MySQL seems pretty widely used for web apps though, so it can't be that bad. I've fiddled with it a bit and it seems perfectly fine for it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •