Results 1 to 11 of 11

  1. Post
    #1

    Converting Access 2010 DB to SQL

    I have a client who has a database which is their CRM. This DB was running on a local pc just shared over the network, but now they need to be able to access it remotely, and I've been advised the best thing to do is convert it to sql.

    Is there an easy way to do this?

    I'm kind of hoping for some tool that I can just upload the db to and have it host it somewhere, does this exist?

    FYI, I'm not a Microsoft guy, if someone can do this for me, happy to chat and work something out.

  2. Post
    #2
    What kind of SQL does their CRM support?

    Does it necessarily need to be offsite? Which is more important, money or performance?

    How many users?

    Setting up either and making it remotely available is easy, setting up either and making it secure and remotely available is entirely different.

  3. Post
    #3
    Sounds like Access could be their entire CRM, e.g. forms and crap for the front end. Or is there an actual CRM there - if so, what is it and what version?

    I wouldn't trust any tool with anything but the simplest of Access databases. Depending on your location and time frame I may be able to take a look - but wouldn't be able to work on anything until the end of October.

  4. Post
    #4
    suntoucher wrote:
    What kind of SQL does their CRM support?

    Does it necessarily need to be offsite? Which is more important, money or performance?

    How many users?

    Setting up either and making it remotely available is easy, setting up either and making it secure and remotely available is entirely different.
    This IS their CRM, it has a front end with a bunch of forms etc.

    Does it need to be offsite? This is the question I first asked, but apparently as the staff are now working remotely, yes.

    5 - 10 users only, just staff members.
    Security is an issue, as this database contains info on all their clients, as well as a whole lot of info about them.

  5. Post
    #5
    suntoucher wrote:
    What kind of SQL does their CRM support?

    Does it necessarily need to be offsite? Which is more important, money or performance?

    How many users?

    Setting up either and making it remotely available is easy, setting up either and making it secure and remotely available is entirely different.
    With a managed service it might not be a big deal to secure it, depending on what kind of flexibility they need.

    I don't know what formats you can export from Access to but one of these will probably cover it:

    https://docs.aws.amazon.com/AmazonRD...AnySource.html

    https://docs.aws.amazon.com/AmazonRD...lExisting.html

  6. Post
    #6
    A.brdgr wrote:
    This IS their CRM, it has a front end with a bunch of forms etc.

    Does it need to be offsite? This is the question I first asked, but apparently as the staff are now working remotely, yes.

    5 - 10 users only, just staff members.
    Security is an issue, as this database contains info on all their clients, as well as a whole lot of info about them.
    Can Access forms access an SQL database? Not a question I've ever posed but my gut instinct says no.

    If that's the case, then you need a new front end as well. Forms aren't a thing with SQL. So that might be a thing you need to consider.

    The off-site question remains unanswered unless on-site doesn't have an internet connection. They could have a machine gathering dust in the corner of the room that could act as a server if need be. Or a regular NAS can do it.

    Otherwise they can fire up a VPS, or use AWS. But simply putting it somewhere is easy, but making a secure connection to it from your shiny new front end will take a bit of work. Eg, do they VPN in first, then fire up their app. Or is it just publicly available on user/password? Key pairs would be more secure than the latter, but you'd have to generate keys on every machine and set up a method to make them available (like Pageant on Windows).

    Then it's a question of MSSQL (SQL Express sounds like it would be fine), usually on Windows with a Windows license, or MySQL, usually on Linux with no cost but the machine.

    These are all of the money questions. Sending your Access data to a MySQL database is easy, but there's a whole host of other stuff involved.

    Cheapest method would be grab an old box, Chuck Linux on, MySQL and write their app to access it via static IP (which they may or may not already have). Then use a free Access to SQL conversion tool. The static IP could be the only cost. But then the next part is, "What is accessing this database?"

    Because it's likely no longer Access Forms.

    Money is no problem method would be to go the whole hog, and get them a proper CRM system that's hosted wherever (on-site is fine), then export the Access DB data into a format that can be imported by their new CRM system.
    Last edited by suntoucher; 10th September 2019 at 9:26 pm.

  7. Post
    #7
    Another cheap alternative with minimal reworking could be to grab a box, old or new. Make that a shared location on their network then set up a VPN. They connect to the VPN remotely and boom, they can still access their Access remotely.

    This would probably be the best short term solution whilst you talk them into a real, long term solution above.

  8. Post
    #8
    Just thinking about what you said, it sounds like they're getting off-site confused with available-off-site. Like as if the cloud were this mystical place of wonder.

    On-site stuff can very easily be accessed off-site.

  9. Post
    #9
    suntoucher wrote:
    Can Access forms access an SQL database? Not a question I've ever posed but my gut instinct says no.
    Access will quite happily access (lol) tables or views in a SQL database through an ODBC connection.

    Easiest and cheapest way to do this is if they already use a VPN. Stand up a MSSQL (Express) database, move the tables and queries over to SQL tables and views. Link the Access database to the SQL database and give everyone a copy. You would need to do some multi-user testing to make sure it didn't lock tables for longer than necessary to complete a transaction, and that it failed gracefully if there was an existing lock (sounds like a rare event based on the number of users).

    The more thorough option would be to re-write all the forms in something .NET as well as stand up the database. But that's more expensive and time consuming, and they would probably be better served buying a generic CRM software (on prem or cloudy) which meets their needs and can load their data.

  10. Post
    #10
    scholar wrote:
    Access will quite happily access (lol) tables or views in a SQL database through an ODBC connection .
    Bingo. Got to love ODBC.

  11. Post
    #11
    suntoucher wrote:
    Another cheap alternative with minimal reworking could be to grab a box, old or new. Make that a shared location on their network then set up a VPN. They connect to the VPN remotely and boom, they can still access their Access remotely.

    This would probably be the best short term solution whilst you talk them into a real, long term solution above.
    The Access db already sits on its own little box, an HP Elitedesk thing which I set up a while back for them, this is shared over the network.

    I think this is probably the easiest fix for the time being.

    I also clarified with the owner what he meant, and you are correct, he just wants to access it while off-site.