Consulting

Results 1 to 9 of 9

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

  1. #1
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399

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

    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.
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  2. #2
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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!)

  3. #3
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    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.
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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

  5. #5
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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

  6. #6
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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.

  7. #7
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    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]
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  8. #8
    VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,168
    Location
    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.

  9. #9
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    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]
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

Posting Permissions

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