Access db to MySQL

[anishkgt]

Hi All,

does anyone know how to transfer existing Access db to mysql. Would this increase performance as in searching and loading of the data.

 

[sneuberg]

http://www.bullzip.com/products/a2m/info.php looks interesting. If you are going to convert your backend to MYSQL and then link your Access frontend to it, please let us know how it goes. I'm betting you'll have quite a story to tell.

 

[jdraw]

anishkgt,

What exactly is the current issue?

Would this increase performance as in searching and loading of the data.

 

[anishkgt]

http://www.bullzip.com/products/a2m/info.php looks interesting. If you are going to convert your backend to MYSQL and then link your Access frontend to it, please let us know how it goes. I'm betting you'll have quite a story to tell.

Thanks sneuberg. Tried that app but there was some need to add an odb connector which i had no idea how to. I am hoping to do do so but seems like that i won't be possible by a noob.

The existing setup has no problem as such but i wanted to see if this is possible as the MySQL is able to moitor the health of the DB.

Was hoping i could get some help from here.

 

[sneuberg]

Thanks sneuberg. Tried that app but there was some need to add an odb connector which i had no idea how to..

This video https://www.youtube.com/watch?v=K3GZidOwGmM looks good for that albeit it goes pretty fast so you will probably have to pause it here and there.

 

[anishkgt]

Ok found this on the way video tutorial https://www.youtube.com/watch?v=zu-8T4ERPN8

So i am guessing i have to migrate my existing table for ms access to MySQL, rite ? how do i do that ?

 

[anishkgt]

hey sneuberg !

When coming to the ODB connection part, is that something done after having a DB already setup in MySQL ? will have to learn how to do that. are there any steps to convert the existing Access db.

 

[anishkgt]

Now am wondering if i need to have an ODBC connector, i think i will have to just migrate the existing Access db to MySQL. any ideas anyone ?

 

[Minty]

The ODBC connector provide a method for you to link to the new backend tables that you migrated to MySql.

If your current database is a split FE / BE design experiment by creating a new test table in MySql then use the ODBC wizard to link to it. Once you have that established you should be able to work out how to do the rest.

 

[anishkgt]

The ODBC connection is what am trying to figure things out. how do i connect it to the MS access DB? but before that i have to migrate the existing access db to MySQL. how do i migrate it to the MySQL ?

 

[jdraw]

anishkgt,

What exactly is the issue that is suggesting a move to MySql is warranted?

You can use your Access front end to work with any database supporting ODBC. In MySql that's what the ODBC connector is for.

There are several youtube links based on this Google search

 

[anishkgt]

Ok migrated the Access db to MySQL (https://dev.mysql.com/doc/workbench/en/wb-migration-database-access.html) now trying to link them from Access

 

[sneuberg]

anishkgt,

What exactly is the issue that is suggesting a move to MySql is warranted?

 

[anishkgt]

Jdraw,

The main concern is a way to monitor CPU usage, connections to the db, users connected, network activity to and fro from the db. So thought i'd give MySQL a try.

 

[anishkgt]

Ok got it done ! Attached here are some pics showing the status of the db.

I've migrated the access db to MySQL. Linked this db to the front end. Tried adding some data to this table but it seems like there seems to be some rights. I could see that a primary key is not generated when a new record is open.

where do i set the permission ? i could see that the connection to the MySQL is done by the user root.

 

[Minty]

You will need to set permission on each table on the backend to allow connections from your users.
The normal business route is to allow domain users read / write access to the tables they need to update (this is from MS SQL, but it must be similar in MySql)

 

[anishkgt]

This is not on a domain but on a workgroup basically on local pc. how is the rights sets on MySQL. Since the front end is connected via the ODBC how can we see who is connected. There is tab for user and privileges and the ODBC is connected via root user.

I can add records from the front end but the the PK field does not auto generate the PK and i know that has something to do with the MySQL tables where the auto numbering has to be enabled but don't know where exactly.

 

[anishkgt]

ok found the place to do that, that has to be set with the MySQL user. That was strange, did not know with the database itself there were restrictions. So i checked the AI and G and when applied it gave this error

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `database4`.`tbldelivery` 
CHANGE COLUMN `DelID` `DelID` INT(10) GENERATED ALWAYS AS () VIRTUAL ;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VIRTUAL' at line 2
SQL Statement:
ALTER TABLE `database4`.`tbldelivery` 
CHANGE COLUMN `DelID` `DelID` INT(10) GENERATED ALWAYS AS () VIRTUAL

 

[Minty]

I'm not familiar with MySql I'm afraid, but on MS SQL you need to set a field as being Identity and provide a seed etc.

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Shows the context

 

[anishkgt]

Thanks minty. Thats a little too complex. Anyways will google some.