Results 1 to 7 of 7
  1. #1
    Historypaul is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64

    MS SQL or MySQL?

    I'm ready to split my access front end and expand into a SQL server. I'd like to host the tables at a webhosting company, so I can access them over the WAN. Can anyone recommend a good webhosting provider for this service?

    My current webhosting company shows that it will actually host Access databases. If I only have 5 or less users, but they'll all be on at the same time and probably using the same tables, will this be sufficient? Here's what they say:

    As a customer hosted on the Dotster Windows platform, you can automatically connect to any Access database stored within your web directory. To do so, you'll need to embed a connection string in the body of any script that needs to talk to a database. Here's an example:'Database connection info and provider
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
    Server.MapPath("database\dbname.mdb")

    The only other thing is that I want the user level security that I can get with SQL. I could work around that, but it would be nice.

    My webhosting company also offers MS SQL hosting, but they state:
    MS SQL restrictions

    There are several restrictions on MS SQL database connections:

    • You cannot connect to your MS SQL database remotely; we only provide online database access through theManage MS SQL tool and myLittleAdmin. Learn how you can access and manage your MS SQL databases
    • Dotster does not support DSN connections

    So it doesn't look like they'll work for hosting the backend... Am I correct in that understanding?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am not an expert but, I would look for a host that offers a Microsoft IIS platform and DSN connections. At least if you are interested in remote SQL and or Access DBs

  3. #3
    Historypaul is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    I'm looking at Microsoft's Azure hosting, which seems to be fairly straightforward and easy to migrate to. Now that I'm looking at their cloud services, I'm wondering if it wouldn't be better to just host the whole thing as a virtual application instead of distributing front ends... I'm hoping somebody with much more experience in this can answer that...

  4. #4
    Historypaul is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    In doing some reading, am I correct in the understanding that once I migrate the backend to a sql server, I won't be able to add/change columns in my tables without using the SQL management tool? I'm still doing a lot of developing. Should I consider using the access database hosting by my webhosting company and later upgrade to sql to make development easier?

  5. #5
    Historypaul is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    Update for the other rookies.

    Well, I took the plunge and migrated my data to SQL Azure. I had a lot of errors at first, and it ended up making sense to install a local copy of SQL Express and do my test migrations to it, then review and repair before reattempting. I used the built in upsizing wizard to go to SQLExpress a few times, then dove straight into Sequel Server Migration Assistant (SSMA) to go straight from access to SQL Azure. It translated my tables and queries into T-SQL very, very nicely. I was paying attention to data types/compatibility with SQL during design, so the only real problem I ran into were my concatenate statements. Since I only had [item1] & [item2] in my access queries, it worked fine in access, but after being translated to T-SQL, if [item2] was null, the entire statement was null. It also screwed up some complex Iif functions in the queries if I didn't have an "if false" option declared. Once again, it would consider the entire formula null - not just that particular value. So I had to change a lot of simple [item1] & [item2] statements into (IIf(Not IsNull(item1), & item1,'')) & (IIf(Not IsNull(item2), & item2,'')) statements. Note that the "if false" portions always have an empty quotes. Doing this, it translated nicely into a CASE ELSE statement and stopped returning nulls if one of the items in the concatenate string was empty or null.

    Overall, the app is running really well. I changed a lot of subforms Record Sources to include a WHERE with a static reference to their parent form, which really cut down on the quantity of records the front end was downloading and improved the performance of the app considerably. Then, I had to unbind a lot of the subforms where I was using a tab control with multiple forms under it. Having them all try to load immediately kinda slowed things down. I also created a FOR EACH NEXT loop in the ON CURRENT property of the main form to unbind all of those tabbed subforms (except the initial view tab) each time the user moved to a new record on the main form. Now everything runs pretty quickly all the time.

    For the testing, I just included the sql login and password in the tables .connect properties (yes - I'm using a DSNless ODBC connection. It was so much easier than I thought it would be.) I wrote a routine to update all of the ODBC tables .connect properties quickly, so I could test it out with different drivers. I still get ODBC connect errors periodically... Still trying to work that out. It seems that there should be some vba retry code out there, but I don't know what it is yet, or how to use it...

    Next step, I need to build-in user level permissions at the SQL server and decide whether I'm going to try to use trusted connections, or require sql server logins each time the user opens the software...
    The next big steps for me are integrating an online file storage location where I can relate images and pdfs to links in my tables and load them on demand into forms, but use the same user level control over access.
    After that, it's image and file acquisition from devices cameras, networked scanner/printer, etc. Somewhere in-between there I need to work out getting my software to communicate with Google Contacts...

    If anyone has any advice on the "next steps" I'm working on, I'm all ears (eyes). Much thanks to June7 and pBaldy for answering many of my questions as I fumble through this stuff like a blind man in a dark void.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't worked much with a hosted SQL Server, so I'll defer to others on the next steps. To your problem concatenating fields with possible Nulls, check out the T-SQL CONCAT() function. It will implicitly convert a Null into an empty string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Historypaul is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    Thanks again, pbaldy. Your willingness to guide us rookies is fantastic.
    I wanted to update this, though, on the experience(s) I'm having with SQL Azure. I've been trying to figure out why - after 5 minutes of inactivity, I get an ODBC error for another 5 minutes. 5 minutes after the initial "timeout" everything goes back to working fine. I've tried everything I could find, and finally contacted SQL Azure tech support, who told me it's a known issue with MS Access caused by tables that are too large. Based on their explanation, I think they don't even understand the issue I'm having well enough to refer me to the known issue in question (http://support.microsoft.com/kb/2730109). Someone else seemed to state that they fixed this issue by enabling MARS, but I added that enabler to my connection strings with no improvement.

    So I'm likely going to be trying a new SQL hosting service, and I can't recommend SQL Azure, since they don't even want to acknowledge that it can be made to work smoothly with Access. And I'm really going to try to get everything translated into MySQL if I can, so I can see how well that works.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. help with next steps mysql
    By Hockeyguy1924 in forum SQL Server
    Replies: 1
    Last Post: 06-15-2012, 05:13 PM
  2. Access to SQL or MySql
    By Randy in forum Access
    Replies: 6
    Last Post: 08-05-2011, 08:38 AM
  3. mysql connecting help....
    By alex_raju in forum Access
    Replies: 1
    Last Post: 07-18-2011, 07:22 PM
  4. MySQL & Access
    By Scorpio11 in forum Database Design
    Replies: 3
    Last Post: 05-21-2011, 02:32 PM
  5. Using DSN alternatives with MYSQL
    By ajetrumpet in forum Access
    Replies: 2
    Last Post: 10-14-2010, 12:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums