IMPORTANT UPDATE
Before going any further, please quickly read: Steer clear of Access Web Applications (AWA)
The following article explains How-to ‘Hybridize’ your MS Access Database In Office 365 Azure Database or How-to use an Office 365 Back-End database with your MS Access Desktop Front-End (FE). It is meant to be a step-by-step instructional guide of how-to to convert a standard split desktop database so that the data, the back-end (BE), is imported and available through an Office 365 account. So the data thus becomes available to anyone with an Internet connection, but the application itself, the front-end (FE), remains on each user’s local computer.
A few people have now asked me ‘Why such an article?’ If you are here, you already know the reason! Simply put, Microsoft has, once again, not provided a simple step by step explanation of how to accomplish this task. I was banging my head trying to get a simple ODBC connection functional and couldn’t locate instructions that actually worked anywhere. So once I finally got everything working smoothly, I decided I’d share with the rest of the world to save the days of frustrations I faced! Microsoft’s support (whether it be online documentation, Knowledge base, forums), more and more, simply disgusts me. This learning experience allowed me to learn that there is a flaw (it does not work! just a small flaw, lol!) in the Windows ODBC Data Source Administrator when creating File DSN to Office 365 SQL Azure Databases (that has been confirmed now by fellow MVPs), which I provide a work around to below. No where have I found this mentioned anywhere. This cost me 6 days of development and countless frustrations. Hopefully Microsoft will address this, eventually. I can only imagine the number of people faced with the same issue with no solution. But if you are here, you now have a solution!
Another question asked is ‘why ‘Hybridize’ a database when you could simply create a Web Database?’ Simple, VBA! Need I say more! Web Databases are primitive versions of MS Access databases and you cannot use any VBA whatsoever. A hybrid database bypasses this limitation while making the data available over a WAN (in the case of Office 365 – The Internet/World). Sort of the best of both worlds; get around Access’ native limitation of not being available over a WAN while bypassing Access’ Web Databases’ limitation of not allowing VBA.
Also, for existing databases, converting them into Web Databases can be involve a tremendous amount of work, to the point of being inconceivable altogether. By Hybridizing them, you can greatly benefit from this new technology while requiring very little changes to your standard database.
So what is involved?
Let’s get going.
First, before doing anything whatsoever, PLEASE make a backup copy of the database you are wanting to ‘hybridize’, before going ahead with any of the following instructions! This way should anything go wrong you can at any time revert back to the original copy. Never work directly with your good copy of your database!
Below are the step-by-step instructions as of 2014/01/07.
Office 365 Setup
Obviously, you need to open an account.
Login to O365 account.
Once logged in, click on Sites
Click on Team Site
Click on Site Contents
Click on Add an app
In the search control, perform a search for ‘Access’
Click on Access App
A Adding an Access app dialog will pop-up. Enter a name and then click on OK (will now appear in your list of assets, allow a couple minute for Office365 to configure things for use.). You should, after a moment, see something similar to the following where your newly created Access app is flagged as New.
Click on the newly created App.
Once the App loads, Click on Settings (the Gear in the top right-hand corner)
Click on Customize in Acces
Click Open (the pop-up at the bottom of the screen – Access should then launch on its’ own)
At the bottom of the Access screen, Click on Access (assuming your be is an Access database) from the Create a table from an existing data source menu
Simply follow the prompt to select your existing database back-end to pull the tables from to import into Office 365 SQL Azure Database
Now that you have successfully uploaded your database tables into your Office 365 SQL Azure database, you have to enable remote connections and take note of the DSN setting to create said connection. To do so:
Goto the File tab and click on Manage (under the Connections)
The following menu/dialog will appear. Click on From Any Location, as well as, Enable Read-Write Connection.
Click on click on View Read-Write Connection Information.
The following dialog will appear. Be sure to copy the information verbatim as it will be required to create a File DSN (or a DSN-less connection).
Close the dialog and then close Access. You can now log off of Office 365.
SQL Server Driver Native Client
If you do not already have the SQL Server Native Client 11.0 install you need to get a copy and install it.
Goto http://www.microsoft.com/en-ca/download/details.aspx?id=29065
Click on the Download button.
On the following page, click on the Install Instruction link which will expand a listing of the various files that you can download. Scroll down until you find the Microsoft® SQL Server® 2012 Native Client (about midway down).
If you are running on a 32bit OS, then download and install the X86 Package (sqlincli.msi). If on the other hand you are running on a 64bit OS, then download and install the X64Package (sqlincli.msi).
Create a File DSN
Note: If you are going to create a DSN-less connection (which is always a good idea) you can step over this step). That said, this is still the easiest method for beginners to get things up and running.
I’m not going to go into the details, just let me say that do not try to create a File DSN using the Windows ODBC Data Source Administrator utility as it seems to be very unreliable. Instead, simply create a new blank text document and give it whatever name you wish with a dsn file extension.
Then open the newly created text file and enter the following (replacing the appropriate value with those you retrieved from the MS Access ‘View Read-Write Connection Information’. Pay particular attention to the UID (username/user id as it includes the server!) and the SERVER as it is prefixed by ‘tcp:’.
[ODBC]
DRIVER=SQL Server Native Client 11.0
UID= db_1446956c_xxx_xxxx_xxxx_xxxxxxxxxx_ExternalWriter@ kcxxxxxxxxfx
Pwd=jsxxxxxxxxxxxxxxFu
Encrypt=yes
DATABASE= db_8332e578_xxx_xxxx_xxxx_xxxxxxxxxx
SERVER=tcp:kcxxxxxxxxfx.database.windows.net
Save and close the file.
MS Access Setup
Launch your MS Access Database front-end (FE).
Goto the External Data tab and then click on the ODBC Database from the Import & Link group
Click on the Link to the data source by creating a linked table and the click on OK
Go select the File DSN from wherever you saved it and then click on OK
Select the table(s) you wish to create linked table to and then click OK
You are done. Your front-end should now have read-write access to the Azure database on Office 365!
Final Conclusion(s)
At the end of the day, the hybrid database fills a need. It has to be noted that performance does take a hit when compared to local database or LAN databases. That said, performance tuning can greatly help with overall performance (like with any database) and aspects such as your Internet Connection can also greatly impact your ability to work smoothly.
Loved this article. This is exactly the solution I am hoping to implement for several mid sized apps I now admin. However, your comments in the last paragraph have me nervous.
“….performance does take a hit when compared to local database or LAN databases….”
I already have several small apps with BE data as SP lists with “standard” FE’s w/VBA. Users pull down copies of the FE I have posted to SP (hosted/cloud 2010) to their local PC and work linked back to the SP lists. We have had very good performance with this setup, however, the list sizes & number of connected users is small, <20K rows & <5 connected users. The mid sized apps each have one large table, (100K rows x 100 fields) & more users, (15-20). There are also multiple local lookup tables, queries, forms, reports, etc. Additionally our users are scattered among several states and mostly working from home. I did not even test migrating the large tables to SP as I feared the performance would be unacceptably slow. We did just get hosted SQL Server made available to us (business users, not IT) and I did migrate a table & test. The performance was very good even without any SQL Server optimizations. However I believe we would like have to publish our FE to Citrix and have users connect and work from there. IT really fights to keep Access from being used on Citrix unless you get VP level signoffs. SP is a business side tool and my hope was to eventually move all our apps there so as not to keep butting heads with IT. I would be very interested to hear more about your work/experience/testing with the 2013 hybrid style apps. We are supposed to get both O365 2013 & SP2013 in house for testing by the end of Q1-2014.
Thanks again for a great article !
Joe
Fantastic post. Worked out very well.
Thank you.
You are my hero! I was on the phone with Microsoft Tech Support for 2 hours and they did not know this!
You are most welcome!
Finally! I have wasted so much time trying to figure this out! This guide filled in the missing pieces for me. Thank you for taking the time to lay it out simply!
I’m glad it helped. I wrote it because I spend almost 2 weeks trying to get a simple database hybridized myself and found that there was no true, complete, tutorial. So I created this one!
I just don’t get Microsoft providing so very limited support for their own products. It would now appear that Microsoft support is, more and more, falling on the shoulders of the general public (bloggers, forum, …). They are very poor a providing help of any sort. Outdated, incomplete or simply non-existent.
I’m just glad I could help!
Daniel,
Thank you very much for writing this article. As you say, there is very little information on how to do this, even though it is a very useful technique to extend the life of existing Access databases.
Thanks for sharing your efforts so that I didn’t have to spend days repeating them!
Cheers,
Ian (Christchurch, New Zealand)
You are most welcome and I am glad it could help.
Great article, Daniel. I’ve yet to try the whole Access Web App thing because of the lack of support for VBA. Maybe this will prove to fill that void.
I remember creating an intranet app many years ago with FrontPage and using VBScript and a SQL back end. I wonder why MS is dragging its feet when it comes to the use of code.
Bill,
I to, have so many questions about the entire Web database (now obsolete), web apps approach taken by Microsoft. They have done such a poor job introducing this technology and are lagging far behind many of their competitors.
Their support is a farce!
Just figuring out which Office365 plan allows one to create/support an Access Web is challenging! Furthermore, Office365 is so very confusing. I spent soo much time turning in circles trying to figure out where to go and what to do. I personally think it is horribly organised. I sometimes truly get the feeling MS is trying to kill Access by doing things so poorly.
If they are going to wait another 3-4 years before another release of their Web App technology, I fear everyone else will simply continue to peck away at their client base.
Not supporting VBA is pathetic, but not supporting any web programming language at all is even worse!!! Javascript, jQuery, something?
Time will tell, but thus far I have been very disappointed with Microsoft Access’ initiation to the World Wide Web.
This looks like exactly what I need and I have followed your steps to the letter. I am stuck at the creating a dsn step. I’m really a newbie with all this so do mind clearing up a couple of things. When I create a text file, do I just open Word Pad? How do I save it with the dsn extension, since there is no such extension offered. Also when copying the read/view/write info, do I include the labels you put in, driver =, server = etc. In other words is it exactly as you wrote it only with my read/write info?
One more thing, do you have steps that do not involve knowing a techie talk for setting up an no dsn? I have about 12 users to share my db with, so whatever makes that the easiest for us.
Thank
Yes, when you create the dsn file, it should be verbatim what I included in the post including the DRIVER= …
As for the file extension, it should be YourFileName.dsn
To simplify distribution, as I mention in my article, I would suggest you look into using a DSN-less connection.
Ha.. I have also spent many days trying to work out how Access 2013 and Office work and how to get my back end db into Azure. I have managed to get it “up there”, but did not go through the Office Portal but rather through Access. Got my FE tables liked and all working, but now I have gone back to it a month later I cant open my BE db as its not on hte “recent” list and does not show up in my cloud location!@#$%
I suspect if I had created the db through the Office portal it might be visible through Access
I too have found hte bug in the ODBC creating a File DSN and have logged a service request with MS about it.
I have to admit that MS support for Office 365 has surprised me…they have actually provided some!.
That has not been my experience with MS support!
Just as an FYI, I informed MS of the File DSN issue (through our MVP program some time ago – before this article was created) but haven’t ever heard of any movement on the subject one way or another. It doesn’t appear to be a priority.
Ok, now trying to create dsnless connection. I have had code to do this with on-site SQL Server but its not working with the SQL Server db up in the cloud. Gives error:
“server connection failed. @@Contact Database Administrator”
I usually use this code with trusted connection, but obviously cant use a trusted connection with a cloud linked table as have to use the login and password supplied by O365.
Whats the best way to handle these linked tables to O365 Azure db with dsnless connections on multiple workstations?
Per my article, I would recommend you look over Doug Steele’s DSN-less connection article which can be found at: http://www.accessmvp.com/djsteele/DSNLessLinks.html
Daniel,
Thank you so much for this article. I, too, have been working months on my database project with no luck. Your article has validated my frustrations. I had reached a dead-end and completely stuck but am so hopeful now. I couldn’t even find any Microsoft phone support. I sure hope you keep this line of communication open as it seems to be a lifeline to many of us in this same boat. Thanks again for posting!
You are most welcome.
Sadly, for me, Web Apps are pretty much a dead-end with MS Access. They are simply too primitive and without any programming simply useless with the exception of the most basic of databases. I create complex application with all sorts of interactions with Word, Excel and much more, Web Apps simply do not permit this.
So although I was originally quite hopeful and thought I’d be creating many more articles on the subject, the more I worked with both Office365 and Office 2013 I ended up having to refocused my energies on more versatile development tools/languages moving away from Access for web applications.
MS seems to have rushed to put out a solution that simply isn’t up to part with what users actually need or what their competitors already have on the market.
Now with recent ‘improvements’ allowing us to incorporate Apps for Office for Access, you now have to master MS Access, get an Office365 account (figure that all out) and then master Visual Studio to create the App for Office… Access was simple because it was an all in one solution. Now however we are looking at needing 3 different tools, and it still can’t do nearly what can be done on a desktop version. Yet other technologies are all inclusive! MS has missed the boat. Hopefully they will improve upon their existing setup, they had better! There user base is eroding.
As you stated, their support is pitiful!
Best of luck to you in your development.
That’s too bad about Access. Access was always the one tool I had under my belt to keep me employed. All that knowledge I’ve gained over the years is apparently obsolete.
So I’ve tried your steps above and it’s still not working. I have three large tables and am now having timeout issues. There seems to be a magic number of 5000 records. Sometimes they import, sometimes they don’t. It’s odd. I’m able to use Sharepoint on the BE, but then I’m unable to use queries to change data from the FE. When I use SQL, I can get the data to connect on my personal PC or laptop, but when a new user tries to connect from another PC they get OBDC connection failure errors. I was really hoping this would solve my problem. I think it’s time for a database redesign to keep stored data in tables under 5000. That seems to be what’s causing many of the Sharepoint problems. Still not sure about the OBDC/SQL issue.
Again – thanks for posting.
There are a few properties you can tweak to try and improve your experience. For instance, take a look at http://support.microsoft.com/kb/153756 and http://www.dbforums.com/microsoft-access/1212406-odbc-access-sql-server-driver-timeout-expired-0-a.html and http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2010/02/04/ms-access-odbc-connetion-timeout-error.aspx
Also, whenever you are working with a lot of data, should I say good design practices, it is a good idea to only return one record at a time (or a handful at the most).
The only true exemption from such a statement would be when you are doing some sort of reporting. Then again, you’d make use of Pass-through queries to minimize the data transfer.
So you’d need to build you forms to open with no RecordSource or one that only returns 1 record, or no records at all. Then have a user selection that would then update the RecordSource to only 1 record being returned.
Then every single combo box and listbox will greatly reduce performance (especially in these hybrid databases!).
As I said though, the web apps are simply too weak to do much of anything at this point in time. I have been extremely disappointed by what MS has put forth in this realm.
If you need WAN access to a database, then IMHO, Web Apps aren’t the answer. The proper solution remains what it has been for over a decade now: use terminal services, CITRIX, Remote Desktop, …
Daniel,
Thank you so much for this article. I, too, have been working months on my database project with no luck. I was ready to give up on office 365. This has been such a help.
Just a note to those reading this article.
I’ve been working with Access linked via ODBC to SQL Azure for a few years now. It’s a good approach and works with all versions of Access not just 2013.
But my advice for anyone looking to go down this route would be to set up an Azure account and create the backend database yourself.
If you use Office 365/Sharepoint to do this, you will get an SQL Azure database but you won’t get full access to set up and manage the database. (Well I haven’t found a way and I’ve been looking hard!)
I finally got this to work. I did link only the tables I created, but I still wasn’t succeeding in linking or so I thought. The problem turned out to be that after I went through the ODCB steps, I needed to delete my local tables linked to the backend, and rename the web linked tables to my original table names. Now it’s working. Hope this helps someone else.
One note, MS tech support is completely, utterly and totally useless.
When I performed this operation I was working with an empty shell (no tables) so I didn’t encounter this problem. Thank you for mentioning it as I am sure this precision will help others!
Sorry folks you will just have to learn Visual Studio if you want to do anything that is a tiny little itzy bitzy complicated on the web.
I have developed a very large Access app over the last 10 years that runs my business and there is no way it can be rewritten using VS so I am developing new functionality (when it is appropriate) on the web.
I have tried and hoped and tried and hoped for 10 years to wait and see if MS would give Access serious web capability and early 2013 arrived at the conclusion that it *never* will.
Why should it? VS IDE is incredibly sophisticated and rich.
Access was long ago dropped by MS as a serious development tool and I rue the day (but starting a business I had no time to develop new skills) 10 years ago that I committed to Access instead of the .NET framework. (I’m talking desktop app here)
I have found the ASP.NET C#, JS, CSS and a myriad other concepts and languages incredibly difficult to master (I have been programming since 1975) but am glad I bit the bullet.
VS is where MS is putting ALL their development related resources into.
Access is totally dead and they should bury it but I guess they think newbies may be swayed by eg “easy web development” LOL.
MS spend all their time in making Access “pretty” and that’s about it.
Move your customers and personal skills over to .NET and you will not regret it in 2 years time.
Otherwise you will be stuck in the Access mire forever and with an ever dwindling list of clients to earn $s from.
Not disagreeing with what you have stated, but let’s not panic either. Access is still part of the newest Office offering from Microsoft (Office 2013). So it will still be around for a while. Also to put things into perspective, I still have clients running Office 2000 and 2003 (13+ years later) so there is a good chance people will still be running Access databases well into 2020 and beyond, so the Access skillset will still retain value for the foreseeable future. More over, if the unavoidable does occur, and people move away, the skillset will become a rarity and thus such developers will get paid a pretty penny.
That all said, it is a wise move for any developer to try and stay on trend with developing technologies, so learning Visual Studio can only be beneficial in the long run! No one can argue that point.
Thank you for the article. It is exactly what I needed. I do have some questions I hope someone can help me with.
Instead of using office 365, can I just use share point online?
I will have 5 computers with the database front end, is each computer considered a user?
Any help will be appreciated. Thanks for your help!!
I see you started a discussion on the subject at UtterAccess.com, this is the best place for such discussions. For anyone else, see: http://www.utteraccess.com/forum/Office-365-Sharepoint-On-t2017526.html&pid=2459250
a tremendous thank you ! i spent all my night trying to connect a front-end access DB to a SQL Azure DB created by WEB access. It’s your explanation (creating an ODBC source from a file.dsn) which has, at least, solved my problem !
One more time, thank you.
Thanks so much for this guide!
I am starting up a new business with a partner and needed to create a job tracking database that we could both easily access from our own homes, I was at first excited by the prospect of Access Web Apps but the more and more time i spent with it, the more i realised it was just not ready and lacked so many crucial features.
Now i can have the best of both worlds, cloud data but feature rich front end!
You’ve saved the day for me!
I am glad my article helped.
Thanks for the great article.
This helps in overcoming the shortcomings of Sharepoints limit of 5000 records in a list, and the lack of VB in Access Apps.
One addition for the dutch version: In the dutch version of Access there are two options for enabeling read links. The second one is in fact the button for enabling read-write links. only when clicking that second option you get the possibilities of getting the DSN info.
thanks so much for MS Dutch
Daniel,
Thank you so much for this. I’m totally new to databases, ODBC, etc. and I’ve run into probably a novice problem when going through what you mentioned related to the Create a File DSN/MS Access setup sections. I believe I have all the information correctly entered into a text file saved with the dsn extension. However, when trying to link to it, I get an error saying that it can’t open the server requested by the login. Client with IP address ##.###.###.## is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address.
Would it be possible for you to point me in the direction to resolve this? Thanks!
Nevermind. I didn’t enable allowing connections in the master database. Fixed it.
Great piece of work, you made my day !
5 out of 5 stars rating from me 🙂
I would like to say a big thank you for for this article. It has definitely helped drastically improve our database performance. One thing I would be interested in is if there was a way using a program or vba code to perform backups of the backend using this method? Thanks again!
Thank you for this very useful and well written article.
Great article, and many relevant links to other subjects and MVP sites.
Thanks for sharing !
Hi,
Using the methods above, is it possible to create backups at any point in time just incase things go wrong?
Backups of what? The Front-End or the Azure Back-End?
You can make backups of the Front-End as you please?
As for the Back-End, I believe, depending on your Office365 plan, MS is performing backups that you can use (but you’d need to look into that). You could always potentially copy the data locally using Append & Update queries. All of that to say, there are options.
hey DP – glad I found this – – I had actually found your original Q/A string of frustration I think over on an MSDN url……. My question is this: Office 365 or Azure ? ….. I actually have an account at each… I found the 365 site to be confusing in that it is offering me features I have no interest in if I am only wanting to use it as the ‘BE-in the-sky’ location. The Azure SQL (once you navigate to it within Azure) is fairly straight forward – though I’m new to it. So I’m leaning toward Access 2013 – Azure SQL rather than to Office 365 – – and while I know that 365 is implemented on Azure SQL it really all the other 365 stuff that is distractive in terms of the user interface. Have you any thoughts on this?
I am going to answer or comment on my own comment left a couple days back; on further research an Azure SQL account – while much more straight forward in terms of just being database oriented (rather than the other well intended features of 365 which I find distractive) – it is lacking any log-in security that the 365 service inherently provides.
With Azure SQL one must specifically open the firewall to an IP range that is to be permitted. This would be ok in a client/server situation where a company has fixed locations with static IPs. But all other companies, as well as any traveling users cannot be accommodated without opening the firewall and being insecure. In a corporate owned SQL Server environment (non Azure) one would use VPN technology between the users (with non static IPs) and the SQL Server. But there is no way to insert VPN technology in front of Azure SQL.
So while Azure SQL is fine as a web back end of an Azure hosted web app – and I think that is really the market it is aimed at – it has this issue in terms of being the back end to an Access app.
Am obviously in exploration mode and may learn more on this.
Ok it seems i named the text file as a DNS instead of DSN….but now it says the SQL Server does allow me connection to it – that i have to create a Windows Azure Firewall rule or something,….error 37000…?
Ok – it turns out i had the wrong database name in my dsn file – dope!
Thank you.
I have searched everywhere for this solution and was unable to find any.
I hope to try this solution soon.
how do i create a DSN file… I have no idea on how to go about it, can you explain to me in simple terms
Please refer to the ‘Create a File DSN’ section of this article. It explains in detail how to create the file and where to find the settings.
Thank you. That is fantastically helpful.
Why is Microsoft so consistently good at making simple tasks difficult? I wonder if they understand how much this hurts them.
Thank you very much. That is a very clear and good explanation!
Thank you for this. Fortunately I’ve only been stuck for a couple of days trying to figure this out, I fear it could have been so much longer, or I’d have abandoned all hope eventually. I now have a desktop DB backend hosted with Azure. This certainly adds a valuable string to my bow! Your article has been immeasurably helpful.
Great info! On quick question… will the server info including the id, password, and domain ever change or will they remain the exact same for years?
That is up to Microsoft. That said mine continue to work months (more than a year) later. But ultimately, we the users, are at the mercy of Microsoft with this regards. I’m sure they do not want to make such a change unless it were absolutely necessary.
Many, many thanks Daniel. This is the gift that seems to keep giving! I was trying to connect to my O365 Access web app from Visual Studio with no luck. Your post solved that in 5 minutes.
Please keep sharing your knowledge – it is MOST appreciated.
Hi, Thank you for your article I got stuck on the DSN file part and got some error, but after changing the order according to the access order, e.g. server, followed by database, username and password then the encrypt. the dsn file works. thanks a million man.
Thank you for sharing. Odd though. I generated mine through the windows odbc tool and it worked just fine.
You saved my life. I have two very complex Access databases that have to stay desktops that my boss wants made into Access SharePoint Apps. Not going to happen. However since our server is going away I have to make SharePoint the server and now I can. Thanks.
Thank you very much
You are my hero
Thanks for this article. Having set up a hybridized Access / SQL Azure application, I now want to explore using the power of the SQL Azure database. I want to create some user defined functions and stored procedures to process records and return results to my Access application.
I am logged into the SQLAzure database through SSMS as external writer. I have scripted a function using CREATE FUNCTION … in a query window. There are no syntax errors but when I execute the CREATE FUNCTION script I receive a message ‘CREATE FUNCTION permission denied in database ‘db_12……’
Is it possible to create user defined functions and stored procedures in SQL Azure? If so, how do I get around permission denied?
Sadly, although AWA run using Azure SQL, we have pretty much none of the advanced functionalities, nor any access to manage schemas, …
I’d urge you to add this as a suggestion through the access.uservoice.com page in the hopes that Microsoft will, one day, listen and add such features.
Daniel. Thanks for your reply. I will do as you suggest. It would be great to be able to harness the processing power of SQL Azure and to be able to create and run functions / stored procedures directly on the server by using ADODB connections and commands from a front end desktop database, rather than running queries on linked tables.
This is absolutely phenomenal!! I am planning on deploying the access desktop application to almost 50 end users. I have few questions.
1. With this kind of setup, how do I deploy a bundle or a .exe file to the end users?
2. Do they all have to have MS Access on their computers?
3. Do they all need to install SQL server on their workstations?
I am very new to this whole thing. Any and All help is greatly appreciated.
1. That is up to you. If you are in a networked environment, then you can place a copy on the server and then create a vbs file to automatically copy it for each user. Otherwise, see my article: http://www.devhut.net/2014/06/04/ms-access-package-solution-wizard-is-dead-now-what/ which may give you some possible solutions to packaging your bd.
2. Yes, they need Access to run an Access database. That said, they could simply install the free runtime version. Only the developer needs the full blown version typically.
3. SQL Server? No, none of the users need SQL Server. Beyond which I wouldn’t recommend installing a beast like SQL Server on a standard laptop/desktop PC as it would drastically impact the overall performance of the PC. The hybrid system uses SQL Azure through Office 365. So Office 365 run SQL Azure and the Hybrid connect to it.
Good morning!
I used this solution successfully for a client with Office 365/2013 last year. Hoping it works as well with Access 2016.
Thank you so much!
I have gone the “DSN-less” route with this on my own, WITHOUT having to create/convert a DSN file … and it seems to work like a charm.
1.) Create a table named “tblOBDCconnection”, with MyServer, MyDatabase, MyUserName, and MyPassword fields. Fill those fields with the values from your “SQL Server Connection Information”
2.) Create a form named “frmODBCconnection”
3.) Create a command button named “cmdRefreshLinks”
Here’s the code:
Option Compare Database
Option Explicit
Private Sub cmdRefreshLinks_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim tdf As DAO.TableDef
Dim strTable As String
‘The values that are needed to build the ODBC .Connect string
‘are stored in a (local) table called “tblODBCconnection”
‘The form that calls this code is bound to that table.
Dim strConnection As String
strConnection = “”
strConnection = strConnection & “ODBC;Driver={SQL Server Native Client 11.0};”
strConnection = strConnection & “Server=” & Me!MyServer & “;”
strConnection = strConnection & “Database=” & Me!MyDatabase & “;”
strConnection = strConnection & “Uid=” & Me!MyUserName & “;”
strConnection = strConnection & “Pwd=” & Me!MyPassword & “;”
‘These variables will be used to display a confirmation message.
Dim intLinkedCount As Integer
Dim intSuccessCount As Integer
‘Dim strTable As String
Dim Result As Boolean
Dim Msg As String
Dim CR As String
CR = vbCrLf
DoCmd.Hourglass True
On Error Resume Next
‘ Loop through all tables in database.
For Each tdf In MyDB.TableDefs
If Len(tdf.Connect) > 0 Then ‘ If the Connect property is non-empty, the Table Is linked
If UCase$(Left$(tdf.Connect, 5)) = “ODBC;” Then
intLinkedCount = intLinkedCount + 1 ‘Get a count of linked OBDC tables
strTable = tdf.Name ‘Get the linked table name
‘Debug.Print strTable
‘Debug.Print tdf.connect
End If ‘ for OBDC test
On Error Resume Next
‘Enable the Read-Write connection
tdf.Connect = strConnection
tdf.RefreshLink ‘Confirm that the .Connect property is valid
If Err.Number 0 Then ‘If RefreshLink fails…
Else
‘The re-linking of the table was successful
intSuccessCount = intSuccessCount + 1
GoTo GetNext
End If ‘(for Err 0)
End If ‘(for Len tdf)
GetNext:
Next tdf
MyDB.TableDefs.Refresh
Msg = “”
Msg = Msg & intSuccessCount & ” of ”
Msg = Msg & intLinkedCount & CR
Msg = Msg & “linked OBDC tables have been ” & CR
Msg = Msg & “successfully re-linked.”
MsgBox (Msg)
Set tdf = Nothing
Set MyDB = Nothing
DoCmd.Hourglass False
End Sub
Thank you for sharing Don! I’m sure it will help others.
It is hard to overstate how appreciative I am of this post. Although I didn’t have to go through your learning curve, I saw enough to know that it would have been very painful…
A couple of observations which may be helpful:
– The text in your sample dsn file has spaces in (presumably for clarity), but if copied and pasted, won’t work without the spaces removed (I found)
– Creating a text file and giving it a .dsn extension wasn’t recognised by Access 2016 on Windows 10. Instead, I created a dummy dsn file connecting to an access database. I then replaced the contents with the contents of your sample dsn file and it worked fine
– I observed it didn’t require a user to be logged into Office 365 to work – less licenses, but not possible to distinguish between different users?
– I observed each time I opened the front end and tried to access data for the first time, I was prompted for the SQL user name and password. Good for security, but given the format of user name and password, inevitable that users will need them both readily available (in which case, not good for security)
So, I am left wrestling with the security issues of each user needing user name and password readily available, plus no way of distinguishing between different users. I’d be interested in hearing if you or any other reader has solutions to these issues.
I’m always glad when my posts are useful!
Now to try and answer your questions:
– The text in my dsn sample was verbatim from what was created through the GUI. I did nothing to it for web purposes.
– I can’t say as I never tested any of this on Access 2016. But thank you for sharing this will be very useful to others I’m sure.
– No, the login is through the credential contained in the dsn. You are right in the sense everyone using that dsn is the same user. This is a Pro and a Con depending on the context.
– I’ve never had this happen to me. The whole point of the dsn file is to authenticate the app. This shouldn’t be happening IMHO.
Martin, can you explain further on the resolution with Access 2016? I had successfully had an odbc connection to my Office 365 database with a previous version of Access on my Windows 7 computer. But, since upgrading to a new computer with Windows 10 and Access 2016 cannot use my previously created dsn file and nothing I do seems to work.
Thank you!!! I volunteer with a small non-profit and was struggling to find a way to share our Access database.
Sorry please ignore previous two and post this one.
This is indeed a brilliant article and I took my previously desktop VBA app and with a little tweaking using lookup fields (i really don’t like them, but hey it works) I was able to hybridize my app. It really is impressive. Actually, really, really, really, impressive! With the web app I can virtually work from anywhere. That is great. No, on second thought, that sucks, but times as they are its a necessary evil.
I have also shared my hybrid app with others, by setting up a subsite managing their permissions and inviting them on Sharepoint.
Here’s my issue. I want an easier way to deploy. No issues with the web app part of the hybrid, that is easy just make the subsite deploy the .app manage permissions and good to go. Its the desktop part of the hybrid that is the problem. I have never been able to get the DSN-Less code to work. Accordingly, I have to make the DSN connection and do the install for the person using it. If anyone can get me through the DSN-Less code I would be most grateful. Note that there are also links to runtime queries on the server side. If I can get the DSN-Less to work I can use my Sagekey install and just send an install package. Any assistance would be greatly appreciated.
I will be happy to share screen shots etc. Just looking for someone who has had success and can assist.
Can the Azure data for these Apps be exposed to a sharepoint based reporting system (SSRS) using integrated mode SSRS for sharepoint and the SSRS webparts. If so maybe a Sharepoint Dashboard or Reports page for the App would be doable and a stripped down (Access Web Apps Interface) available for simple viewing and also being able to connect to the data for for Excel Web Parts using Pivot Tables for Drilling in and filtering capabilities.
I really can’t answer this question. Hopefully someone else will be able to reply.
I can not thank you enough for this article!
Microsoft thrives on leading people down dark back alleys only to abandon them in the darkest corner.
As SQL Server Drive Native Client is no longer available or recommended, I feel it important to note that Microsoft ODBC Driver 13 for SQL Server MSI is the way to go.
Also of importance; if you are going to create the new ODBC connection through Access, be sure to “Run As Administrator’ or it will not save the configuration OR allow you to connect.
Thanks again!!
My pleasure and thank you for the update in information. I’m sure it will help others reading this post.
You’re absolutely right Claire.
Microsoft seems to be bent on destroying the Access/Office 365 and Sharepoint combination, which I had previously had a lot of hope for.
In order to get (keep in my case) both Access and Sharepoint, it appears that I will have to upgrade to the Enterprise E3 plan. $25.30 CAD per month … which is $10.00 more (per user!) than Small Business Premium was.
Now I’m not even sure about that …
I was looking at it just a few days ago, and it had the Sharepoint icon … and now it doesn’t.
So now … Sharepoint is available seperately … for another additional fee:
https://products.office.com/en-ca/sharepoint/compare-sharepoint-plans
Interestingly they SAY that E3 includes Sharepoint on this page, but if you click the “Try SharePoint Online with Office 365” link at the bottom of the page it takes you here:
https://products.office.com/en-ca/business/office-365-enterprise-e3-business-software
… and the Sharepoint icon is GONE.
This situation (and now add the fact that you have to install a new ODBC driver … which you find out when nothing works anymore, BTW) is COMPLETE BULLSHIT Money$oft!
I really don’t think that we can refer to Access as a tool that allows amateurs (like myself) to design much less distribute their own relational databases!
Listen, I’ve stuck with Access since version 2.0, and have put up with having to purchase “Professional” versions at times, switching from DAO to ADO (and back again), etc. to be able to continue to “do what I do” … but this has brought me dangerously close to my breaking point!
This is NOT how you reward brand loyalty. NOT at all !!!
If I’ve got it wrong, if I’m over-reacting … please tell me.
Brilliant. Very clear. Extremely useful. Many thanks