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.

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

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??

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

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.

i think what's driving this is ...

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

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.

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.

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

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.