Solved: Online SQL Database - access via VBA from Access 2007

[Movian]

Hey,
we currently have an access 2007 database that acts as a report creation system. We have recently decided to change our licensing model and wish to have an on line MySQL database that stored license information for each of our clients. This would give us the benefit of having a fairly straight forward online purchase system. And would also give us such functionality as being able to "Turn off" systems if they fail to meet their terms of payment for example. I have seen many threads talking about connecting to the MySQL database via the installation of a MySQL Driver.

I was wondering if there is any way to connect to the MySQL database without using this driver and just using VBA and if not would it mabye be possible to produce a php script that the database can post information to and get a result back, letting the php script do all the work of retrieving information from the database.
*note* this may be a better option infact as im sure there is some way to increase the security of the system by producing the mysql database in this format.

Unfortunatly having little experience with mysql (with the exception of setting up PhpBB and zen cart) i am unsure where to begin with this. So any Help is apriciated.

[Tommy]

Hi Movain,

I would suggest you use the php solution. It is much more secure. The problem with the VBA solution is Vista doesn't play nice with ODBC without some tweaking, the last time I tried to do something with it, so it more than likely has changed since then. (I would hope anyway) The "Preferred" way is NOW CDO. Tomorrow it will be BMA. LOL

The best part is you can still make a development machine and have all of the stuff setup to work seamlessly before going live. Just remember to remove your test data before going live so you don't embarrass you or somebody else. (No I have not ever done that in any shape form or fashion!)

[Movian]

Ok so this changes my question.

While i suggested a php go between simply because it seemed plausable it does not mean i have any idea how to go about it!!!

Any help on passing info between the php script and vba ?

i can figure out how to do the MySQL calls from php easily enough, there are plenty of tutorials for that.

[stanl]

The problem with the VBA solution is Vista doesn't play nice with ODBC without some tweaking, the last time I tried to do something with it, so it more than likely has changed since then. (I would hope anyway) The "Preferred" way is NOW CDO.

Sorry for the tangent, but Tommy... care to elaborate on this a little more- cuz it's very interesting. Stan

[Tommy]

Hi Stan,

The problems I had were with the mdac. What I found out was that even though it was already in the OS it was registered incorrectly (from reading some posts online). To get it to work you had to unregister the dlls and reregister them. Since the development enviroment I was working in didn't have a Vista machine and wouldn't get one, I had to debug and trouble shoot over the phone. So I could be wrong. Will not be the first nor the last.

I was laid off in Jan and couldn't find a job as a developer, network admin, or anything else in the computer world. So I haven't gotten the chance to play with anything since then.

Movian,

You

[Tommy]

Hi Stan,

The problems I had were with the mdac. What I found out was that even though it was already in the OS it was registered incorrectly (from reading some posts online). To get it to work you had to unregister the dlls and reregister them. Since the development enviroment I was working in didn't have a Vista machine and wouldn't get one, I had to debug and trouble shoot over the phone. So I could be wrong. Will not be the first nor the last.

I was laid off in Jan and couldn't find a job as a developer, network admin, or anything else in the computer world. So I haven't gotten the chance to play with anything since then.

[Movian]

Ok well i apear to be having some minor success with this.
I know have a button that sends multiple text fields to a PHP script that then takes the text and inserts it into the MySQL database

i am now working on a VERY VERY basic encryption method based on altering the ascii values.

this is a system i use effectivly in my VBA programming (i have Encrypt and Decrypt Functions)

However i am atempted to implement a Decryption process as part of the PHP Script. However i am unable to get it to do what i want....

here is what i have so far (i know its not VBA but the VBA tags will help keep it looking nice)
Note it IS putting Text into the database, however the Text is in its Encrypted un altered format.

[vba]$GetText1=$HTTP_GET_VARS["GetText1"];
$GetText2=$HTTP_GET_VARS["GetText2"];
$Decrypt1 = ""
$Decrypt2 = ""

for ($counter = 0; $counter < strlen($GetText1); $counter += 1){
$Decrypt1 = $Decrypt1 & chr(ord(substr($GetText1,counter,1)) -50)
}
for ($counter = 0; $counter < strlen($GetText2); $counter += 1){
$Decrypt2 = $Decrypt2 & chr(ord(substr($GetText2,counter,1)) -50)
}

//define query
$query= "INSERT INTO Test VALUES('','$Decrypt1','$Decrypt2')";[/vba]

[Tommy]

Movian,

My php is rusty so forgive me if I stray.
$counter is for strings right so if you change $counter to counter it may work.

[Movian]

actually the $ donates a variable of any type.


i got this working in the end

the final code is as follows

[VBA]for ($counter = 0; $counter < strlen($GetText1); $counter++){
$Decrypt1 = $Decrypt1 . chr(ord(substr($GetText1,$counter,1)) -15);
}
for ($counter = 0; $counter < strlen($GetText2); $counter++){
$Decrypt2 = $Decrypt2 . chr(ord(substr($GetText2,$counter,1)) -15);
}[/VBA]