VP-ASP :: Shopping Cart Software

Shopping Cart Software Solutions for anywhere in the World

US/Canada(Toll Free): +1 888 587 2278
Europe/UK: +44 (020) 7193 9408
Australia/New Zealand: +61 3 9016 4497

VP-Cart StoreFront Customer Forum

Home | Profile | Register | Active Topics | Members | Search | FAQ
Save Password
Forgot your Password?

 All Forums
 VPCart Forum
 VP-ASP 7.0 Questions
 Changing from Access to My SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

VP-ASP Super User

282 Posts

Posted - July 23 2011 :  10:32:57  Show Profile  Visit chrisredco's Homepage  Reply with Quote
I've never run my site on anything bu Access databse. My database size is currently 100MB+ and I have concerns about reliability and stability.

I'm considering moving to MySQL, but having never used this type of database, my concern is since I'm not hosting my site (alentuis currently), if I do convert, do I somehow have a copy of MySQL on my local machine to be able to extract data, alter tables, etc like I now do with Access on my local machine.

I know we're talking a whole different language here to get info out of the databse, my having no MySQL experience, am I opening up Pandora's box as far as my learning curve here once I convert?

Redco Audio

Senior Member

United Kingdom
1898 Posts

Posted - July 23 2011 :  16:15:39  Show Profile  Visit devshb's Homepage  Reply with Quote
There's usually 2 main ways that you can access a mysql database (ie aside from the actual site/cart accessing it through normal asp means using an ado connection etc)

1) MySQL Administrator
This is software installed on your pc (it's free to download), it's the equivalent of having Access installed on your pc, but instead of just opening a file, you specify the ip/userid/pwd of the database and it connects to it remotely (ie from your pc to the server), and it gives you a gui-style front-end so you can see the tables/data, run queries, and export them etc:
However, some hosts don't allow remote connections (ie from your pc to the database/server), so sometimes you can't use it, if that's the case you'd need option 2 below.

2) Most hosts that have MySQL installed will have a web-based front-end for the database, they'll give you the url for the mysql control panel; it's like a web/browser-based version of option 1.

SQLServer has the same kind of options, ie you can download sqlserver management studio (which is free) as a pc-based front-end, or the host would supply a browser-based alternative to achieve the same kind of thing.

Not easily being able to take copies of the database so you can play around without effecting the live site (eg to setup test areas when running a vpasp upgrade) is the only downside of switching to mysql or sqlserver, but if needs be then usually the host will allow you to create a test copy for a short time without much (if any) extra charges.

You shouldn't need to make any programmatic/asp changes on the vpasp side of things, you'd just change your shop$config.asp files to point to the new database/type (if you're using vpaspv7 don't forget to change the admin shop$config.asp file as well as the main/customer side shop$config.asp file)

If you're using our addons then you'd just need to change the byz_system_config.asp file to change the database type from "ACCESS" to "MYSQL" or "SQLSERVER" as appropriate.

Whether it's MySQL or SQLServer, you don't need mysql or sqlserver itself installed on your pc in order to view the data etc. For mysql you'd just download the free administrator, and for sqlserver you'd download sqlserver management studio (or use the browser-based alternatives if the host supply one).

eg you'd never need to install full-blown sqlserver on your pc just in order to access a sqlserver database from your pc.

If you've got some customisations which are database-type dependent then you'd need to change those.

The sql query syntax between Access, MySQL, and SQLServer is mostly the same, but there are some differences (which vpasp and our addons take into account and deal with automatically).

eg access uses "UCASE", whereas mysql/sqlserver uses "UPPER" for forcing something to upper case in a query, but then again all databases by default will ignore case when you do "where columna=columnb" so it's rare that you even need to use upper/lower on the sql query side of things.

The main differences are how it deals with dates, you'd definitely want to test things out on that front once you've got a mysql instance in place and being tested (eg to make sure the day and month values aren't getting incorrectly transposed; ideally test things for a date where the day can also be a month (eg 7/8)

Simon Barnaby
[email protected]
Follow us on Twitter: http://twitter.com/bigyellowzone
Web Design, Online Marketing and VPASP addons

Edited by - devshb on July 23 2011 16:35:49
Go to Top of Page

VP-ASP Super User

282 Posts

Posted - July 23 2011 :  16:27:51  Show Profile  Visit chrisredco's Homepage  Reply with Quote
Terribly helpful Simon, as usual.
Thanks for the help with understanding this.

Redco Audio
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000