Now that we can talk about all the features in Access 2010 I think I have to place my marker immediately on what I believe is the BIG story in this release.
For the past 3 or so years I have kept hearing internally at MS about the Web story in what became Access 2010. The idea seemed to be that in order to sustain a future for Access it was necessary to “tie its can” to SharePoint’s bumper and offer itself as a somewhat functional database tool for lists and relatively small data requirements inside SharePoint. I always had trouble with that idea because I was concerned that by doing so they would marginalize the very community that Access needs to survive, namely the worldwide non-network of people using client-side Access (often secretly) to produce Departmental Solutions.
But nonetheless I jumped on the bandwagon a couple of years ago. Reading the writing on the wall I stuck my neck out and developed a serious application using Client-Side Access 2007 with SharePoint 2007 Lists as the backend. And it worked !!! In fact that application has been in production for over a year, and with a little help from Terminal Services for those users spread around the world (literally), this application has worked and worked well (despite the kludges I had to employ to get everything to work well).
But in the background of all of this was with the expectation that Access 2010 would provide a far better story. Well it’s true !! But not in the way many expected.
Welcome To The Hybrid Access Application
While a lot of the talk will revolve around the pure Web-story in Access 2010 I believe that is not the REAL story – and any effort to make that the real story would be misguided.
The conception of the Access 2010 Client – SharePoint 2010 Server integration is not only brilliant it was also very, very ballsy. I cannot imagine a bigger leap of faith than what they did. That is the REAL story of Access 2010 and Access Services.
In the end Access 2010 allows the traditional client-side Access developer to stage their entire application inside SharePoint on a Subsite created by Access Services. When an Access application is “Published” to SharePoint all objects (tables, queries, forms, reports, etc.) get stored inside the SharePoint subsite.
The user needs only a small (200+ bytes) shortcut to subsequently launch this application from SP. But wait – the first time a user launches the application, Access Services reconstitutes (they use the cute word “Re-Hydate” at MS) in a folder on their client machine C: drive in binary format (not XML). Then Access launches THAT version locally. But while the data is cached on the local machine (again in binary format) this data is directly synched to the actual tables INSIDE SHAREPOINT.
Yes – the data, although cached locally, is synched in real time – BOUND – to the actual live tables inside the SharePoint site. Any changes to data is instantly synched with the Server version. So the user gets the benefit of using a cached client-side version of the full-featured client Access application but the data is natively staored on a SharePoint server.
Next time the user launches the application, Access Services synchronizes the local cached version with the SharePoint site and only takes action on objects and data that might have been changed since the last visit to the site. Therefore in most cases subsequent launches will be close to as fast as when using a local version of the data.
At the same time the developer (or developers by the way) can make changes to the application both front and back and then Synch their changes to the Server. Subsequent visits to the application by users will then automatically drive those changes to their local cached version to keep them in synch as well. No more re-deployment hassle !!
In order to even more enhance the performance of the client app, Access Services has its own special caching mechanism ON THE SERVER that works in conjunction with the local cache while the user is in the application. It is my hope that this will allow client-side Access applications to operate across a worldwide Intranet EVEN using only a VPN connection. If that is the case (and it is something I have not been able to confirm yet), then suddenly we have the ability to deliver Access applications to anywhere across a corporate Intranet with total real-time concurrency and performance that will be more than acceptable (especially when one considers the distances involved). Getting access to Terminal Services or Citrix is a pretty easy process in most Corporations anyway if necessary.
Certainly an existing Access application cannot be Published to SharePoint Server without a few changes. Indexes, relationships, lookups, primary keys will all have to be changed but these requirements will be easily defined and the Compatability Wizard in Access is a BIG help for that. The pain is minimal and is all manageable (nothing is ever totally FREE in life anyway :-)).
One important point is that IF the application is actually backended on SQL Server, those links can be preserved and the application can live on SharePoint without even using Access Services tables on the Subsite. But having the application live and be deployed through SharePoint using Access Services will still be the way to go. In fact applications that started as Access MDB or ACCDB backends can migrate to SharePoint Tables and then up to SQL Server tables as demand requires …. all that with virtually no changes to the Front-End objects of the database. Think about that one……
In the end Access 2010 provides the existing Access developer with a path upwards to the wonderful Browser-based world without giving up any of the capabilities of the Client version of Access. In effect what we are looking at here is what I would have to call a true “Client-Server” scenario – one that truly puts the processing and the objects always in their proper place to produce the best results – how often have we really seen that?
This is a big deal (IMHO).
Dick
Dick Moffat's Spreadsheet and BI Blog 
This is great information. I am very interested in more information. How about
A demo site where we could see a Sharepoint Services app built in Access that runs in a browser?
More specifics on limitations or restrictions – how much access developers change what they typically do or their thinking.
Clarify what if any limitations there are to vba code in form events and modules?
I read (Albert Kallal I think) that in some cases where this runs locally on a desktop, Access software is still required. True? Can you elaborate.
Bob:
I think the Access Team has several demos at its Blog site listed here.
Limitations and restrictions are an interesting question. If one continues to use Client-side Access even with SharePoint tables there are few changes necessary when Publishing to SharePoint. The biggest change would be in the Tables themselves, and those changes arer manageable and definable (i.e. Relationships do not transfer and will have to be replaced with either Lookups adefined at the Table level or by Data macros firing events). I will talk about this in more detail later and this is also discussed on the Access Team Blog.
With regards to performance I have seen a major improvement over Access wth SharePoint Lists in 2007. The big question which remains to be seen is what is the defacto limits for tables size and number. As with all things Access it is always possible to design a bad database schema (which has been a source of much of the unfortunate and unfair bad feelings about Access in some circles).
I have been assured that this technology will be able to handle the data of most Access applications – considering how many small Access apps are out there. In my testing I have seen great performance but I have not been able to perform serious stress-testing yet myself. My sense is that performance will be more thsn acceptable when using the Hybrd model – and frankly that’s all I really care about at this stage – maybe in Access 2013 I’ll expect more from the Web-only story.
VBA code will work exactly as before when used in the Hybrid Client/Server scenario. If you want to do strictly Web-based applications then VBA will simply not be available. That may shock some people but it is both an absolute necessity and far less of a problem than many would think. See my earlier article here about Macros in Access 2010.
With regards to requirement for having Access installed on the client this is absolutely true if you want to use the Hybrid Client/Server scenario. I see absolutely nothing wrong with that as I like the idea of Microsoft making money on Access. At the same time there is always the availability of the freely distributable RunTime version of Access, and this was so improved in 2007 that I am sure it will be comparably easy in 2010. Having to have a Client version installed is fine with me…. If you want to use a Web-Only solution you can do so – but will find that there will be certain limitations that may not make you very happy if you are expecting the same level of functionality as Client Access with VBA. But there are definitely many ways you can take advantage of the Web-only capability of Access 2010 regardless.
Dick
Thanks.
so with the Access client/sharepoint lists, does that allow multiple access clients to share the same database via the sharepoint lists? If correct, that would seem like a great solution to where a common database is needed for geographically distributed users.
Absolutely !! That’s the whole idea. It gets better though…
If you include some Browser-only forms in the application then users with omly a browser can feed pthe same database in real-time too.
I recently did a demo whie in the Netherlands where I launched a browser form on my local machine, edited the data (that resided in Redmond), and then went over to my Terminal Services session running in Redmond and saw the changes instantly on the client version. Proved thst it works !!
This makes the idea of worldwide, or at least distribution beyond a particular LAN, a very real opportunity for Access developers.
Dick
I specialize in Microsoft Access development and lately have been getting in the habit of creating my lookups at the table design level. Not only will Access 2010 be able to use this in creating of the Web pages but my regular Access forms will also be created with the combobox and listbox controls already prepared. I suggest anyone creating a table field that will be feed by another table(“lookup talbe”), use this approach. Just click on the table field in the table design view, and click on the Lookup tab in the field properties area below. Change the display control from text to either combobox or listbox. Row Source Type can be a value list where you enter some text values separated by semicolons such as (Debit;Credit;Cash). If you make the Row Source Type a Table/Query you can select a pre-existing table or query or you many create a query right there by pressing on the elipse, the three dots on the right side. After creating your query, use the Save As and give it a name. When you close the saved query, you will be asked if you want this named query to become the Row Source property of the field, say yes and you’re done.
Bob Heifler
MyAccessProgram.com
DailyAccessTips.com
Contact at:
http://myaccessprogram.com/Contact.html
Find example files at:
http://myaccessprogram.com/Download.html
Find free video tutorials at:
http://myaccessprogram.com/Tutorial.html
Hey Bob:
I’ve been a fan of that technique for quite a while. In Access 2010 the Lookup gets new importance because it is there, when the tables are hosted on SharePoint, that you define Referential Integrity and/or Cascading Updates and Deletes as well. This replaces the Relationships Dialog while also providing Table-level One-to-Many relationships delivered through a default Combo box that gets automatically exposed in Forms or Datasheets. Handy.
Dick
Hi Dick,
I have seen demos of this and can’t wait to get my hands on the beta to start trying it out.
In Access when defining tables each field has a name and a caption property; In SharePoint you only have a Column Name for a list. Presumably the Access fieldname is used and the Caption property is ignored? I have always follwed a strict naming convention for my fieldnames, such as not using spaces; Should I ‘unlearn’ this habit and effectively start using my captions as fieldnames?
Hopefully there will also be a document published that says how do Primary Keys, Referential Integrity, etc. and the best practices when developing web-only databases.
Thanks
Peter
Peter:
Yes you can add a Caption (i.e. “LastName” intrenally can show “Last Name” on a form or datasheet or report automatically). You can also give the field a description that will show on the Sattus Bar at the bottom.
As we speak, someone is writing a White-Paper for Microsoft on converting from Traditional Access to the Web Access using Access Sevices. I’d hoped it was going to be me but oh well – you can’t win them all 🙂 …
Dick
Dick,
You might like to know that this post was mentioned on the Access team blog yesterday.
http://blogs.msdn.com/access/archive/2009/11/11/access-in-the-news.aspx
Yes I saw that when I noticed a spike in traffic here 🙂 Thanx Clint…
Dick
Hey
Really cool info, can i use it on my site?I love dmoffat.wordpress.com, what theme are you using here?
Thx
Of Course !! Go ahead.
I’m using “Neat!” as my theme apparently 🙂 Keep it simple ….
Dick
p.s. Thanx for the kind words
Hi Dick,
Can you add you your final comments about SQL Tables?
‘One important point is that IF the application is actually backended on SQL Server, those links can be preserved and the application can live on SharePoint without even using Access Services tables on the Subsite. But having the application live and be deployed through SharePoint using Access Services will still be the way to go. In fact applications that started as Access MDB or ACCDB backends can migrate to SharePoint Tables and then up to SQL Server tables as demand requires …. all that with virtually no changes to the Front-End objects of the database. Think about that one……’
Are you talking about custom BCS and external content types? Or does the publish from Access 2010 to Sharepoint create an Access Subsite that works with existing SQL Server tables another way?
Or Neither…please explain.
Thanks,
Josh
I’m talking about traditional linked tables to either external Access database OR to SQL Server databases. They will live in your Access Services subsite until you “rehydrate” them on the client in the ACCDB file that is created on your local machine. That means you don’t have to store your data in SharePoint Access Services to take advantage of the other advantages this brings to the table (deployment, maintenance, etc.)
The only thing you can’t have are traditional local Access tables. Therefore you can’t use local tables in the Frontend as Temp tables. But you can easily work around that.
Hello, DICK. .
This is Edwin from Puerto Rico, great post . . congratulations . . .
I also like the new stuff in access 2010. . .
Keep on doing the good work. .
Edwin
–
Hey Edwin… Thanx my friend.
Pingback: Tune Up Your PC » Post Topic » Access in the news
Hi Dick,
Great article.
I’m particularly interested in the external linked tables functionality since this would be a great way to create prototype web apps. However I’m struggling as Access 2010 will not let me create web forms based on ODBC linked tables. Any thoughts?
Cheers
JJ
I was initially shocked to find that Access Services Web-only apps can’t be linked table (to SQLor Oracle or any OLEDB or ODBC sources). But I am now more accepting of this apparent limitation.
In fact I look at Access 2010 as a way host client-side Access apps from SharePoit Server using Access Services that can include SOME web-only functionality that requires data stored only in Access Services tables. I can’t speak for the capability of future versions but that’s my take n 2010.
Ironically I am really excited about this even though I have yet to be convinced that Microsoft is planning on promoting this concept as opposed to promoting Web-Only Access apps as a way to create small list-type database that are limited by design.
So I’m not sure how well Access 210 wil work for your Web-Only solutions I’m afraid.
The one possible bright-spot for Web-Only Access might be iin the using of Microsoft Business Connectivity Services (BCS). Look that up on the ‘Net and you might be pleased…. I have yet t dig into this but it does strike me as having potential.
Dick
I need your help in two points:
• How I can manage the access data on Sharepoint. on other words, I want each user to be able to see specific records or rows from a table based on his/her Login.
• Is there any format for the text box control such as Password.
Thanks in advance
You described your own solution 🙂 … You need to capture the users login ( Environ(“UserName”)) for starters. Then you have to assign each record to some grouping that a user can also be matched to (like Dept Nbr). Then apply a Filter to the Records based on the Department the user is responsible for.
If the records are neing entered by the users and you want them to only see their own records then just add a field to the table(s) where you place their Login on creation of the record, then filter based on the login applied to that field.
I know this is not your specific requirement but if you think about it you should be able to match this technique to your needs.
With regards to a password on a Textbox you could lock the control, add a double-click event that asks for a password, then if the password passes you unlock the control. Then in the OnCurrent Event of the form lock the control again. Should do the trick 🙂
Dick
Hallo !!…… interessant post… I publish on this site because you apparently may be able to help me. I know, it’s not the exactly exactly area to publish but ……. I want to find a 100% free hosting service that is ok to accept Mysql. Have you a really good source or better a first-class free hosting list or something like that… … for that ??? just 1 thing: Have you links to give me for free without ads hosting ?? With wich tool have you built your blog ? See you
No idea – sorry …
Dick
Good post and thanks.
I’m running a replicated access system – one master and around 7-9 replicas , in Access 2003.
If we move up to Access 2010 will the old replica system still work or do I need to factor in some development to configure the system towards the sharepoint architecture you are pointing to ?
Replication went away in Access 2007. But if you go with this then IMHO your coded replication days are over – it’s baked in and free (from a development standpoint). But the old system will NOT work.
Dick
According to David W. Fenton’s Access Replication Wiki page http://www.dfenton.com/DFA/Replication/index.php?title=Things_not_to_believe_in_the_MS_documentation replication still works in Access 2007 (and likely 2010) if you continue to use the MDB format for the data. Of course you won’t be able to use the new data types available in ACCDBs.
Thanks to both Dick and Tony – looks like I will need to transition up and away from the replication system – to be honest it was a gludge really
Richard
Ok, Im a dinosaur still using 2003 – be patient.
How can Access 2010 through SharePoint work in an environment where a client user could work off-line and synchronize when connection is restored? Real time is not critical for this app but a daily synch would be optimal.
JB – Send me an email to dick@plogic.ca and I’ll send you off the file.
Dick
Oops – wrong question :-(.
To take an Access Services client app off-line you just need to open it when not connected to the Internet and it will automatically work off-line. Then when you connect up again it will automatically synch up. No code no hassle.
I recently had a client in India enter some data into one of my A.S. apps offline and when she connected up the next day I saw all her additions right away here in Canada ! No delay on either of our ends for the addition of 70 new records in one table. Very cool!
Dick
I have used Access 10 and published to SharePoint 10. My problem is that several groups I work with still use SharePoint 07, and do not plan to migrate soon.
Is there any way to incorporate the access services into / in conjuction with SharePoint 07, so I can develop access web apps, and publish them for SharePoint 07.
No there is no story for Access Services using SharePoint 2007.
I know that none of this works until corporations migrate to SP 2010 but in the meantine there is a site called http://www.AccessHosting.com where you can “rent” an SP2010 site and stage all you Access Services apps from there. I suggest you go there and sign up.
Dick
Hi,
I got very interested by the ability of publishing access applications on sharepoint and running web and hybrid apps with access or runtime. However, when I started enquiring about hosting databases on sharepoint servers, I was told that Microsoft recommends that access tables stored in sharepoint lists should not be longer than 20,000 rows to get optimal performance.
I thought that the point of hosting access on sharepoint servers was upscaling. If this is the case, this would represent quite a serious limitation to access development on sharepoint especially considering the cost of renting space on sharepoint servers.
I was wondering if you could comment on that and maybe suggest ways around the problem.
Thanks
Marc
Marc:
There is a SERIOUS problem in the message around Access 2010 and Access Services when it comes to the difference in the demands on a SharePoint Server of a Browser-Only Access Services database and those of a “Hybrid” Client-Server Access database.
Those warnings related to databases designed to work exclusively in the Browser, where SharePoint itself doeds all the heavy lifting when data is requested. Frankly IMHO Browser-Only Access Databases should not contain tables over 20000 records (if not less). That’s a fact.
But the REAL story in Access 2010 and Access Services is in creating (or migrating) Client based Access databases to live inside SharePoint Access Services but to function nearly totally on the client machines of the users. In this way, once the database is installed and the user accesses each table more than once (period) every query, every aggregation, every Combo-Box list is performed on the client against the local Access Store. This means lightning fast response and zero stress on SharePoint.
In a “Hybrid” application the only interaction with SharePoint once the program is downloaded to the Client machine is for CRUD operations. If you add, edit or delete single records the updating of SharePoint lasts miliseconds. When there are changes to Forms or Queries of Reports those changes flash down to the client VERY quickly, often so quickly the user doesn’t even notice it. The only place where there is a bit of a bottle-neck in this Hybrd story is when you do Batch updates like a batch deletion of hundreds of records or if you do 100’s of record updates. From my experience, batch updates are few and far between in most Access apps and by the enforcement of numeric Primary Keys and Lookup keys (Foreign keys) the need for such group updates is usually very small (like a name change).
As far as size is concerened, I JUST ran an ACCDW file for a 100,000 record table sitting on a SharePoint site (which wouldn’t let me go to it in the SharePoint UI BTW because of a 10,000 record limit) and when I opened the Table it DID take a couple of minutes to get to the last record in the table but what do I expect !!!!! And that was JUST THE FIRST TIME! Thereafter I can go in and out of the table, back to front quickly and easily because it is living in the local ache. I changed a field in the 100,000th record and there was no visible activity related to updating the SharePoint tables… but it was updated !!
I have been assured that, while 1 million records is kinda pushig it for most Access applications, there is no reason you can’t do it (especially I guesss if you were on a Corporate network Intranet) and no reason for it to be a problem for standard record management in a real-life business transactional database. To date I have NOT tried a BIG database in Production but on paper there is no obvious reason why it shouldn’t work for 100’s of thousands of records at least.
Dick
Thanks Dick, that clarifies things a lot. I see what you mean. The real interest is in the development of hybrid client server applications. That way you can have an online repository of your databases to distribute to your clients and provide easy updates and maintenance. Then they can work on the data online or offline (using the sync option), which they would not be able to do with a SQL based client server app which only works online.
One last question on the way data is shared between server and client. If I wanted to create a single application that could be used by different clients using login, passwords and some UserID fields in my data tables so that I only make the relevant data available to each client, would the client side of the application have to download the whole data tables (for all clients) or would it selectively fetch the records that correspond to the relevant UserID?
Marc
First. I wonder if that solution requires everybody who accesses that Sharepoint subsite to have Access installed. Access 2010? I just want my users with only a browser to fill out some forms – I don’t want them to have Office.
Second. And what about licensing… Is Sharepoint Standard CAL enough? Or do I need Access license? Or even Sharepoint Enterprise CAL?
Third. What are the limits of such a database? If I create a solution with only 3 tables, a couple of forms and 3 reports. How many rows will a table hold? Up to 2GB? Or there’s some row limit?
The Third no more important – I’ve just read the previous post.
Scenario: upgrade a VBA-laden 2007 Access application to 2010 Access Services. It had backend and frontend components (split database). I’ve got 50 users. Is it best to give all 50 users the backend database when installing ? If I did not do this, would the initial “synchronization” take a long time ?
Any updates made via the browser would not have any validation logic done in VBA, correct ? IOW: that logic would need to be written in that new macro language, correct ?
If you move to Access Services you just send the users the ACCDW file that will download everything the first time it is run. The initial “Synch” would take some time (minutes ?) but then the app will run at local speed. Also remember that the data is only drawn down to the local cache the first time you ask for data form a particular table – so it will take some timer the first time you open any forms linked to large tables. But again this is only once.
Any updates done in Browser forms would in fact not be subject to any VBA validation but this can be emulated with Data Macros as you say. Of course proper “Lookup” relationships and referential integrity definitions and table-level validations would apply to both.
Pingback: Access 2010 in the news - Microsoft Access - Site Root - Telligent
Access 2010 / Sharepoint / client-server
If the data provided to a user is pulled by a username filtered query (Environ(“UserName”)), does the data that does NOT meet the username filter get downloaded to the client’s machine, too?
If so, can the client user (or their IT department) get access to the non-query data?
In our app, we would like to be sure that one user’s info can’t be accessed by other users.
Thank you,
Tom
Yes – ALL data for any tables included in the query gets pulled down to the User’s Local Cache. But this is only done once except for new, changed or deleted records from then on.
But if the concern is that all users would then have theoretical access to any data in those tables then yes that could be a problem. I personally rely on queries and forms to manage who can see what but if someone REALLY wanted to go in under the hood they would definitely be abe t get to all the data if they want. There is security at the table level if you wish but there is no way to give a person access to a Table without them having access to the whole table’s at the same securuity level for all data in the table.
Because Access is a “Client-based” and not a “Server-based” technology it needs to draw everything back to the client in order to let the query filter out what you are asking for. If that’s a BIG problem then you probably should use another technology as your backend (or split your data into separate Access Services sites for each group.
Dick
“Because Access is a “Client-based” and not a “Server-based” technology it needs to draw everything back to the client in order to let the query filter out what you are asking for.”
That’s not how Access works when using an MDB/ACCDB file for data storage. Access only pulls down the entire table if the filtering or sorting fields aren’t indexed.
As to how it works on SharePoint I have no idea.
Hey Tony…. Yes that’s how it works in regular Access but when connected to SharePoint, Access needs to bring the whole table(s) involved in the query back to the Client-Side cache BEFORE it can do the query. The actual query is just performed against the local data so it all has to be there first. It is not querying against the Server Tables – just against the local ones.
Dick
Hi,
I have a Access 2003 DB.am converting it into access 2010 default format and publishing the same into sharePoint 2010.After pusblishing it into sharepoint it is working fine locally.Whenevr i opened the same site into other system am getting the Sync Failed error.
Attempt to sync with the server and reconnect all tables
changes have not been sent to the server
changes have not been downloaded from the server
Could you please suggest me how to solve this error.It’s very urgent for me.
Thanks in advance.
I have seen this if you install a version on another system other than the development machine and then do a lot of changes. It seems to sometimes get to the point where it won’t synch on the other machines. This is frustrating and makes you feel twitchy about the technology initially but I think it’s ok.
My solution is simple ….
1. Go to the machine with the problem (hopefully you can get access to it physically or remotely) and delete the “Cache” file in the “Access Applications” Folder under My Documents on their machine. This file will be named something like “<> on <>.accdb”.
2. if they have an ACCDW file on their desktop for the program just run it again – if they don’t then send them one. This will “Re-Hydrate” the most current version and data to their machine and things should go fine from there.
I am not sure but I think this might be a thing you will have to do whenever you make a lot of changes to your app. It is a nuisance to be sure and makes users tense but it might be a necessary evil on a regular basis.
Let me know how it goes.
Dick
Does anyone have a list of “rules” to follow regarding moving an existing Access app to the web via sharepoint services ? I know that VBA is not supported…and that relationships must go. For instance, how does one link tables/lists in Sharepoint ?
That’s a BIG question with a very long and complex answer. There are a lot of things to look at when considering a move to Access Services and I’m afraid I know of no single resource at this time thsat gives a succinct and detailed answer to your query here.
Personally I am a big fan of the “Hybrid” model for Access Services as anyone who visits here would know I suppose. Re-engineering an existing Access app to a completely Web-Based application would require a total rewrite and the development of a new set of skills and there are a many questions that have not been answered to my satisfaction about performance and capacity.
I have considered writing a book on this but many others have far more invested in books already and I don’t see it as an opportunity for me. As far as posting it on here I am afraid it would be huge, never adequate and I have some question about the value of all that effort would be to me (oops there I go again).
I want people to get out and use this technology but if there isn’t noise out on the Internet about Access Services and the issues then I’m not exactly sure where you’ll get ALL the answers – except through your own efforts.
Dick
I don’t see a reply to the post by: Mantvydas October 21, 2010 at 9:11 am
regarding his question:
“Second. And what about licensing… Is Sharepoint Standard CAL enough? Or do I need Access license? Or even Sharepoint Enterprise CAL?”
Also, how many seperate databases can be managed with Sharepoint Server? Does each db require a seperate license? And what about hosted Sharpoint? As an Access developer, can I utilize hosted Sharepoint?
Lastly, I plan on using runtime for the client, and am curious if I will still be able to use JRO for direct synchs via LAN.
I don’t see a reply to the post by: Mantvydas October 21, 2010 at 9:11 am
regarding his question:
“And what about licensing… Is Sharepoint Standard CAL enough? Or do I need Access license? Or even Sharepoint Enterprise CAL?”
Also, how many seperate databases can be managed with Sharepoint Server? Does each db require a seperate license? And what about hosted Sharpoint? As an Access developer, can I utilize hosted Sharepoint?
Lastly, I plan on using runtime for the client, and am curious if I will still be able to use JRO for direct synchs via LAN.
Sorry this took so long ..
1. I believe you have to have an SP Enterprise CAL … but I may be wrong on that.
2. You can manage as many as you want on your SP site. Each one lives in a separate SubSite.
3. You can run anything in RT that you can in the Full application. As far as using JRO for direct synchs via LAN I have no idea but cant see why not … Never had any inclination to use thta technology I’m afraid.
Dick
One question regarding the hybrid method. Is the periodic compact/repair task still required in the hybrid model?
It would probably be a good idea on the local cached copy. I would set it to automatically compact and repair on close like I always do anyway.
Dick
i am busy creating a web DB with Access 2010 and would like to know how to put a Login on a tab (having one tab as the tab to update the other tab) or how do i update my web DB online daily without anyone else being able to change my data? Please help!!!
Daniel:
That’s a pretty big question my friend 🙂 …… I would need some more detail on what you’re trying to accomplish before I could answer you properly. Can you give me more detail?
Dick
Interesting article… like.
I’ve created a rather extensive asset management system for our team to support assets for our sales team (laptops, blackberries, and printers). This helps us manage assets assigned to individuals, repairs, etc. and keep a history of all.
The issue and shortcomming of Access Services I have found is that you have not way to update a master data base table – you can append, but I have not found a way to update. This is an issue because people change status – they leave the company, change organization, etc. Don’t want to delete the record (can’t), but need to update the current employee info which comes from one of our master data warehouse databases. Can’t just delete the table and replace because of all the relationships already established between the various tables.
The core question, is their anyway in SharePoint Access Services 2010 to update a table (in mass/bulk, not invidual record updates) when you append new data? BTW – you may have seen this post on another blog.
The main issue I have found with Access Services in SharePoint 2010 (however much one likes the idea of it – and I did, otherwise would not have created this application) is that it seems you cannot bulk update a table as you would be able to do in Access or SQL. My employee data is dynamic, thus I must update from our datawarehouse frequently -i.e. indivual record updates will not so for an entire sales for of 5000+ people. Also, when linking to an external table, it is READ ONLY, thus you cannot build relationships from other tables to it, nor use it in a Query.
Really appreciate comments on this.
Thanks,
Tom
Tom:
Thanx for your comment here. From what you’re saying it sounds to me like you are using a strictly Web Access Services app (Yes? No ?). If that is so you’re right that you do not have the ability to do Action queries (such as Updates) as you would in Client-side Access. You can, however, use data macros to do updates but how do you automate the import of the updating data? And there is definitely a performance hit to that.
But, if you create a Client-side Action query you could run that as the Admin of the application and have Attached External tables being used as the sources of your Updates against the Access Services Tables on Sharepoint that are still used by your Browser-only users. Again the performance is not great but if you only do it daily that should be ok. I have found that other user’s will slow down during the Update but if you have a fast network and/or you’re doing the updates on a machine near the actual SP server (as opposed to thru a VPN for example) you should be fine.
As far as to why external links would be read-only I don’t understand why that would be if you’re using Client-side Access. As you know, I am a big fan of “Hybrid” Access 2010 solutions. I think it sounds like including a little Hybrid “Action” might solve your problems.
Thoughts?
Dick
Thanks Dick:
You are correct in that this is stricly web based and not the combo. Not a luxery we have at this point since the user base is very diverse and spread out – lots of users (not all at the same time, but as required for new asset request, theft reports, repairs, departures, or just inquiries on who has been issued what). Because all have different versions of Office, different capabilities, etc., hard to train (sales folks) – it is not practival – especially distributing the run time to such a large potential base and maintaining updates to it. Just not sure how to make this a “hybrid” it definetely does fill the shortcommings.
Tom
As the developer of the Access file YOU have the original ACCDB file on your machine. That means YOU can add a Client Query to your database that would allow YOU to do Actions like Bulk Updates and Deletes.
The regular users of your app, who access the database through a simple URL will STILL get only the Browser version of the database, but you will be able to do any traditional Access Action queries from your machine that will maintain the database that your Browser users will have access to.
It is also possible to distribute this capability to others that you want to be able to do Action queries by distributing the ACCDW file that you can download by right-mousing the Hyper-link in the upper left of your SharePoint Folder listing the objects in your app (“Design With Access
Modify this web database, add new fields, customize forms and reports.”) and saving the ACCDW file to your desktop.
If you make a new query it will be synched to your other Client-side users when you Synch your database.
This way you have your cake and you eat it too 🙂 ..
We are in the process of developing an Access database that will operate via SharePoint with Access services. We discovered that we cannot use a browser as the interface as there are too many rows of data that need to be actively queried. So now we are going to deploy Runtime versions of Access as front-ends which will allow for large row-set queries locally. I’m presently not sure if the Runtime version of Access will allow for offline editing. There are a lot of mixed messages out there about this capacity.
Yes – there is a serious failure to get the facts out there about Access Services (and I am constanty beeating on Microsoft for that :-))….
One of the BIGGEST missing piece of info is that the Web browser capability of Access Services is pretty much useless for anything of any significance. To me the story in Access 2010 Access Services is the Hybrid that you are moving your app to. You have to trust me that the Access 15 Browser story is going to be a BIG improvement , but that’s all I can say at this time.
I see no reason why the Runtime wouldn’t work off-line. Do you have reason to think it might not?
Also I have ahad good luck with the Off-Line story but you will have to coach you users about it. If a database goes off-line, when the user gets back to a network scenario again it will take several seconds (maybe a minute or so) before the system will try to resynch with Access Services. When this happens a Yellow bar shows up across the top of the active form and the user has to click the button to Synch up. I have found that some users just simply don’t get that when it comes up and can either ignore it or can freak out 🙂 … You HAVE to educate them on this.
Also one more trick. Once you go to the Hybrid model do NOT be surprised if every once in a while you or your user will have to go in and delete the user’s local cached version of the database (in the Access Databases Subfolder of My Documents) and allow the program to “rehydrate” the database again from the server. This would mean a loss of any data entered off-line before that, but this problem is not related to the off-line story so this is a small risk IMHO. Try to convince your users not to stay off-line for days and days… they should synch up often or they MIGHT lose their work off-line. Fear is always a good motivator…
This mostly happens if I make changes to the database and synch them up to SharePoint and the user hasn’t been in the database for a while.
You can build a batch file to perform this task if you want so the user can run it themselve. Otherwise you can use remote support software to do it yourself. This doesn’t bother me at all.. the fact that it works so well in every other way makes me forgive this little inconvenience 😉 .
Good luck and PLEASE let us know here how it goes.
Dick
I’m implementing a Sharepoint-Access solution for portfolio tracking. Does anyone have information on how many concurrent users can write to an SP list (through a linked Access Form deployed to the SP site) without a big peformance hit? Our solution could have up to ten people hitting a list concurrently. Thoughts?
I’m going to need more info about your plans I’m afraid. What exactly do you mean by “a linked Access Form” in your app? Are you saying you want to connect a Client-side Access app to regular SharePoint lists? Otherwise there is no “linking” scenario available here – either you use a Hybrid Access app where the tables are Native to the Access file and SP OR you can create a Browser-only solution that has nothing to do with Access Forms as we now them?
Can you give me more detail of what you’re trying to accomplish and how?
Thanx
Dick
Hmmm…good questions. I’m a newbie to this nomenclature. Thanks for the prompting. Here is, I hope, a better explanation.
List (and list definition) resides in SharePoint. So tables (lists) are in SP.
I open Access (local, on my desktop) and link (hope that is the right word)to the relevant SP lists. In my desktop copy of Access I design more “user friendly” data entry forms than SP can provide.
I subsequently “publish” the Access db (and its more helpful forms) to the Sharepoint site and instruct users to enter data using this SP hosted Access DB.
I’ve researched and tested issues around “publishing” the Access DB to SP and maintaining/synchronizing that connection with my desktop version. However, I’m wondering if having up to ten concurrent users hitting the SP hosted Access DB interface (form) would be a problem, peformance-wise (response time or SP list write latency or conflicts).
For the sake of this scenario, assume users will be working on different records.
I see – that’s kinda what I thought but had to be sure.
I did an application the way you are, using links to regular SharePoint Lists using a standard Access Frontend Linked table mechanism. This worked great for me and I really don’t think there is a problem with multiple users (especially as you say if they tend to work on different records). BUT – I found that unless you users are basically in the same building (or preferrably sub-net) as the SharePoint site you will find the performance on this design SUUCCKKSS !! ;-( .
To get around this I made all my Worldwide users go to a WTS session (Citrix would work too) which was hosted at least in the same city as the SP site (or at least that used a fire-hose to get data back and forth between them) and then it worked just great. But the users never understood why they had to go thru so many hoops to get to their app.
In the end (once Access 2010 shipped with Access Services) I converted the entire app to a “Published” “Hybrid” app. In this way the users work on a Local cached copy of the Frontend AND the Backend (which are actually merged into on file) and their data is automatically (and instantly) synchronized to the SP server and then made instantly available to all users – no matter where in the world they are.
Oh, sure this would be a big leap of faith on your part (and would require SharePoint 2010 with Access Services running) but if your app is a “Departmental” one and all the users are going to use the same application and link to the same tables then you should go for it !!
I can’t remember if you said you saw this:
http://channel9.msdn.com/Shows/Access/Developing-Access-2010-Hybrid-Apps-with-Dick-Moffat
In there Ryan and I explain how this all works. Give it a look if you haven’t already.
Let me know what you do and how it goes.
Dick
A few more questions-
For the above mentioned runtime client hybrid configuration, is the Access db 2GB limit still in effect?
Is there a provision to upgrade a 2007/2010 Access db to a sql server backend within Sharepoint 2010 Online? If so, I assume this means that the hybrid model of working via remote client and then syncing upon connection to Internet wouldn’t work since the Internet connection would need to be active in order to access the backend?
If necessary to upsize to Sql Server, does Sql Azure offer more, less, or about the same options/benefits as Sharepoint 2010 Online?
Dick
Can you point me/us to further articles on using Access 2010 / Access services on Sharepoint 2010 in a HYBRED application environment? YOu may recognize my name as the first commenter to your original article. I just signed up as a MS CLoud Partner and now have Office 365 and Sharepoint 2010 for one year for testing and demos to my nonprofit clients. I can successfully publish one of my apps to Access Services.
I am still trying to find out how to proceed once I have successfully published my Access 2010 app to sharepoint. Specifically
What is needed for a user to retrieve the sharepoint located Access front end?
What security do I need to set so the user can use but not modify the Access front end?
What about sync conflicts? The existed with Replication. How are they identified and resolved now?
Thanks for you article and dialogue in this thread.
Bob Alston
Dick,
Ok, I am starting to get really excited about this hybrid app configuration. Why has MS not made a bigger deal out of it?
Thanks, Dale
Good question! why? I got a need to know too.
Thank you for any comments.
So do I David … so do I. I am getting sympathy on this from the people designing and building the product but it appears the issue is in the Marketing end. IMHO MS Marketing doesn’t see Access as a “Strategic” product (in fact I was told that several years ago by an MS Manager who probably shouldn”t have said that).
Since a product like Access is not “Strategic” (like “the Cloud” is and like NT was and like Windows Server 2008 was and like Bing is), in other words there is no obvious competitor trying to move into the space that MS either occupies or that they would like to occupy, then marketing funds and energy for Access are token at best but probably actually less than that.
I have tried for so many years to change this, usually at risk to my own reputation and whatever influence I might have at MS, but to no avail.
I promise you I will never stop pushing the story of Access and Excel and how completely under-utilized and under-appreciated they are. How by letting them die on the vine like MS is doing they are not only hurting us (who really don’t matter in the big picture) but they are hurting all the businesses around the world that are either using these tools badly, dangerously and/or inefficiently and also those simply not using the technology at all because someone told them that Access is a “toy” and that Excel is ingerently dangerous because it is so flexible and open to erros. All this while offering no alternatives – because there aren’t any.
Frankly MS is being an irresponsible caretaker of this technology that they worked so hard to get an effective monopoly on.
Does that answer your question ??? 🙂
Dick
It’s just typical of Microsoft. Their new release promotion leaves a lot to be desired.
I bid on a CRM rewrite project and was proposing AC 2010. I came in at about $80k. I lost it to a dot-net/SQL Server proposal. The winning contractor still has yet to deliver the solution after 2 years and $150k in billing. Bottomline: the client didn’t have the data volume to justify SQL Server, but they definitely wanted remote internet access for clients and workers in the field.
For smaller clients, AC2010 can be a heckuva RAD webdev solution. Only downside is the need for a full AC2010 license for each user….and the limitations of the database structure when relating tables….and the limitations of the new macro language.
Interesting comment… Thanx
If each user has a full Office Pro license then you could do it all with a Hybrid model with no liitations. Alternatively the users could use the RunTime Access on their desktops for free ? Just a thought.
Also I’m not sure what you mean by limittations in relating tables? The “Lookup & Reationship” Data type should pretty much accomplish everythng you might want to do and Data Macros can take you even further. I’d look into that before you dismiss Access Services tables too quickly 🙂
Also this technology is an even better fit for large clients, because they are much more likely to have SharePoint 2010 and Access Services. It will vary from client to client, but I have some BIG clients happily using Access Services on SharePoint 2010…
Dick
Dick
Dick,
Do you have any insight as to why Access services reports are not available in Office365? When they’ll be fixed?
Thanks,
Josh
I can only speculate as to why Reporting wasn’t added to Access Services in Office 365 – but I won’t here 🙂 …
I do believe that Reporting in Access Service will NOT be added to Office 365 in the Office 2010 time-frame so plan accordingly if you plan on using Office 365. Whether it shows up when Office 15 ships is anybody’s guess. I assume it depends on the opinion of Access inside Microsoft at that time. We’ll see I guess :-).
Dick
After hearing this news, I just can’t believe Steve Balmer is still at the head of this crappy company. AC2010 has rolled-out with many bugs….SP1 fixed some and introduced more than it fixed.
I don’t think anyone could ruin Access to this extent. I have been trying to determine who the product manager is, but I have failed in that endeavor. Last I knew, it was Clint Covington.
It’s not Clint anymore…. That’s all I’ll say 😉
Yep – SP1 has been a mess and it’s hard not to ask what that means.
Dick
I am gathering from your response that the issue with lack of access services reporting in office365 may be more deliberate than technical.
Perhaps MS feels making access services too powerful on O365 will cut sales in other areas such as on-premises licensing for sharepoint enterprise, SQL and Reporting services. Way to cut your self off at the knees, MS.
I have to say I am impressed with the online story for access2010, especially considering it’s ver1 in many respects. I can only hope they don’t abandon this direction. There is no excuse for Office365 and Access teams not being full featured before GA. Not to mension 3 months later. Similar incompatibilities still remain between CRM Online and O365. Sometimes I wonder if the right hand knows what the left is doing.
Well I guess there’s always the competition: accesshosting.com
Josh
Josh:
It’s not technical at all. Personally I think it has to do with the overall MS corporate attitude toward Access (they wish it would go away and for reasons I have never understood). I think we’re lucky we got what we did in Office 365 but the lack of Reporting pretty much kills it as anything but a toy there … IMHO.
Dick
Hello Dick,
Good day! Great info and vision, I do agree with all you have discussed. We all hope here in Philippines for Microsoft to strengthen its MS ACCESS applicatio cause it will be a BIG project and will have a great impact here on philippines. Cause the Philippine National Police has developed Crime Reporting System (eBlotter) using MS ACCESS 2007. Where instead of using other front-end they do utilized their licensed MS OFFICE. It was a cost efficient project and i saw that eBlotter where they created a very detailed system for Crime Incident.
Actually tha system is great and running well and the first time i saw the eBlotter i thought that it was developed using Visual Basic but that is reality that MS ACCESS can be use on Great projects like eBlotter. When i saw the system i was so amazed and also admired the Programmer of the system which is also a Police Man. I wish that programmer will be given a well deserve recognition in uplifting the moral and standard of the Philippine Police Force. And also for Microsoft to admire the Police Programmer being clever to select MS ACCESS and promoting MS ACCESS in field of System Development.
This site can help a lot for the Philippine National Police Crime Incident eBlotter System to enhance their potencial in terms of Crime fighting. From stand alone in every Police Stations nation wide to MS ACCESS Web base for Microsoft to pursue the dream of the public to utilized MS ACCESS for System and for Crime fighting.
Long live Microsoft products and specially MS ACCESS!
Many Thanks Dick and GOD Bless!
Captain Santos:
I am speechless (for the first time in my life I might add 🙂 ….. What a great comment and I am very glad to hear the value you are getting out of Microsoft Access and your appreciation of my comments and advice here. Much appreciated and I guess is the best response is “You’re welcome”… :-).
I can only imagine the value you would be able to get if you would migrate your application to the Hybrid model of Access in 2010 with Sharepoint 2010. Of course there will be those who will scoff at the idea of using Access for such an important function but you know what ?? It gets it done, it’s cost effective and it’s good enough !!!! How can you criticize that ? The fact is that not every department of a company or, as in your case, a government department, can justify the costs of a BIG budget, complex, support and maintenance nightmare. They just need “good enough” in a time and at a cost they can justify. I have always said that that’s where Access fits, that is Access’s “sweet-spot”. And you have just showed us a great example of where that “sweet-spot” lives.
Not all our solutions can bring such a contribution to a society as yours but in all our solutions for businesses and government worldwide Access is definitely delivering value for time and money spent – and what can possibly be wrong with that?
I would very much like to see any more information you might have on your Access solution (appreciating, of course, the security aspect of what you are doing there). And if you ever need any assistance, you know where to find me.
Thanx again.
Dick Moffat
What are the capabilities/limitations regarding the use of Access 2010 database/applications integrated with SharePoint 2007? Can this be done so that the SharePoint application would operate and permit “replication” with the Access 2010 database?
The features of Access Services in Access and SharePoint 2010 are NOT available in Sharepoint 2007 . full stop.
There are ways to link to SharePoint Lists that live in Sharepoint 2007 but frankly you don’t want to go there :-).
Sorry.
Dick
Hi Dick,
Can you remind me, how do I produce that “link” to send my users to click and start their hybrid, desktop application?
When you are in the Settings Page of the Site which lists all the Objects in your Access Services Database you have to right-click the hyper-link that says “Modify this web database, add new fields, customize forms and reports. ” and then save the fie to somewhere on your machine. This is an ACCDW file and you distribute this file only to your users. Let me know if you have any problem with those instrux …
Dick
Dick,
I have a couple databases in use that require user security. I have been using front end / back end separation then requiring valid user login prior to table linking. How well will the hybrid model support user security and tracking user logins? Under the Client/Server scenario will Sharepoint manage security or do we still need to build in user login features?
Thanks,
Chris
Dick: This article made my day.
I have a large (80 Mb) Front End regulatory Access 2010 application with extensive VBA code (no macros) that guides users through complex regulatory process. It is used nationally over citrix today.
The back-end will be converted to SQL Server very soon.
This ability to publish Access 2010 to SharePoint would be revolunary! Where can I find more step-by-step information for planning?
Glad to hear it !!
BUT … I need to know a little more before I can give you FREE advice (and remember you gets what you paid for) as to whether I thibk this is a good solution for you.
If you wish to send me inf directly to dick@plogic.ca I will be glad to have a look.
1. If it’s 80 megs how many records are there in how many tables?
2. I certainly hope you’re thinking “Hybrid” for this keeping your frontends on the client (?)
3. Do you have any problem with insisting your users have Access 2010 (at easdt runtime) installed to use your app
4. How often and how extensively do you edit the data (i.e. do you only add stuff? or do you deete everything and refresh periodically? How many records do you add at a given time? What is the consequence of being DOWN for a few hours periodically for maintenance>
If I think of more I’ll let ya know.
Dick
Dick,
I found your article of hybrid apps very interesting and I have just recently started using Access 2010 instead of Access 2007 because of its capability with Sharepoint. The company I work has started using Sharepoint as a means of getting information to the field through an internet connection. Most of our company, except myself, still have Access 2007 and probably won’t be upgrading for a while if at all. Because of this problem, I have decided to build an app that will run completely in the Sharepoint web browser. This of course limits functions/macros/vba that can be used in design and I have been finding way around this by looking at Microsoft templates. I have now come to a point where I do need more advanced functionality, but only a select few users of the app need that portion of advanced functionality. Is it possible to do an in browser Access 2010 Web DB with an advanced portion being Access 2010 Client/Sharepoint Server? Have you ever run into this situation? Your advice/experience would be greatly appreciated.
Thank you,
Sam
That’s a typical scenario that I do all the time. The user’s with advanced needs use Access 2010 with the Client version (or Hybrid) running on their desktop while others interact exclusively through the Browser. Works well.
Dick
Thank you for the response. There is really only one reason I need a Client version and that is due to the lack of DateDiff() function in querying. I am using a couple Client Queries and Min(DateDiff()) to calculate accumulating hours on our construction equipment. The end result is a warning of which equipment are overdue for service. I would hate to have to have everyone need Access 2010 to just see those results. My idea was to have one Client app that runs the queries and publishes the results to a temporary Web table that gets wiped clean and renewed everytime the Client app runs the queries. I haven’t tried it yet, but have you tried such an application? I know its not good practice to store calculations directly, but its only temporary and not very many in the overall scheme.
Sam,
Publishing a Access Web db makes a sharepoint list for each table. There are some functions for calculated columns that you can add to a sharepoint list which are not available in the Access table designer. One such function is DateDif(). After publishing to Sharepoint, you can go to the list settings page for the coresponding table and add a calculated column in sharepoint UI like so:
=DATEDIF(Field1,Field2,”D”)
Sharepoint calculated columns are more limiting than vba of course and this function is no exception. For example it will not return a negative result if the Field1 is later than Field2 – instead you get #NUM.
This field will work in the Access client, but you will no longer be able to edit the formula in Access – only in sharepoint UI. There’s prolly a good reason it’s not available in Access, but I haven’t found a problem with it…yet.
HTH,
Josh
Josh,
Thanks for the advice. I have not had a chance to look into that because our Sharepoint site has been down for a revamp. I will give that a go because, as most of the more experienced Access users know, you can not use a Make A Table Query with calculated fields. I unfortunately am new to access and found this out last night.
Hi,
I have a problem publishing the Access database 2010 to sharepoint 2010.. after we run the compatibility check and publish the database, i received this error “An error occurred while initializing the Access Services database”. I badly needed this one to publish.. Please advise.
Thanks,
Renz
Does this happen in the Publishing Process? In other words does it “seem” to Publish or does it fail before finishing?
Not sure if I have an help you on this but one more question that comes to mind is “How recently have you synched?” In other words to you do a little work and then synch and then do some more and then synch or did you do a whole lotta work and then finally synch?
Dick
Great read. We’ll be migrating to 2010 soon – I currently manage 47 SEPARATE Access dbs across the country as we had no way to solidly ensure connectivity and stability. I compile quarterly into a single database from which we update the web, etc. Data is often out of sync and must be manually corrected. Looks like I have a solution now!
Thanx for your comments.
This article was however written quite a while ago and some things have changed since that time. Although I am just as big a fan of the hybrid model in Access 2010, I suggest you wait until more information comes out about Access in Office 15 before doing anything.
This is all I can say at this time … Sorry.
Dick
Might it have something to do with the fact that there apears to be a new database format called ‘Access 15 db’ which has a local path and a URL as seen here:
http://www.office-2012.com/microsoft-office-2012-15-0-2703-1000-first-look-with-full-screenshots/#more-17
The correct answer is ‘i can neither confirm nor deny’, but we’ll take ‘no comment’ as a yes. 🙂
I want very much to help people here but I have already said too much. I guess eventually it’ll all be revealed. My advice is just don’t rush decisions right now 😉 … Wait and see what happens in 15.
Dick
This is why I don’t blog anymore … It’s too dangerous.
Dick
Very intersting read. I work with MS Access (now 2010) daily in my work. What about MS Access form timer events (events that execute VBA code at set time intervals)? Some forms trigger a timer event every 10 seconds; others every 40 seconds. Do these work in SharePoint 2010 using Access Services and/or what complications might this bring to that environment? I adminster a production monitor system built in MS Access that utilizes timer events that are coded to display production status “real time”. Data tables are a combination of linked Excel workbooks and Access tables in a split DB configuration (front-end / back-end) that get updated by various data capture points located within the facility. It has worked great so far, but we have sites in two other states that want to use the system, but be able to share data from multiple sites accross the WAN… thanks..
Hey T. Locke…
There are 2 answers to your question:
1. Yes if you use a “Hybrid” 2010 Access Services scenario where your forms, reports and code all stay in a local Access file but your “backend” moves to SharePoint I see no reason why your Timer code should not work.. but
2. I would strongly suggest that you avoid converting your solutions to Access Services 2010 because, frankly, Microsoft has depreciated that technology in 2013 to such an extent that you can’t even create new Access Services 2010 solutions in it (you can however open and manage ones you already created in Access 2010). But you should read that inability to create new “Hybrid” solutions using the Access Services 2010 model as an indication that this technology is finished and is only provided in 2013 to be compatible with 2010 apps and only in this version. DO NOT build new solutions using the Access Services 2010 model.
I would suggest you consider moving your backends to SQL Server or to SQL Azure, if that is available to you, and if you have a need for a distributed database and your record count is going to keep increasing in the future. Otherwise I’d leave well enough alone :-).
Dick
I work with about 15 multiuser Access databases (some .mdb, others .accdb) that reside on our network file server. Everything was working ok until the file server were moved to another state. We have tried to use Citrix to open the databases, but we have been confronting several technical difficulties and we are looking for options.
I don’t think that is our scenario, the hybrid application can be an option since the data is not on our site.
I would like to confirm if the 2010 Access Web applications can generate complex reports and work with several forms and queries that are generated based on user input.
If not, I would appreciate your insight on which other options are being used to develope this kind of applications on a web based environment.
Thanks you very much for any help you can provide!
It’s an remarkable piece of writing for all the web people; they will take benefit from it I am sure.
Your article offers established necessary to
me. It’s extremely useful and you really are naturally really educated in this field.
You get opened up my personal eyes to different views on this specific matter together with intriguing and
solid content.
I have been using a hydrated access 2010 front end with data in Sharepoint via Office 365. Installed and light usage since March. Once I signed in one time and selected the option to save my credentials, it works fine and opens without issue. This process has been used by my clients, following step by step screen print and instructions with success on several PCs by several different installers.
My client recently tried to set up the accdw on a new PC that did not previously have it installed. Access 2010. Windows XP. failed.
I tried also on a windows 7 virtual machine. I sign in as normal and when entering my email, which is a hotmail email, i get transferred to the “Microsoft Account” signin. After entering the password carefully, I get this error:
“Microsoft Access could not open the web application from ….. Please verify that the server is available.”
I tried this twice and even did screen prints as I went.
Ultimately I found a workaround which was to use the address of the web application and enter it in a browser. Then I signed on and said to keep me signed on. Then, when I tried my normal process again, I got access to the web application. (I think i didn’t even have to enter the password).
Anyone know of a real fix?
bob
Write more, thats all I have to say. Literally, it seems as though you relied
on the video to make your point. You obviously know what youre talking about,
why throw away your intelligence on just posting videos to your
site when you could be giving us something informative to read?
Hi … Thanx for your support.
Are you an active Excel user btw? Have you looked at Excel 2013 and its integration of PowerPivot? Thoughts?
I might start blogging again but I have become VERY discouraged by the directions Microsoft has taken in Access and in their complete inability to promote all the great new features of Excel 2013. I am VERY discouraged lately and have just worked as much as I can, knowing that the end is near for reasons I cannot in any way understand… 😦
Dick
Hi, I do think this is an excellent website. I stumbledupon it 😉
I will return yet again since i have book marked it. Money and freedom is
the best way to change, may you be rich and continue to help others.
Thanx for your thoughts. I’d love to help people more but without Microsoft’s support of the Access and Excel dev market it would be like pissing into the wind. Why waste my time?
I have decided to try to make as much money personally out of this market before Microsoft completely destroys Office as a serious developer tool.
Dick
Dick – All of these problems result from MSFT largely hiring cheap programmers from China and India. The msdn support forums are just loaded with them…and I’m sure they are in the dev teams as well.
Balmer should be fired…he’s really done nothing of significance there. Access and Excel VBA just have so much power and promise, but all of the bugs and issues make the whole thing so unreliable.
Not sure if that’s the problem actually. To me the problem appears to be right at the top with an organization that does not accommodate independent thought anymore. There is an obsession with Google and “The Cloud” and now “Devices” (read Smartphones) and there is no bandwidth left for the mundane realities of business software. That’s just simply not cool ya know ?
I only hope they wake up before it’s too late (if it isn’t already).
Dick
Admiring the time and energy you put into your blog and in depth
information you present. It’s awesome to come across a blog every once in a while that isn’t the same unwanted rehashed
information. Great read! I’ve saved your site and I’m including your RSS feeds to my Google account.
Yeah, this is what I tried to do last year. The tables must be converted to SharePoint tables for it to work … and that’s what didn’t work with my queries. I do, though, intend to do some testing when I get some time to see if the shared user “work around” revision I made early this year (that I was testing at your house) somehow sidesteps the shared table issues. It is a long shot, but worth a try. The theory is that maybe the “conflicted”, dual-used fields may be taken out of the picture since the data is now hidden in and used from the main menu page to control the query selection parameters.
________________________________
WordPress
Working “At The Speed of Government” so we are just moving to Access 2010 now. I saw your video on the Access Hybrid and this looks great. One question I have is can I share hybrid tables on sharepoint with another access database program. ie, is it possible to get the same performance by using linked tables as with the hybrid tables?
Right now we have our main ms access program with our data tables, but also have a different ms access program for updating our main data set. we don’t want to combine them since the data we are using to update the main tables is very large, we are just taking a subset. So we are looking at ways of linking to the MS access web database on sharepoint in a way that takes advantage of the hybrid caching.
Ken:
Not sure if Hybrid Access as I described in that posting so long ago is going to work for you.
Actually even though there is a way to link to Access 2010 Access Services tables I don’t suggest it because frankly Microsoft has dropped that technology from Access 2013 and it will not be available going forward – they simply changed their minds 😦 …
If I was you I’d stay away from Hybrid Access apps..
Dick
MS may have dropped it, but I am told that if you create it in A2010, A2013 can run in the hybred mode. It is working for me.
I personally think the hybred app with ability to work offline on a laptop then automagically replicate to the sharepoint host fits a number of needs and so far is working well for me.
Bob
Hey Bob ….
True but I wouldn’t invest any effort in new solutions if I were you is all :-)..
I LOVE the Access 2010 Access Services tech and especially the offline “story”. I have several major solutions using it that I will have to migrate to SQL Server one day soon.. But it doesn’t make sense anymore to use it for new biz if MS is dropping it.
Sucks but true IMHO ..
Dick
I get less concerned about apps when MS drops support. I still have an A2003 replication app working with three server clusters each in a separate physical site and about 40-50 laptop users using traditional old but complex Access replication. Has been working since 2006 and I have to support it every 2-3 years.
bob
Yeah I agree when it’s just plain old Access or Excel but when SharePOint is involved like this who knows what they’re going to think is important in the future. You will need access to an SP site with Access Services 2010 active and I have my doubts that most IT departments will be interested in doing that in SP 2013 and i frankly believe it will just disappear in the next verion of SP.
It ain’t so simple anymore 😦
Dick
Thanks for your replies. I think I will be moving away from Access and Sharepoint in the future. However, if you have a reference showing how to ” link to Access 2010 Access Services tables ” I would appreciate it for my short term solution.
The simplest way I have found to link to Access Services tables is to use “External Data” “Access” and “Import| the Tables from an existing Access Services Table into a blank database. Do NOT try to “Link”, just “Import” and it seems to create a link for you. This is not published anywhere I know of so I don;t think it’s “official’. But that seems to make a fully functional link… I haven’t used this in Production though so I can’t confirm that it will be reliable and fully capable but I can’t see why not..
Dick
always i used to read smaller articles that as well clear
their motive, and that is also happening with this piece of writing
which I am reading now.
I would be careful committing to this particular Access feature because Microsoft has discontinued it…
Good post and thanks…Check here for free demo version and for free evaluation of Access Database Recovery software…