suggestion hosting MS SQL server for MS Access created database

  • Jeremiah Chung
    Participant
    Post count: 3
    #93534 |

    Hi,

    AllTheWord used to work on the language material database in the Win desktop environment. We would like to host the database with MS SQL and allow collaboration and sharing of the database. How can I find a free hosting service or discount hosting service for Bible Translation NGO? I google a bit and found something, e.g. accuweb, would allow the free trial service for 3 months. In case we tried for 3 months, how difficult is to migrate to somewhere else.
    I would appreciate trusted sources info and experience from the Christian NGO here. Thanks.

    Thanks,
    Jeremiah

  • Chris Hecht
    Admin
    Post count: 5
    #93537 |

    Hi Jeremiah,

    I presume you originally developed this as a stand-alone Access database and now want to use MS SQL as a backend while retaining the Access user interface. This is quite doable and there are tools in Access to do this. The issue is that most hosted MS SQL services need to charge fees because MS SQL Server is not free. I had looked at those services a number of years ago for a project, but not recently.

    Moving a simple SQL database is not difficult – primarily just a backup and restore of the complete database including the schema. You would then need to update the OBDC data source in the user interface to point at the new location.

    A possibly better way to share the information at less expense is to put the data into a MySQL database running on Linux. There are free or inexpensive web-hosting sites that can provide this service. The Access user interface can be connected to a MySQL database through OBDC, see here.

  • Jeremiah Chung
    Participant
    Post count: 3
    #93539 |

    Hi Christ,

    Thank you for your reply. That’s very helpful. When you mentioned it is not difficult. I just wonder whether we can estimate the effort in term of man-day/hours. For free web-hosting, I’ll google around first. I think that can be a very good subject to share with SIL/WO clients, as we free web-hosting along with mobile can help people share resources and the translated products and allow crowdsourcing collaboration. I’ll start another thread for this question.

  • Chris Hecht
    Admin
    Post count: 5
    #93541 |

    If you have someone with Access and MySQL experience, it will not take very long at all. Once you have a MySQL instance running (not difficult with most hosting services), you can export the data directly out of Access by following these instructions here. Depending on the size and complexity of your database and assuming that the data types in your Access database are acceptable to MySQL (they probably are), then it may take a couple of hours to a day.

  • Jeremiah Chung
    Participant
    Post count: 3
    #93546 |

    Hi Chris, I found the tool: http://www.bullzip.com/products/a2m/info.php Access to MySQL. However, I came across the error about authentication of the connection:
    “Properties=”SERVER=localhost;UID=jer;PWD=;PORT=3306;OPTION=0;STMT=;”
    Program Version: 5.5.0.282
    ODBC Version: 5.3.10.0
    Function: modMySql.MoveData2MySql
    Error: -2147217843
    Hint: Open connection to destination database
    Reason: [MySQL][ODBC 5.3(w) Driver]Authentication plugin ‘caching_sha2_password’ cannot be loaded: ?????????”
    I tried to look it up and google around, it seems related with MySQL8. I download MySQL5 and unzip it. I just wonder whether I can run multiple version SQL server, it seems very complicated, or should I just uninstall 8 and reinstall 5. Any suggestion?

  • Chris Hecht
    Admin
    Post count: 5
    #93548 |

    I would unistall MySQL8 before you install MySQL5 in order to prevent problems with incorrect component versions. I am presuming this is a test environment so there is no issues with changing versions.

You must be logged in to reply to this topic.