Solved

Microsoft Access and the Web

Posted on 2013-06-07
24
514 Views
Last Modified: 2013-06-18
Hey Experts, I could use help and references regarding Microsoft Access and the web for Access 2010 and beyond.

We're looking to have a project where the web would be used to gather data and an Access App would be used for processing

What's the best way to set my back end database up? Should it be SQL?

Also, it seems that the power of Access Web database is very limited because it does not allow the use of VBA Code, just macros. How do you deal with that.

Any help to books, videos, training videos would be greatly, greatly appreciated.

E
0
Comment
Question by:eantar
  • 9
  • 8
  • 5
  • +1
24 Comments
 
LVL 84
Comment Utility
Without knowing more about your application and needs it's hard to say, but in general Access (2010, 2013 or otherwise) has difficulty working with the web. 2010 introduced "web databases", and 2013 improved on those, but as you noted they are somewhat limited in scope, and cannot really interact with other web systems out there.

What do you mean by "the web would be used to gather data"? Access can connect to remote datasources (like a SQL Server, for example), but that server would need to be setup to allow remote connections, and you'd need the necessary connection/security information to do that.

IMO, if you're dealing with web-based stuff you're much better off moving to .NET, PHP, Java or some other web-based language.
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Thanks for your comments. I'm not familiar with .NET, PHP, Java or other web based languages.

I was wondering if I could create a web database with a connection to a specific SQL backend to use for simple data collection through the web. And then use an Access Application to link to that same database for other processing.

The data collection portion doesn't require much. But there is some coding that will be needed to aggregate and manipulate data once it is collected.

Am I thinking in the wrong direction? Are there resources to help me?

Thanks.
E
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Actually, A2013 has much less difficulty, and is going to be your best shot to date.

Just for the record, in A2013 ... the nomenclature is 'Web Apps'. In A2010, it was 'Web Databases'

"I was wondering if I could create a web database with a connection to a specific SQL backend to use for simple data collection through the web"

You *can* do that with A2013 Web Apps.  AND ... you can directly link - from the Desktop side of A2013 - the the SQL tables created on the Web App side of A2013 - HUGE!

Pre-Order Jeff Conrad's about to be released (7-17) new book - MUST HAVE:

Click  Here
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Am I thinking in the wrong direction? Are there resources to help me?>>

 Your thinking in the right direction.  As long as the web data collection is fairly staright forward and simple, you can do this easily enough.

  Just be aware though that Web databases are hosted through SharePoint and Access Services, the latter of which requires an enterprise license if your going to run your own server.  If your not, then you'll need an Office 365 subscription.

 But you can do a "hybrid" type app like your describing.  In fact this is what Microsoft is promoting the idea of because web apps at this time are basically limited to CRUD (Create, Read, Update, and Delete) operations, with a limited amount of control types that can be used.

Functionally they give you what you need to do basic CRUD, but it doesn't approach what you can do in a desktop app by any means (or in ASP.Net, PHP, etc).

Suggest you take a look at this:

Access 2013 -- How to Create an App in Just About 60 Seconds
http://www.youtube.com/watch?v=40APedbHaG8

What's new for Access 2013 developers
http://msdn.microsoft.com/en-us/library/office/jj250134.aspx

  Just to give you a feel for what it can do.

Jim.
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Thank you.

I have to start development now. I don't have A2013. Can I not still do what's being asked? Develop a web database in A2010 for data collection linked to an Access App for processing?

I appreciate the reference to the Jeff's book, but I need to move quickly on this.

I appreciate these responses. Please keep them coming as I'm entering new waters with this stuff.

Thanks again.
E
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
THANK YOU JIM!

This was the answer I was hoping for. Big contract at stake.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
"Web databases" ...  Web Apps :-)

There are a load of KBs Here   on A2013 web apps - this is a good place to start.

"Just be aware though that Web databases are hosted through SharePoint and Access Services, "

To clarify - for A2013 ... your db back end will be in a full blown SQL Server database  - hosted and managed by Microsoft  (unless you host it yourself) ...

My Suggestion:  FORGET A2010 and get A2013. You are basically wasting your time in A2010 - compared to what can now be done in A2013 - on a relative scale. You will be severely limited by Sharepoint Lists as your back end in A2010.
0
 
LVL 84
Comment Utility
I was wondering if I could create a web database with a connection to a specific SQL backend to use for simple data collection through the web
Perhaps I misunderstand, but if you have connection information for the web-based SQL Servers, why not just connect to them directly from your desktop-based Access app and not worry with web-based apps? If your intent is to get the data into your local Access database, then this would be the quickest way.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"I appreciate the reference to the Jeff's book, but I need to move quickly on this."
As  I noted, there are several KBs on the Access blog link to get you up to speed ... I've read them all several times.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<<My Suggestion:  FORGET A2010 and get A2013. You are basically wasting your time in A2010 - compared to what can now be done in A2013 - on a relative scale. You will be severely limited by Sharepoint Lists as your back end in A2010. >>

  I couldn't agree with this more....don't use A2010.  As MX has pointed out, in 2007/2010, it was "web database" and in 2013 it's now "Web Apps".

 The reason for that is for 2013 it's a different animal then in 2007/2010 for getting a database on the web.   With the former, all the data was kept in SharePoint lists, which also as MX pointed out, is limiting.

  SharePoint lists start to peter out at about 5,000 records; for a database of any type, that's pretty limiting.  With A2013, it's now kept in Azure (even though it still runs through SharePoint).

  Think A2013 at the very least or use something else.   2013 was a total reboot of getting access DB's to the web.  You won't see any improvements or changes in the 2007/2010 way of doing things, which is now a thing of the past.

Jim.
0
 
LVL 5

Author Closing Comment

by:eantar
Comment Utility
You guys are awesome! So the way I understand it is:
Get A2013 and get a subscription to Office 356. My client has both SharePoint and Office 365.

I really, really appreciate this info.

E
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Rock N Roll ... and get  Jeff's book when released, noting that Jeff is not happy with the delayed release - beyond his control.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Get A2013 and get a subscription to Office 356. My client has both SharePoint and Office 365.>>

 Yes.  I believe there are two different subscriptions (Office 365 comes in a number of flavors) that will give you SharePoint and Access Services, which allow you to publish a Access Web App.

  Also, might be remiss in not mentioning this:

www.eqldata.com

  It also is a subscription based service, but it's different in that you take a desktop db and publish it to the web through their servers.  Via a plug-in, it sync's data between their servers and yours.   Those that have tried it have been pretty happy with it.

 Don't know if that might be a better fit for you or not.

Jim.
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Okay. So one last question... I think.

I have MSDE for SQL Server 2005 on my local machine. Can I develop the web app for that and then, once I publish the app on Office 365, reconnect to the SQL Server that has the actual data.

I'm trying to compare it to what I do when I develop desktop apps for a client and then install, and re-link to the client's actual database.

Thanx in advance.
E
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I have MSDE for SQL Server 2005 on my local machine. Can I develop the web app for that and then, once I publish the app on Office 365, reconnect to the SQL Server that has the actual data.>>

  No.  On premise development would require SharePoint with an Enterprise License (so you would have Access Services) and SQL Server 2012.

  Do not think of developing in terms of that you have done with Access in the past with the traditional desktop applications.

  This is not like working with a ODBC to SQL connection or ADP, it's entirely different.

 If you don't have these tools on premise, the cheapest way to get them is with an Office 365 subscription.   Small Business or Enterprise E1 gets you them (double check that - they've been shuffling them around a bit and I haven't looked in a while).

  Note that you can connect a desktop DB to an Azure database on the web, so with your Web database in Office 365, you will be able to access the same data.

  But a Web App in Office 2013 only works with Sharepoint.

   MX knows a lot more then I do on this...at present, I'm not all that excited about it and have only played with it here and there up until now.

    I find the current setup being like Access 1.1, but for the web; it works, but there's not a ton you can do with it.   Maybe I lack the vision and it will all take off, but they (Microsoft) need to add quite a bit more to it.   That's mostly because of where I come from; development of rich interfaces and apps for mostly small and medium sized businesses.  I typically don't have a lot of remote users and if I do, Citrix and Terminal Services has always handled them nicely, and I can develop one application for all users that works all in the same way.

   Don't get me wrong though; what they have come up with is great, it's just that right now it is very limited.   It's like Henry Ford saying "Choose any color you want, as long as it's black", so I find it hard to get excited about it.   Especially when they have left long standing bugs/problems go in the existing product.

Jim.
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Thank you Jim. You've been immensely helpful.

I'm like you. Most of my shared apps for remote users have come through Citrix and Remote Desktop. I'm an independent developer who develops from my home office and then installs the latest version remotely, which is then re-linked to the current live database.

I think from what you're saying I can still do this. With A2013, I will still be able to develop the Web app and connect to the Azure db as long as I have a subscription to O365. I'm in the process of doing both.

What I'm still not sure about is: I will be creating the data structures, testing them and modifying them till we go live. I assume I'll still be able to do this by creating them locally with MSDE, and then, when ready, attach the SQL database to the Azure db, yes? I know I'm being given administrative rights on O365. I think that's what I'll need.

Anyway. Thanks again. And Mx, if you have any input, please. The more info, the better.

Thanks again for all your help.

Eddie
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<What I'm still not sure about is: I will be creating the data structures, testing them and modifying them till we go live. I assume I'll still be able to do this by creating them locally with MSDE, and then, when ready, attach the SQL database to the Azure db, yes? I know I'm being given administrative rights on O365. I think that's what I'll need.>>

  Not with the web app itself no.  When you work with Web app and Office 365, you'll be on-line and talking to the Azure DB.

  You could do that through a desktop database though, but I would not.  Until Azure is updated, anything you'd do with the web app would be out of date.

  It really would be simplest to either:

1. Work through the Web app and Office 365.

or

2. Do data changes with a desktop DB attached to the Azure DB on the web.

 and forget about doing anything local.

  It would have been better almost if they had called Access 2013 web apps something else as their "Access" in name only.   Access Web apps and Access Desktop apps are two totally different things.

  If you haven't already, I would go through this:

http://msdn.microsoft.com/en-us/library/office/fp179695.aspx

  as throughly as possible before jumping in and especially this:

Access 2013 custom web app reference
http://msdn.microsoft.com/en-us/library/office/dn125265.aspx

Jim.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I will try to comment a bit more later, but Jim has pretty much wrapped it up.
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Hey guys,

Just installed Office 2013. One more question about development.

so... does one always do development and testing on live data? This is so different that what I'm use to. And can I actually create the database in 0365?

Sorry, I'm really new to this.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"does one always do development and testing on live data?"
For me, it would depend on the Web App in question.

This issue is going to be a big deal breaker for a LOT of developers, as pointed out numerous times at the MVP Summit back in Feb.  Microsoft has GOT to come up with a solution to this.  

As it is at the moment, if you make changes to your UI or backend of a Web App ... it's LIVE baby. Here and now.

This is not going to be a problem for me ... for apps I am planning on building initially. BUT ... as noted above, it's definitely and issue for a lot of developers.

mx
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Thanks Mx, but why is this NOT  a problem for you? Just curious.

E
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Because the initial apps I will be building will low traffic and not for any clients. So, I can do ongoing dev and update w/o really impacting anything.

Down the road of course, this will likely become problematic. Hopefully by then, Microsoft will have a solution.  Believe me ... this was made LOUD and CLEAR at the Summit by some heavyweight MVPs (that have a lot of influence and connections with Microsoft).

Turns out there is a 2nd Summit this year in Nov - so, we will see what has transgressed.

mx
0
 
LVL 5

Author Comment

by:eantar
Comment Utility
Great! I'll take this all in and pass this on to my client.

Wonderful info. I still think I'll be ahead of the game if I start "playing" with A2013 and O365.

So appreciative for all the help.

Eddie
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You are welcome.
GET Jeff's book ... pre order now on Amazon. I'm confident Jeff's book is going to put a  LOT of things together. Once I get the book, then I am really going to get serious :-)

mx
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now