Solved

MS Access: Opening remote MDB

Posted on 2015-02-18
24
266 Views
Last Modified: 2016-02-11
Hello,

We'd like to make available (read and write) access to a MDB file stored on a web server.

I see how I can upload a MDB file to a directory on our web server, but how do the client users connect to or open that database for viewing and saving information?
0
Comment
Question by:skbohler
  • 9
  • 7
  • 3
  • +2
24 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40617586
but how do the client users connect to or open that database for viewing and saving information?
MS Access is meant to be used as a LAN-based, file-based database.
Your web clients will not be able to create useful connections to an .mdb file from the internet.

ASP.Net and ASP Classic web pages could and still can use .mdb files as data repositories although that usage has dramatically declined with the advent of free SQL Server Express Editions.

The short answer is 'you can't connect MS Access clients to an mdb over the internet'
The long answer is build an ASP.Net application or SharePoint site.
0
 
LVL 75
ID: 40617597
Or ... look into creating an Access Web App (which will have a SQL Server back end) using Access 2013 Web Apps.

mx
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 500 total points
ID: 40617622
@mx
https://support.office.microsoft.com/en-ca/article/Create-an-Access-app-25f3ab3e-510d-44b0-accf-b976c0813e71?CorrelationId=1bb47f35-d809-4c8e-a576-1ee631207a78&ui=en-US&rs=en-CA&ad=CA
An Access app is a database that you use in a standard web browser, but which you design and modify in Access 2013. The data and database objects are stored in SQL Server or Microsoft Azure SQL Database, so can share the data within your organization using on-premises SharePoint 2013 orOffice 365 for business.

If  'client' doesn't mean 'someone in our organization' does even a web app get you any joy?

Nick67
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40617628
or simply put the data in SQL Server, located anywhere.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40617652
@Dale
You still need a frontend for the client.
Access cannot be used to provide that on anything but @mx's WAN
<envy>

@skbohler
@mx happens to have a WAN with a 100 Mbps fiber link.  What's LAN to you and me is WAN for @mx, so he can get away with having remote clients open and use .mdb over that link.  Us mortals can't manage that :(
0
 

Author Comment

by:skbohler
ID: 40617662
So, it looks like I'd need a Sharepoint 2013 installation to serve as the SQL backend. Will a hosted Sharepoint 2013 provider work?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40617670
Nick,

That is not true.  If you use Access as the front end, and SQL Server as the backend, you can put that SQL Server anywhere.  I routinely work connect an Access front-end in Virginia connected to a SQL Server backend in Houston without difficulty.  Using Access as the backend, over a WAN is not advised unless you have Joe's fiber optic backbone, but using SQL Server as the backend connected to multiple Access front-ends, located virtually anywhere, works just fine.  If you can connect via a VPN, it is smoother because you can use Windows Authentication, but you can use SQL Server authentication if you don't have the VPN.

Also, another alternative would be to use terminal server.  Each user would have their own terminal server login and the front-end would reside in that location, with an Access backend also on the server.
0
 

Author Comment

by:skbohler
ID: 40617687
Second question: Is it easy to migrate an existing desktop application to be a web app without starting over?
0
 
LVL 57
ID: 40617710
@Dale,

<<That is not true.  If you use Access as the front end, and SQL Server as the backend, you can put that SQL Server anywhere.>>

 While that's true, it's only true to a point.  Typical Access Development practices won't yield the best performance with a true client/server setup like that.

@skbohler,

   No, going to a web app in Access, or doing a ASP.net is a total re-write.

  Additionally, besides what's been mentioned already, there is also a service out there:  www.eqldata.com, that allows you to put your Access DB on the web pretty much "as is".  

  Down side is it's a service and your dependent on them.  Upside, no real re-write.

 I only wish Microsoft had done something similar...they'd be killing the market right now.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40617744
Jim,

While I agree that "typical Access development practices won't yield the best performance with a true client server setup", tweaking will be required, and to get the best performance will require significant utilization of pass-thru queries, views, and stored procedures.  But it still gives you the advantage of using Access as the front end, and a secure database as your backend.

Personally, I'd go with the terminal server option as my first choice, might look into eqldata.com but their rates are awfully high ($245/month for 5 concurrent users).  Which would still be cheaper than a rewrite to optimize SQL Server or rewrite as a web application.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40617751
@Dale
If you use Access as the front end, and SQL Server as the backend, you can put that SQL Server anywhere
I wish!
My data has been on SQL Server for seven years.
My ISP wants $5500 to bring fiber to the door and $1350/mo. for 10 Mbps DSL
Without that, I am limited to 6 Mbps down sub-1Mbps up
You can absolutely FORGET trying to run Access over that connection.
VPN'ing just adds to the latency and problems.

Within 10 minutes the frontend has crashed.
Any attempt to print a report brings an immediate crash.
IF you've got a high bandwidth, low latency WAN (looking at you @mx) it can work.
But WAN connections good enough to run Access over are the exception, not the rule.
0
 
LVL 75
ID: 40617798
@Nick
"@mx happens to have a WAN with a 100 Mbps fiber link"

It's a 1GB Fiber Optic connection
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 26

Expert Comment

by:Nick67
ID: 40617809
The last time it came up <observing self-satisfied preening, but still liking> it was 100 Mbps, I think
Is it a full 1 GB (that being 8 Gb), now
Any idea on costs?
0
 
LVL 57
ID: 40617864
No, I'm pretty sure he was in the GB range even back then....

Jim.
0
 
LVL 75
ID: 40617874
Sorry ... for the UPPER CASE typw.

Gig ah bit

It's never been 100Mbs ...

in fact, I heard recently our backbone is now 10Gb ...

And it's not a 'remote' connection. It's a network connection ... to our share drives ... which of course are 'remote' from this physical location.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40617933
I may very well be mistaken.
I had thought somewhere over the last decade 100 Mbps had been the number
It was 1 Gbps in '11 when you described it as 'the killer WAN'

Long story short, @skbohler, to give internet clients read-write facility with an MS Access application that is a file on a web server isn't going to happen.  You can
1) deploy SharePoint, authenticate the users, and then basically gut the Access application of any VBA code, and some other non-Web App acceptable features and give clients access to that
2) Create an ASP.Net site that recreates the features of the Access app as best as web-based pages can
3) Move the data to SQL Server and optimize the frontend of the Access application and distribute that to the clients -- who if they and your SQL Server have very, very good ISP connections with high bandwidth and low latency may have a good experience -- or not!
4) Deploy and license Terminal Services and have clients authenticate and access it that way.
5) You can use www.eqldata.com,

You can lead a horse to water but you can't make him drink.
You can put an mdb on a web server, but you can't get a multi-user experience as a result.
Sorry, but the UI created by Access cannot be served out by your web server.
0
 
LVL 75
ID: 40618025
"I may very well be mistaken."
You are.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40618076
It happens occasionally :)
0
 
LVL 75
ID: 40618102
Why was all of this dialog concerning our connection part of this thread in the first place ?

RE:;1) deploy SharePoint, authenticate the users, and then basically gut the Access application of any VBA code, and some other non-Web App acceptable features and give clients access to that "

OR ... get an Office 365 account with Sharepoint  and Access Services.  Depending on the complexity of the OP's UI, he may ... be able to create a usable Web  UI from the existing Access UI, or not. Further, when you create the Web App,  you get a full blown SQL back end db (hosted by Microsoft) , and then you can link the *existing* Access UI ... as is ... to the SQL back end ... which may or may not be useful.

Anyone mainly used to Access will find a MAJOR  learning curve moving to an ASP.Net (et al) paradigm.

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40618123
Your connection is of a quality that might (in your case does) permit Access to operate as a frontend on backends located far away.  @Dale has had success with that as well -- but it is rare to be able to run Access frontends through VPN WANs with any kind  of success.

And the last time I called that 'impossible' you spoke of your setup and success. :)
0
 
LVL 75
ID: 40618139
It's *not* a VPN WAN.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40618146
It's a 60 mile long dedicated intersite fiber-optic link, if memory serves.
0
 
LVL 75
ID: 40618261
Something like that. Although the configuration has changed ... some of our share drives are on NetApp. I'll hit up a network engr friend of mine in IT and get an update. Trust me ... it's super FAST and super STABLE.
0
 
LVL 57
ID: 40618356
I'm sure it's more along the lines of a traditional dedicated "point to point" rather than anything being allowed to run over the internet.    It may be on a providers MPLS network, but I doubt even that if it's running at 10GB.   Although some providers have some really solid back bones and I haven't kept up with state of the art in networks, so I may be off.

But suffice to say, it's not the norm for most companies and not what one typically has to work with.

Jim.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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