?
Solved

Putting my .mdb online

Posted on 2006-11-30
9
Medium Priority
?
886 Views
Last Modified: 2013-12-05
OK I've read a few solutions, but I'm not sure what I need.  My company uses Access to keep up with mostly everything.  Products, orders, shipments, invoicing, you name it.  However, we are spread out geographically and we don't have a windows server at my office.

So, I'm looking into putting Access online in some fashion so that we can all access the same data, but use Access itself as a frontend.  I'm interested in knowing the best way to do this.  Can I get a shared windows hosting plan with someone like ixwebhosting.com and configure the DSN to connect the online version to the not-online version?  Or do I have to convert my database to SQL Server, upload it, and then somehow connect access to the online version?

The ultimate goal is to have just the DATA online where we can all access it.  But I'm not knowledgable enough in PHP, ASP, or other languages to build an online application, so it seems that using Access as sort of a front-end would be preferrable. But all the talk of OLE DB, ADO, JET, SQL SERVER, ADP, etc...this is really confusing to me.  I need some help understanding my options here and finding a solution.  Web hosts are clueless as to whether or not they even support Access as a frontend as I've noted.

Please help?
0
Comment
Question by:rowejd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 12

Author Comment

by:rowejd
ID: 18045277
Also, as I forgot to mention, is this the best method or should I start from scratch with something like PHP and MySQL and just force myself to learn?
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 1050 total points
ID: 18045413
Access is NOT a good choice for an On-Line Database.  

You shouold consider migrating your Access DATA to SQL Server (a copy of SQL Server Express Edition can be downloaded for FREE from Microsoft,and should be sufficient for your company, for the time being).

Your Databases should all be configured as FrontEnd-BackEnd- the BackEnd (BE) database (whether Access or SQL Server) holds the REAL data tables, and nothing else.  The FrontEnd MDB holds all of the Forms, Reports, Queries, and Modules, and LINKS to the tables in the BE.  If you choose to use SQL Server for the BE, there is a tool in Access to migrate all of the Data Tables to the BE (Tools/DataBase Utilities/Upsizing Wizard)

You can then consider keeping the in-house Access application, connected to the SQL Server BE, and providing a Web version of the application (connecting to the same SQL Server database) for those users who are geographically distributed.  This type of setup is very common.  It is not, however, a simple matter of making the desktop Access FE available on the web.  A Web-based application is quite differnt from a Desktop application.

The design and development of a Web-based application is doable, even by someone whose experience is primarily in the desktop arena.  How are you programming skills?  You may want to investigate tools such as ASP.NET from Microsoft, or Dreamweaver, as the vehicle with which to develop the Web application.

AW

0
 
LVL 12

Author Comment

by:rowejd
ID: 18045501
Arthur I appreciate your helpful comments.  A question though... when you said...

"You can then consider keeping the in-house Access application, connected to the SQL Server BE, and providing a Web version of the application (connecting to the same SQL Server database) for those users who are geographically distributed.  This type of setup is very common.  It is not, however, a simple matter of making the desktop Access FE available on the web.  A Web-based application is quite differnt from a Desktop application."

...I think I understand you to say I can use Access to do its thing on my desktop, but it will update the DATA online so that those who use the web application will have updated data from my desktop.  Why, then, could I not have these 3-4 other people use their own version of Access on their desktops and do the same thing you're suggesting that I do from mine?  Is it not possible to have them do this simultaneously?

I do realize that the best approach is probably going to be a web application, but I don't really have enough skills now to create one.  I will in the future as I'm taking classes when I can online.  However - for an "intermediate" solution I would love for 3-5 users to be able to use Access as the frontend simultaneously.

Is this do-able?  Or do I need to go ahead and commit to learnign PHP/MySql or ASP(.net)/SqlServer and forget having an "intermediate" solution?

~thanks!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Accepted Solution

by:
tncbbthositg earned 450 total points
ID: 18047862
rowejd, I think that's what Arthur was saying.  Use the upsize wizard to "prepare" your access application for use with the SQL Server backend.  Then, everybody will use the same access application and that application will link to the backend server.  Your geographically distributed users can still access the sql server over the internet, but that's a pretty huge security risk.

TNC
0
 
LVL 4

Expert Comment

by:dovidf
ID: 18048102
How many users do you have and how big is your MDB? How are you currently hosting your data if you don't have a Windows server.

Strictly speaking you don't have to go to the web. You can have your Access front end connect to your backend database over a wan and a VPN. However, new release deployment would be an issue for the front end code.

Access itself provides web capabilities for its database.

I agree that it would be better to switch from Access to some other more robust relational database but it would be job to convert all the SQL. The queries would probably also have to be redesigned to have most of the work done in the backend on the database server to minimize network traffic.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18049804
" but it will update the DATA online so that those who use the web application will have updated data from my desktop"

Yes, by having both applications 'talk' to the same SQL Server instance.

tncbbthositg >>
"Your geographically distributed users can still access the sql server over the internet, but that's a pretty huge security risk."

What I was saying was to have both the Access FE for the in-house users and the ASP or ASP.NET or Dreamweaver Web pages talk to the same SQL Server Database.  Not to have the Web users access SQL Server directly.  There is no security issue in that case.


AW
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18049857
I agree Arthur.  You're absolutely right about that.  The asker said, "the best approach is probably going to be a web application, but I don't really have enough skills now to create one."  I was saying it's possible, but then again, it's also possible to kick yourself in the face, but that doesn't make it a good idea.  The best bet would be to write a web app and have everybody use it.  I see no reason to use an access front end internally, but a web app externally if teh web-app is written well enough.

TNC
0
 
LVL 4

Expert Comment

by:dovidf
ID: 18050092
Security can be accomplished by using a VPN with strong password protection. Although a web app is probably the right way to go, it would require much more of a rewrite and apparently a learning curve in this case.
0
 
LVL 12

Author Comment

by:rowejd
ID: 18053540
Thanks for the help.  Arthur -I meant to put yours as the accepted answer and the other as assisted...but I messed up and can't undo.  I did distribute the points correctly, though, so thanks again!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

741 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