Building an Application that uses Access Database

EIREHotspur

Ok I have a program that takes it's info from an Access database.

I want clients to be able to edit an Access database online and create their own version of the application.

I want to be able to manage this in the most efficient way so what would anyone suggest?

I had thought of creating a web form with MySQL and letting people enter their details and then copying and pasting or exporting the database and then inporting into each Access database to create their version and email it to them but thats adding another few steps.

I had also thought of putting a downloadable Excel (nearly everyone would have a copy of that) on my website and have people edit that, email it to me so I could copy and paste that into the Access database but again thats adding another few steps.

Looked at Sharepoint and maybe thats the way to go.

Seth Brundle

You won't really be able to provide an editable version of Access that can be downloaded and re-uploaded without issues with the underlying data (although you might be able to use use replication services).
The simplest and less labour intensive method may bt to use various web forms to insert & edit data.

Is it essential that each user has their own local copy of the system?

Will each user have a continuous connection to the internet when using the system? If so, you may be able to link the access database to the remote MS SQL / MySQL database (via linked tables) - but if users are not going to be connected to the web all the time then it brings us back to a messy area.

Sharepoint may meet your needs but I don't know what exactly these are so I can't really comment on them. However, not all systems that work out fine in MS Access are suitable for migrating over to SP (as I discovered recently)

jmcc

EIREHotspur wrote: »

Ok I have a program that takes it's info from an Access database.

I want clients to be able to edit an Access database online and create their own version of the application.

Not a good idea. Theoretically you would have to have a db for each user. That's a clusterf*ck of a db admin problem and it doesn't even begin to address potentially shared data.

I had thought of creating a web form with MySQL and letting people enter their details and then copying and pasting or exporting the database and then inporting into each Access database to create their version and email it to them but thats adding another few steps.

Coallate and publish might work but again you are creating a lot of administrative overhead.

I had also thought of putting a downloadable Excel (nearly everyone would have a copy of that) on my website and have people edit that, email it to me so I could copy and paste that into the Access database but again thats adding another few steps.

If the data is simple enough to be Excel based then look at Google Documents:
http://www.google.com/google-d-s/spreadsheets/

Looked at Sharepoint and maybe thats the way to go.

Possibly. The worst case would be to mix different database types (Access and MySQL are not completely similar.) and it would be better to maintain a single system.

Regards...jmcc

BostonB

+1 on the advice given so far. You don't want to do this. But you don't give enough information about the application, or the data in order to give a informed answer.

EIREHotspur wrote: »

Ok I have a program that takes it's info from an Access database.

I want clients to be able to edit an Access database online and create their own version of the application.

I want to be able to manage this in the most efficient way so what would anyone suggest?...

Access doesn't work for any online solutions. You need to dump it and your program and create an online application and database. Unless of course your data is so simple you can use a shared documents. In which case you probably don't need access either.

Creating replication between online and offline databases, applications isn't something to get into if you don't have to.

I suspect you need to set down with a pen and paper and re-think what you are trying to achieve. Then decide the technology you need to use. Not the other way around.

Galtee

BostonB wrote: »

+1 on the advice given so far. You don't want to do this. But you don't give enough information about the application, or the data in order to give a informed answer.



Access doesn't work for any online solutions. You need to dump it and your program and create an online application and database. Unless of course your data is so simple you can use a shared documents. In which case you probably don't need access either.

Creating replication between online and offline databases, applications isn't something to get into if you don't have to.

I suspect you need to set down with a pen and paper and re-think what you are trying to achieve. Then decide the technology you need to use. Not the other way around.

Access can be used for online solutions. It might not be the most efficient solution but that all depends on the full requirement and as such it may well be suitable for what the op wants.

BostonB

Galtee wrote: »

Access can be used for online solutions. It might not be the most efficient solution but that all depends on the full requirement and as such it may well be suitable for what the op wants.

Er...

EIREHotspur wrote: »

....I want to be able to manage this in the most efficient way so what would anyone suggest?...

yenoah

EIREHotspur wrote: »

Ok I have a program that takes it's info from an Access database.

I want clients to be able to edit an Access database online and create their own version of the application.

Can you explain this, are we talking about reflection here? What kind of info is your program taking from the database? What gets created when users edit an online database?

BostonB

I did a bit of digging and if you have access to SharePoint Online., (I'd assume that's unlikely). But if you do, you can actually get to your access data online

http://blogs.technet.com/b/msonline/archive/2010/08/25/more-fun-with-sharepoint-online-using-excel-and-access-with-sharepoint-online.aspx

But it doesn't help with the application you have written. I still think a clean sheet design is the way to go.

Galtee

BostonB wrote: »

Er...

Hmmmmm, the op has already said they are using an access db so one would assume that they're using the word efficient with reference to the application that they want to write to populate that db and not with reference to the use of the access DB itself. :rolleyes: I was replying to a post of yours which said that access doesn't work for any online apps and that's simply not true. In fact depending on how much control the op has over the data contained in the Access DB and the ability of the op to develop a webapp, they may well just want to dump a form in front of their current Access DB and job done but again that depends on the full requirement. There would only be a real issue with a high volume of concurrent users and by the sounds of it (ie where the op has played with the idea of users submitting excel spreadsheets for manual reconciliation) I seriously doubt that's the case.

BostonB

I took efficient to mean, as efficient use of their time. As the OP said "manage this in the most efficient way". As such by not working, I meant not working as an efficient solution to this specific problem. Not that you can't use MS Access online, I phrased that badly on reading it back.

Hes already written an application, I assume not web based or this question wouldn't have arisen. As such his application can only update a local copy of the data. Not an online version. If he creates multiple versions of the database (and the application) online and offline, then he has concurrency problems. My thinking, is its more efficient to put everything online, in one version of the database (and application). That sorts out concurrency problems. Especially moving forward with updates to the database design, or the application.

Perhaps you have a another way of managing concurrency issues with MS Access.

Galtee

BostonB wrote: »

I took efficient to mean, as efficient use of their time. As the OP said "manage this in the most efficient way".

And you tell them to dump their existing DB which seems to be the one constant and tell them to start from scratch??????

BostonB wrote: »

I meant not working as an efficient solution to this specific problem. Not that you can't use MS Access online, I phrased that badly on reading it back.

BostonB wrote: »

Access doesn't work for any online solutions. You need to dump it and your program and create an online application and database.

I have included your original quote which I was originally replying to for reference. I think they (your quotes) compliment each other very well.

BostonB wrote: »

Perhaps you have a another way of managing concurrency issues with MS Access.

I was referring to concurrent users on the site which would infer concurrent DB connections, ie users connected to the db at the same time.

BostonB

You don't seem to understand my reply. I'll respond when you address the issue of data concurrency, in your solution.

Galtee

BostonB wrote: »

You don't seem to understand my reply. I'll respond when you address the issue of data concurrency, in your solution.

With respect I don't think you understand your own replies. Who said theres a data concurrency issue?

jmcc

Galtee wrote: »

And you tell them to dump their existing DB which seems to be the one constant and tell them to start from scratch??????

Sometimes you have to do that when more than one user is going to be using what was originally a single user database. It is even more important when you have more than one user that will be updating data. There are issues that arise with multi-user databases that do not arise with single user databases. Access, though it has come a long way in the last ten years or so, is still very much aimed at the single user market.

Regards...jmcc

jmcc

EIREHotspur wrote: »

I had also thought of putting a downloadable Excel (nearly everyone would have a copy of that) on my website and have people edit that, email it to me so I could copy and paste that into the Access database but again thats adding another few steps.

I think that there is an XML import/export feature in Excel from 2003 onwards that could be used as the basis for this but I'm not sure if Access can import XML data.

As BostonB said, it might be a good idea to sit down and work out exactly what you want to do rather than just relying on using the Access database online. Once you have a clearer view of what you want to achieve, it is easier to come up with a workable solution.

Regards...jmcc

Galtee

jmcc wrote: »

Sometimes you have to do that when more than one user is going to be using what was originally a single user database. It is even more important when you have more than one user that will be updating data. There are issues that arise with multi-user databases that do not arise with single user databases. Access, though it has come a long way in the last ten years or so, is still very much aimed at the single user market.

Regards...jmcc

You have taken that one line you replied to completely out of context. You should probably read the previous posts before wading in and posting something irrelevant.

Access is not a single user DB. It handles concurrent connections and an application can be written around it to manage concurrent connections if needs be also so there's not really an issue using Access as a web backend depending on the requirement. You can't blindly say throw out a DB and start from scratch without knowing the requirement. Sometimes it has to be done, but in this instance there's no grounds for suggesting it and certainly no grounds for saying access doesn't work for any web apps.

jmcc

Galtee wrote: »

You have taken that one line you replied to completely out of context. You should probably read the previous posts before wading in and posting something irrelevant.

Go back and read them.

Access is not a single user DB.

I never said it was. Access is largely aimed at the single-user market. Microsoft's SQL Server is the one aimed at the multi-user market. Access may not be the ideal solution.

It handles concurrent connections and an application can be written around it to manage concurrent connections if needs be also so there's not really an issue using Access as a web backend depending on the requirement.

Writing an application around it to manage concurrent connections is adding an extra layer of complexity and it does not fix the fundamental limitation.

You can't blindly say throw out a DB and start from scratch without knowing the requirement.

You seem to have some kind of emotional attachment to Microsoft Access. Based on the current information, starting from scratch makes more sense. Efficient design often involves starting from scratch and reducing administrative overhead.

Regards...jmcc

Galtee

jmcc wrote: »

Go back and read them.

I never said it was. Access is largely aimed at the single-user market. Microsoft's SQL Server is the one aimed at the multi-user market. Access may not be the ideal solution.

Writing an application around it to manage concurrent connections is adding an extra layer of complexity and it does not fix the fundamental limitation.

You seem to have some kind of emotional attachment to Microsoft Access. Based on the current information, starting from scratch makes more sense. Efficient design often involves starting from scratch and reducing administrative overhead.

Regards...jmcc

Read the op again. The requirement he posted was based on populating an existing Access DB :rolleyes:

BostonB

Multiple users isn't the issue. Single version of the DB isn't the issue either.

EIREHotspur wrote: »

...exporting the database and then inporting into each Access database to create their version...

The issue is multiple versions of the same database (data) in different locations, local and online, and replicating the changes to all versions. Its potentially major hassle you can choose to avoid.

Galtee

BostonB wrote: »

Multiple users isn't the issue. Single version of the DB isn't the issue either.

The issue is multiple versions of the same database (data) in different locations, local and online, and replicating the changes to all versions. Its potentially major hassle you can choose to avoid.

*facepalm* OK

BostonB

Hes also talking about different version of the app too. Then spreadsheets etc. Its adds work and complexity you can avoid with a single version.

stevenmu

OP I think we need way more information about your requirements to be honest. In particular about the end result.

From the OP it sounds like the end result is that each user recieves an Access DB with their own data in it. This may be a fantastic solution, or it may be an unworkable nightmare.



It sounds to me like SharePoint could potentially be a fantastic solution for you (though in the interests of full disclosure, I love SharePoint and think it's a fantastic solution for just about everything ).

With SharePoint 2010 you can actually host Access databases which your users could access through their browsers. But that may not be the best solution.

It's probably possible to easily duplicate (and improve upon) your Access application with SharePoint features. Users could either use one common area, with their data kept seperate by permissions and filters, or each user could have their own seperate area. They could enter, view and update their data directly within SharePoint through their browsers, and if needed copy and paste or import Excel files. And it could all be done through the browser, no need for users to have Access or Excel.

But again we'd need more information to be sure.

BostonB

I suspect the OP wants to expand their development skillset. As such a tradition web application and database, would be more useful experience to a developer. If the OP is a Access developer, and wants to stay with that, then sharepoint would be a be useful to gain experience with. For future expansion, and adding more functionality, the traditional webapp/database would be better.

The Corinthian

stevenmu wrote: »

OP I think we need way more information about your requirements to be honest. In particular about the end result.

From the OP it sounds like the end result is that each user recieves an Access DB with their own data in it. This may be a fantastic solution, or it may be an unworkable nightmare.

I completely agree. One could use a 'template' Access mdb, create an individual user copy for 'editing', allow the user to 'edit' it (on-line, using a Web interface), then allow them to download it and later upload it again for inclusion of off-line data in a central database.

There may be very good reasons for wanting to do this, and the application described may be the best thing since sliced bread, or (I suspect) the OP has a very limited knowledge in IT and is just talking about Access and Excel because it's all they're familiar with, and the application may be a serious disaster. Or somewhere in-between.

I think they need to describe in detail their requirements first. From there, no doubt ten different types of solutions are possible, from which point he can pick the best one based upon meeting both budget and requirements.

BostonB

One of them gets an error, sends you back a incomplete database, then you don't notice for a few days, during which there's a bunch of other updates. Or you get incomplete upload or download, and don't notice.

stevenmu

We don't really know enough about the situation or requirements to know what potential issues there may.

Going from just the OP, one possible sample scenario is that the OP is an accountant with some Access/Excel knowledge, has developed a simple single user accounting package in Access and wants to sell this to a number of clients. As part of the deployment process the OP will do an initial load of basic data (company name, tax details, contact info etc) and then send the client out the Access file at which point the client will commence using it to track their sales, customer accounts, stock or whatever.

In such scenario there's no issues around merging or coallating data. Troubleshooting will be mostly done directly on the customer's instance of the database (or a copy thereof). Functionality updates may be just done manually on a customer by customer basis. And most of the issues we're raising aren't of much relevance.

We really need more detail again from the OP.

BostonB

Well we all said that at the start.

I'm just bitter because I see these MS Access monstrosities so often.

Seth Brundle

BostonB wrote: »

Well we all said that at the start.

I'm just bitter because I see these MS Access monstrosities so often.

In fairness, MS Access can be a brilliant tool to perform simple analysis and reporting and its licences are inexpensive.
However, for what the OP's system sounds like, its quite (IMO) likely that MS Access wouldn't be a viable option. It may be possible to use Access but I suspect that there are 101 other alternatives more suitable.

Galtee

BostonB wrote: »

Well we all said that at the start.

I'm just bitter because I see these MS Access monstrosities so often.

It's funny, I usually find that it's the competency of the developer involved that's the problem where there's a monstrosity to be found regardless of what backend is used.

stevenmu

BostonB wrote: »

Well we all said that at the start.

I'm just bitter because I see these MS Access monstrosities so often.

I can certainly relate to that, having replaced more than a few in my time (and tbh having probably created a couple of my own ).

BostonB

Galtee wrote: »

It's funny, I usually find that it's the competency of the developer involved that's the problem where there's a monstrosity to be found regardless of what backend is used.

Thats a very idealistic viewpoint. But I can't say I've met that very often. Usually its the result of some business/power user with no development background. A developer is most like to use the newest most high end solution, rather belt and braces.

SerialComplaint

If you do move away from Access to an online solution, check out Zoho Creator as an alternative to Sharepoint.

Galtee

BostonB wrote: »

Thats a very idealistic viewpoint. But I can't say I've met that very often. Usually its the result of some business/power user with no development background. A developer is most like to use the newest most high end solution, rather belt and braces.

How is it idealistic? It's based on my experience.

The Corinthian

BostonB wrote: »

A developer is most like to use the newest most high end solution, rather belt and braces.

Wrong. A developer should seek to use the best solution, based upon both budget and requirements and this is actually unlikely to be "the newest most high end solution".

If they're pushing principally for "the newest most high end solution", then - unless it is also the best solution, based upon both budget and requirements - they're doing so so they can gauge more money from the client and/or wish to pad out their CV's.

BostonB

I don't disagree. That just my experience in general.