Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: Access Db to MySql Question

    I am using Access 2000 DB with VB 6, enterprise edition.

    how much work to switch to MySql??

    would most of the code work?

    does MySql support multiple users??


    would the connection string be the only thing to change??

    I thought that MySql was free but it looks like maybe I have to release any application that I develope with MySql to the public, is this true??

    my application is special and the chance anybody else would want it is slim.




    any help would be appreciated.
    Last edited by RonR2; 11-22-07 at 09:42.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access uses sql (the language) and so does mysql

    however, they are somewhat different dialects

    there are (minor) differences in functions, particularly date functions, concatenation, some string functions, and so on

    you will definitely need to look at every query, but the conversion is straightforward

    you do not need to "release" your app if you don't want to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    I am using Access now and there is only 1 line of SQL in my whole program and that line is used to open the table.

    I used VB 6 for all the UI in my DB.


    Access can be used without SQL commands, are you saying that Oracle & MySql will only accept SQL commands??

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry I thiks theres a bit confusion here
    Access is DB front end which can talk to (virtually) any db backend whether thats file based like xBase, Jet (MDB) upto server prodcuts like SQL Server, MySQL , Oracle et al.

    If you are using VB6 then its highly unlikely you are using Access. its more likely you are using JET. Its also highly unlikely that you are only using one line of SQL, especially to open a db... otherwise there would be no point in connecting to SQL database. What I suspect is happening ias that you are using lots of SQl.. its just that you haven't explicitly defined the SQL.. you've probably let wizards in VB6 write the SQL for you.

    Generally speaking its pretty easy to move an Access app from JET to another data storage mechanism.. but if not designed properly then you may get the worst of both worlds. designing an app for a server backend requires a lot more thought.. usually in how you retrieve the data, where you do you processing, how you tune your SQL.

    Unless you have to I can see no significant reason to shift from a JET backend to a server backend, unless you need to.. usual reasons for that are JET is croaking under volumne of connections, transaction, and then a server product can work fine. There should be no licensing issues if you are stroing data in the JET mdb format. there will be if you are using Access in a mdb form

    HTH

  5. #5
    Join Date
    Mar 2004
    Posts
    12
    I AM using an Access DB(.mdb). there is only one line of SQL code. I am NOT using wizards. I created the blank .mdb file with Access and that is all.

    there is no licensing issue because I have Access(licensed) and all my customers have Access(licensed).

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RonR2
    I AM using an Access DB(.mdb). there is only one line of SQL code. I am NOT using wizards. I created the blank .mdb file with Access and that is all.

    there is no licensing issue because I have Access(licensed) and all my customers have Access(licensed).
    ..in which case you are not using VB6, you are using VBA, and there is plenty of SQL in an access mdb.. its just that you don't actually see it.. Access talks to JET using SQL. If you access application is retrieving, manipulating or doing anythign with your data then you can virtually guarantee its using SQL.

    If all your customers / clients have licensed copies of Access then in my view why would you want to complicate things by installing a separate server product like MySQL. waht is driving this question, why do you think you many need to use MySQL?

    to get the best of of MySQl (or any server product) requires a significant amount of effort compared to using access with JET... not something for the faint hearted or beginners.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think what's driving this is ...
    does MySql support multiple users??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Posts
    12
    Quote Originally Posted by healdem
    ..in which case you are not using VB6, you are using VBA, and there is plenty of SQL in an access mdb.. its just that you don't actually see it.. Access talks to JET using SQL. If you access application is retrieving, manipulating or doing anythign with your data then you can virtually guarantee its using SQL.

    If all your customers / clients have licensed copies of Access then in my view why would you want to complicate things by installing a separate server product like MySQL. waht is driving this question, why do you think you many need to use MySQL?

    to get the best of of MySQl (or any server product) requires a significant amount of effort compared to using access with JET... not something for the faint hearted or beginners.
    r937 is correct. It is the multiple user issue I am working on.


    I AM using VB 6, enterprise edition, SP 6


    MySql is supposed to be free.



    here is my connection string to the Access DB.

    CN1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & "c:\Accessupgradetest\blank.mdb"



    here is the only line of SQl
    CMD.CommandText = "SELECT * FROM Incident ORDER BY INCIDENTDATE"



    Set rsIncidents.Source = CMD


    here is how I reference a field in the table:


    RsIncidents("ClientName").value = "Smith, Jim"


    RsIncidents.Update

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on how many users you expect to hit the database concurrently... if its around 10...20 then JET should be able to handle it... somewhere around 15...30 users JET starts to clog up.

    However if all you are doing is a single SQL read and a single SQL update (thats what rsincidents,update is doing) then JET should be OK for many users

    you could improve the performance by using a specific query to retrieve only the required records and required information in place of a select * and then issuing an appropriate update statement.

  10. #10
    Join Date
    Mar 2004
    Posts
    12
    I would guess I would have a max of 6 users.


    opening & closing the DB for every operation seems a little "fat" to me.


    I open the Db and it stays open until the user exits the program.


    I will do some more testing.


    thanks to all.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    leave the db open / connection not a problem (bu tremember to shut it on app close)

    only connect to the data when you need to
    get only the data you need
    then update it

    consider how you will manage concurrency issues (ie two users attempting to update the same data at the same time


    but I suspect for now using JET is probably good enough but consider changing the app in the medium to long term to use a server style approach to retrieving and manipulating data.. the approach is just as valid in JET as in server products

  12. #12
    Join Date
    Mar 2004
    Posts
    12
    I have it coded so that only the person creating the record can edit the record, the record stores the name of the user when the record is created and it cannot be changed except by the program administrator.

    users log in so I know who is attempting to edit the data.

Posting Permissions

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