|Rubik's Cube- I'm new and need MS Access/SQL Database help please|
Need to upgrade an e-commerce site and am a little lost
| 5:55 am on Oct 8, 2002 (gmt 0)|
Let me first say thanks for this great site.
It's probably important to know a little backgorund on me: I own a small but profitable e-commerce company (Skincare products) and I am the only full time employee-I outsource everything- the call center fulfillment-manufacturing-etc. I did all the html programming myself for the web sites (there are about 35 sites) myself and use a third party shopping cart (cartmanager)that links to an Innuity gateway. All of these components are linked loosely by an Access database that someone else designed for me. The person that did the original database died in a car accident last month, and now I am on my own. I know little about database programming, (although I am a fairly quick learner), but I think I would perhaps be better off outsourcing this "project" again unless you guys tell me there are some simple solutions out there. I just need your advice on what I should do:
The problem is that the database resides on a PC in my office, it is not on a server, nor accessible from other locations through the internet. Because of this, neither the operators who take the orders, nor the folks who do the shipping/fulfilment can access this data. If a customer, for example, wants to cancel or change an order, the operators at the call center have to email me with the info, and I change it manually. Furthermore, when UPS cannot deliver a package, the fufilment center has to email me and ask me to contact the customer-they don't have the customer's info and can't access the db from there location. There are some little "quirks" but generally the database works "as is" and has served me well to now. It is however very taxing on me and my time as we grow larger.
I need to make certain parts of the database available to the call center and the fulfilment house. This means it has to reside on another server, right? How would I upload this database and make it accessible over the internet/ (I rent a server for my websites, should I use this one or rent another?) Good news- I don't need a lot of different users at the same time (no more than 5 at the same time). I want to give the other users limited accessability (no credit card info etc.) and I want the ability to "see" what they write or change, but if a customer calls to change or cancel an order, the operators should be able to handle this. Should I keep Access or switch to another solution? Could I keep my existing queries/macros if I switched? Would I have to design a completely new DB? I am hoping not to have to start over from scratch.
I have spoken to MANY people regarding this issue, and have gotten MANY different answers, ranging from "switch to SQL Server" to "Switch to Great Plains" solutions, to "use webideal's solution". I think switching to a MIVA merchant etc. would be a huge step backwards. Prices quoted for the changeover: $2,000 to $100,000. I think the $2,000 solution is not a solution, and I think the 100k idea would be great for a company about 5 times bigger than mine. A high 4 or low 5 digit price would be more is what I am hoping for. Please let me know wht you think- should I keep the Access database and make it Internet accessable or should I switch and if so, what to?
Should I just move up to SQL Server? "What are the options? FYI, it (the current Access DB) IS going a lot slower now that we now have over 20,000 individual customers- so the macros and queries etc. take a lot longer to work than before.
EVERYONE says they have a "complete" solution (like MIVA merchant etc.) but these never are complete, and there is always a lot of customization to do, which only MIVA people do, and I found MIVA to be a step backwards. I don't think the probelme is my e commerce solution, but I would be willing to change it if it made sense.
I am hoping that enough of you will respond to put me on the right track. "Partial answers" are VERY WELCOME! Please- don't be afraid to speak up!
Also,If anyone KNOWS anyone who can help me with this(I am obviously willing to pay a market rate), especially if they are in the Philadelphia area, please let me know. But I suppose that until I know what it is I am trying to do, a database programmer may not do me any good, huh? The ideal person will be very familiar with both the database AND e commerce options.
Thanks in Advance for all of your help!
[edited by: TallTroll at 4:29 pm (utc) on Oct. 8, 2002]
[edit reason] unecessary URL [/edit]
| 8:13 am on Oct 8, 2002 (gmt 0)|
My thoughts on the matter would be;
Access should be okay for 20k records as long as you set up the relationships properly and apply some suitable indexes. The big factors in speed will be CPU speed and the amount of memory available, for 20k records I doubt that disk access speed will come into the equation.
If it were me I'd start by looking at what types of dynamic pages my current server offers, this might be ASP, PHP or something else. Dynamic (aka Server-Side scripting) will allow you to integrate the database and the website meaning that your operators will be able to access the latest information and when they need to update it they can.
Assuming you have at least one dynamic pages option available to you I'd start by thinking about moving the Access database to the server and letting it live there permanently once you have all your interface pages in place.
First you'd need to build/find a set of simple display-only pages to the relevant parts of the database (tables/queries etc). This shouldn't be too tricky as you should be able to take most of it from samples or have someone build this v. quickly.
Secondly you'd need to build/find a set of pages which would allow you/operators to update the database online. The complexity of this task will vary depending on exactly what is involved with each update.
Ideally this would allow you a simple way of allowing syncronised access to the database that is unified and accessible to all those whom need it.
As you have probably figured out by now, an all-singing & all-dancing solution is expensive but you hopefully get what you paid for in that you get support, upgrades etc.
Alternatives are either paying a 3rd party to do the work which gets you a decent solution (presumably with some support) for a smaller amount of cash, or getting hold of suitable samples and doing the work yourself which will be the cheapest but could present problems to do with support etc.
| 1:24 pm on Oct 8, 2002 (gmt 0)|
Welcome to WebmasterWorld!
My preference would be to import the database into MySQL (easy to do) but you could keep it in Access. The issues will be speed and total capacity.
In either case, as Dreamquick noted, is that you'll need to have some sort of web-based interface (secured by uname and password) in order to add/edit/delete data as needed and view whatever data/reports you want. This will need a scripting language like PHP or Cold Fusion.
If you want to do this right then a complete re-write MAY be in order - but not necessarily. It all depends upon how well the database was organized. Again, if it were me, I would shoot for a PHP/MySQL solution for both the Cart and the Admin features needed by your staff.
There is a ton of information about both of these technologies here and elsewhere on the web. Plenty of folks out there willing to help you along if you're willing to do the work and learn. You could probably do everything but I would encourage you to hire someone to integrate the Cart - it is very important that the security and interface with your transaction processor work flawlessly and is tight.
That being said, if you're looking for someone here to bid the work you should place a post in the Commercial Exchange forum:
| 4:13 pm on Oct 8, 2002 (gmt 0)|
having nearly destroyed my site with Access, I would suggest you immediately port the data and code to something else.
My site isn't ecommerce, but is instead a working database delivered through a browser. With 3 people on it was fine with Access. When the number hit 10 my pages would start to take upwards of 5 minutes to load! I examined code, server configurations, and everything led back to Access not being able to handle the constant updating, deleting, and inserting.
I spent an agonizng weekend porting and recoding to mySQL and that Monday morning calls came in asking what I did to fix the site.
If you are just doing an ecommerce thing to hold orders, you might get away with Access, but I'll never use it again.
| 5:02 pm on Oct 8, 2002 (gmt 0)|
|When the number hit 10 my pages would start to take upwards of 5 minutes to load! |
I'll vouch for this as my site did the same though I think it was a combination of Access, a web server that wasn't optimized for Access, and the site was using ODBC connections - ooogh.
| 6:15 pm on Oct 8, 2002 (gmt 0)|
I'm not going to lie and say I've never seen access cripple a site because I have, but if you are doing mainly reads with the occasional write its not a big issue.
In case you are wondering the worse case scenario is that IIS dies and the service needs a restart, but that's pretty rare.
The real problem seems to always be with locking, specifically when you have lots of things being written into/updating the database at virtually the same time.
Most likely this is the access concept showing its age - it was designed to be a simple desktop database but it has been pushed into many other arenas.
| 6:16 pm on Oct 8, 2002 (gmt 0)|
How do I make the Access Database accessible over the internet? I am using both Access XP and the last version of Access just prior to XP. Do I load it onto my server? How is this accomplished? Does my server have to be an NT server to make it work? Do I need a different version of Access?
I went to the MS Access site and couldn't find the topic there. If anyone knows of a good tutorial for this, please let me know (be great if you could include the link).
Also, any tutorials that talk about importing an Access DB to SQL would also be helpful.
| 9:53 pm on Oct 8, 2002 (gmt 0)|
my advice (as someone who does a lot of web development with access databases and/or e-commerce) is to use a web developer right now. the amount of work you need isn't that great and shouldn't cost much. of course, when you don't know where to start, it's confusing. take your time and you'll save a lot more time in the long run.
don't rush into this - you'll miss vital steps. don't try and do everything yourself - you have too much to learn right now and (by the sounds of it) not enough time to learn. a web developer will make life a whole lot easier.
keeping this very very simple, what you need is to have the database hosted on a Windows web server with a few ASP scripts to read from and write to the database. your problem right now is that you don't know ASP and how to build dynamic websites. you may be able to learn enough ASP etc to do everything yourself in just a few weeks, but it could take you a lot longer or you could make a big mess of it.
getting into a bit more detail, you'll need a number of ASP scripts for the call center, for you, maybe for the fulfilment people, maybe also for automated entry of customer details when purchases are made - without knowing a lot more about your business etc, there is no way i can determine exactly how many scripts you'll need. it could be a few, it could be several. leave that for a web developer to worry about.
look for a local web developer with knowledge of ASP / Access / SQL Server and of ecommerce. check their portfolio to see if they have sites that clearly use ASP and databases - if not, move on to the next. you should find a few that can do the job you need, so contact them and ask for quotes or estimates. tell them you have an Access database and you need a few ASP scripts writing, nothing more than that right now.
they can take your database, upload it to a server, and write ASP scripts to read from and write to the database and test that it works ok. you may want to pay 2 or 3 developers to upload and test the database with a couple of test scripts just to make sure they can do the work, to find out if your Access database is usable as it is (more in a minute), and whether or not the developer is ripping you off.
don't rush to dump Access. most problems with Access are caused by incorrect server configuration, poor ASP code, or poorly designed or corrupted databases. Access can (in theory) handle 256 concurrent users. as each user will normally only access the database for a split second, you need a pretty busy site to get more than a couple of concurrent users. a long SQL query on 50,000 records over several tables with a couple of sorts as well can still take less than a second. as you only have 20,000 records and you're not likely to have a lot of concurrent users, Access should be perfectly capable of doing the job you need right now.
a bigger problem will be whether the data you have entered is correct and consistent. you may have entered dates in one format for some records and another format for other records. ASP can be very fussy with certain characters or field types. a web developer can easily work through a 20,000 record database to check for problems, so although it can be a pain, it's nothing that cannot be overcome with a bit of time and effort. let the developers test your database online and upgrade it or modify it as necessary - they are the experts.
the Access database needs to be hosted on Windows servers. most versions of Access can be run on recent Windows servers (Win2k+) without any problems. i don't know if NT will handle Access 2000+ as i stopped using NT a couple of years ago. at the worst, you may need to upgrade the version of Access you are using. again, let the developer deal with this as they will know best.
if you go ahead with a developer, you could have ALL the work complete in anything from a couple of weeks to a couple of months. if you go it alone and you're going to learn ASP etc etc, good luck and we'll see you next summer :) (well, maybe earlier, you did say you're a quick learner!)
| 10:34 pm on Oct 8, 2002 (gmt 0)|
Thanks for the advice. I agree, this is probably too big for me to handle alone.
I've posted a "help wanted ad" in the commercial section of this site. If you know anyone, or if you know of a good website to go to to find the right developer, please let me know.
Thanks again for your replies.
| 11:33 pm on Oct 8, 2002 (gmt 0)|
|I want to give the other users limited accessability (no credit card info etc.) |
I suggest keeping your main database on your PC and configuring Task Scheduler on Windows to upload the database to a web host.
Your web host should at least be able to run ASP (and optionally ColdFusion). They should also have the ability to host SQL Server should Access be not sufficient later.
Could the fulfillment house receive the contact details when they receive the order? Can customers check their order status any other way? If they can not, you can create a select query from the web database that the fulfillment house could view. A login/password system would be good. And you can find free ones on hotscripts.com that does 99% of the work for you, letting your "wrap" your current code around the login/password script.
|and I want the ability to "see" what they write or change, |
This requires performing updates and tracking changes to the DB. A bit more complex, and would push it back to phase II (or hire a programmer/webmaster)to do.
|but if a customer calls to change or cancel an order, the operators should be able to handle this. |
Again, phase II. How about allowing the operators to just view the data for now. You could fancy it up by including an e-mail form that would e-mail you change/cancel order details that are consistent and complete.
By the way, how do you get data into your local Access database? Do you get a CSV file from Cartmanager and a macro imports the data into your database? I ask because if you are considering making the web database your source database, you need to think about a process to push the cart data into the web database. That is something a programmer should be hired to do.
I hope this helps.
| 1:55 am on Oct 21, 2002 (gmt 0)|
Thanks for all of your responses.
I was wondering if anyone out there has heard of them/has any opinions about these "alternative" solutions.
or Order mate Pro ( [tamtech.net...] )
or I-Code - [icode.com...]
These "Mail Order Management" solutions seem to be pretty much what I want to end up with, however I'd like to own the code and be able to tweak it. I belive only the last one (Icode) is like that, but I am not sure if its scalable.
It would save a lot of time (and $$) to take one of these and make it work, then try to build the same thing out of mine. My db is not nearly as sophisticated as these, and they offer a lot of nifty features that I would love to have (email marketing, order processing etc.)
If you have any familiarity with these products, or know anyone who does, I would love to hear some opinions on them.
| 2:54 am on Oct 21, 2002 (gmt 0)|
I used the desktop version of Mail Order Manager (MOM) for a few years for a mail-order business that was much smaller than yours.
I couldn't get the time of day from MOM people unless I was looking into buying an additional module. And, of course, they set it up so you couldn't do s**t without buying additional modules. You couldn't tweek the code yourself so your were trapped.
I haven't looked into their online products in a couple of years but they used to be terrible. The quality of their desktop product was fine.
I know of one large mail-order company that is happy with MOM. But they don't use MOM for the web side.
MOM was developed more than a decade ago. There's got to be better stuff now.
My advice; Keep looking.
| 3:28 am on Oct 21, 2002 (gmt 0)|
David, just to throw this out there... have you looked at osCommerce?
| 6:38 am on Oct 21, 2002 (gmt 0)|
davidk, sorry to hear about the misfortune. In your place I'd put the whole thing to rest and outsource the project to someone who's a competent, experienced programmer, possibly customizing an open source solution, and using good, reliable, secure hosting. The Apache/PHP/MySQL combination is practially universally supported and popular.
One of the most beautiful ecommerce sites I've ever seen used osCommerce or Redhat Interchange (both open source) that was customized by the web development firm - and it had static pages.
| 7:25 am on Oct 21, 2002 (gmt 0)|
Thanks Marcia, I will check that out.
Just in case someone is reading this post and has the same problem that I do, I just found an outstanding website that is dedicated to issues just like this one (e-commerce order management/integration with a database).
I find it pretty amazing that I never heard of this guy's site. Exactly what I needed - ALL database and order management tools and software, listed AND REVIEWED on one site. Looks like I may have to pay something to get access to some of the good stuff, but it's by far the most comprehensive guide I have ever seen on e-commerce related tools. I think you guys may want to check it out, and maybe even bookmark it for future use.
Here is the page with the real meat in it (the buyer's guide):
This guy is located near Philadelphia, by the way, and I am going to see if if he's willing to talk with me by phone, and point me in the right direction.
FYI- so far I have had three quotes to fix me, ranging from $5,000 to $118,000. The problem is that there are obviously different ways to skin a cat, I just don't know which one MOST closely fits my needs, budget, and time constraints. It is amazing though that three different people were so far apart from each other.
I look forward to hearing more opinions from you, your posts are very helpful to me.