MS Access Forum / General 2 / May 2008
Data capture via online form
|
|
Thread rating:  |
David Anderson - 14 May 2008 13:48 GMT I have created an Access 2003 database solution for an annual international photographic competition. In it's first year of operation, all the entry forms were paper-based. With well over 6000 images submitted by nearly 800 entrants, we had a major data entry task to complete. For the 2009 competition we would like to gather all this information via an online form. That way, all the entrants do the typing rather than us and they can't then complain if we spell their names, addresses and image titles incorrectly! We also want to incorporate payment via PayPal.
My problem is that while I have some website design skills, I have never designed any online forms and little or no knowledge on how best to link such forms with an Access database. I also have a limited time to get this up and running (the target is Sept 2008).
I would appreciate some guidance on the best approach to this task. I'm running Access 2003 under Windows XP Professional and also have Microsoft VSTO 2003. Do I have to start learning all about ASP, IIS and a bunch of other unfamiliar acronyms or are there any commercial online forms design tools that would simplify the job?
David
Wayne-I-M - 14 May 2008 13:56 GMT No - asp will do it for you. (use the wizard).
Create the form as you need. Save the form to a databse (use the wizard) "keep it small" Open the on-line DB and set the formats
As and when you need just import this data into an off-line DB via a temp table and update query.
 Signature Wayne Manchester, England.
> I have created an Access 2003 database solution for an annual international > photographic competition. In it's first year of operation, all the entry [quoted text clipped - 17 lines] > > David David Anderson - 14 May 2008 14:11 GMT Hi Wayne, I'm a bit further down the learning curve that you have assumed. Where do I find this thing called ASP and it's associated wizards?
Regards, David
> No - asp will do it for you. (use the wizard). > [quoted text clipped - 32 lines] >> >> David Wayne-I-M - 14 May 2008 14:40 GMT Which web design programme are you using - frontapge is really good with access (and has lots of wizards).
I have just made a page to show you an example
http://www.folgarida.co.uk/sample_form.asp
I will delete this today so please copy the code if you want to as it will not be there long
I have used the names Wayne Albert David
Note there is no send button on the form as I don't want the information (but you will !!)
 Signature Wayne Manchester, England.
> Hi Wayne, > I'm a bit further down the learning curve that you have assumed. Where do I [quoted text clipped - 39 lines] > >> > >> David David Anderson - 14 May 2008 15:07 GMT Wayne, Thanks for taking the trouble to make the sample form (and the advice ref disguising email address).
I have got a copy of FrontPage 2000 that I use to maintain one website, but one or two more recent sites were created using Adobe GoLive CS2. I will probably update my copy of Adobe's Creative Suite to the CS4 version when it comes out and I will then gain access to Dreamweaver. However, I would not have a problem in updating my copy of FrontPage if it was going to provide the easiest way to code links to Access.
David
> Which web design programme are you using - frontapge is really good with > access (and has lots of wizards). [quoted text clipped - 67 lines] >> >> >> >> David Albert D. Kallal - 14 May 2008 13:56 GMT "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
> Do I have to start learning all about ASP, IIS and a bunch of other > unfamiliar acronyms or are there any commercial online forms design tools > that would simplify the job? Unfortunately MS access has little to do with the web. you're pretty much gonna have to use some web based technologies to develop the system, and MS access is not going to help you.
I would suggest you consider using some of the web based design tools, either FrontPage, or visual studio and asp.net.
I suppose you could also consider using one of the many online photo systems like flicker or whatever.
You could also perhaps do this with facebook, and have people join face book and become a group of friends, and that way they can each post their own photos into their own photo albums.
Anyway, since this is not an MS access project, then you're in the wrong place and in the wrong newsgroup....
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
Wayne-I-M - 14 May 2008 14:14 GMT Hi Albert
Access works fine as an on-line database (we have 7 sites using access to collect form results that users input)
As long as you're OK with access and have just a little web design ability its not difficult
 Signature Wayne Manchester, England.
> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message > [quoted text clipped - 18 lines] > Anyway, since this is not an MS access project, then you're in the wrong > place and in the wrong newsgroup.... Albert D. Kallal - 14 May 2008 14:57 GMT > Hi Albert > [quoted text clipped - 3 lines] > As long as you're OK with access and have just a little web design ability > its not difficult You're absolutely correct, but on the other hand you're confusing the absolute hell out of this poster because they don't really understand the difference between the jet database engine and MS access the design tool.
You can most certainly use a jet based database engine on that web site, but you do not have to install MS access on that web site to use get, and I'm willing to bet bottoms to dollars, ms-access is in fact not on the web site.
That person will STILL have to adopt some web based design tools. There is not a whole lot of extra time saving or efforts by using a jet back in this case.
I'm really just try to stress to the end user, and not get their hopes up that they're somehow going be able to use the forms, reports, and programming language we have in MS access to create a web based application, because that's simply not the case....
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 14 May 2008 15:21 GMT Albert, I'm aware of the distinction between Jet and the Access design tools and some years ago I played around with MSDE as an alternative back end. However, you are right to assume that I am unclear on what software would need to be installed on a website in order to facilitate a database enabled online form. Nor do I know if I need to ask our ISP for some additional services to make it all work.
David
> You're absolutely correct, but on the other hand you're confusing the > absolute hell out of this poster because they don't really understand the [quoted text clipped - 13 lines] > programming language we have in MS access to create a web based > application, because that's simply not the case.... Wayne-I-M - 14 May 2008 15:39 GMT You are correct that it not on the sample form - (now deleted) - but there are many DB's my sites we have (I made the html, asp and access sections). I can't give you the address here (that would be a big mistake on a public forum).
David can I make one suggestion that Albert has refered to. My 1st answers assumed you had some expeience in creating these projects so I would suggest that as it has to work 1st time. Get someone to make it for you.
I know thats not the idea of these forums (learning is grea if you have time) but in this case the time your would speand learning would be less than the cost involved. It's quite a simple thing your looking "if" you know how to do it. If not, it will take you quite a long time and it will need a great deal of testing. There are many many people around (not me by the way - I am not touting for work) who would only charge a small amount to do this for you.
 Signature Wayne Manchester, England.
> > Hi Albert > > [quoted text clipped - 20 lines] > programming language we have in MS access to create a web based application, > because that's simply not the case.... David Anderson - 14 May 2008 17:51 GMT Wayne, It's a valid suggestion, but I think I would like to persevere a little longer in pursuing a solution I can code myself. I don't mind a bit of an intellectual challenge. Given that this is Day 1 of my project, my first objective is to find out the most effective ways of achieving my objective. I will then look at the recommended process more closely to see if I think it is within my capability.
David
> You are correct that it not on the sample form - (now deleted) - but there > are many DB's my sites we have (I made the html, asp and access sections). [quoted text clipped - 51 lines] >> application, >> because that's simply not the case.... David Anderson - 14 May 2008 14:22 GMT Hi Albert, I forgot to mention it, but you correctly assumed that I would also want my online form to provide a facility to upload digital images. We also accept prints but it's a little trickier to squeeze them into a PC....
Are you suggesting that MS Access cannot form any part of my solution and that I have to redevelop my entire application from scratch using different web based design tools? Would it make a difference if I limited my objectives so that the online data capture was a purely one-way process, i.e. with no interaction with the database while the form is being filled out (with the resulting information being imported into Access at a later date)?
Regards, David
> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message > [quoted text clipped - 18 lines] > Anyway, since this is not an MS access project, then you're in the wrong > place and in the wrong newsgroup.... Albert D. Kallal - 14 May 2008 15:08 GMT > Are you suggesting that MS Access cannot form any part of my solution Well access can form part of your solution, but the part that access contributes is not worth anything to you at all.
Many web providers allow you to palce/put the mdb file on a web site, and you can write web based code to use data from that database. There's a significant distinction between that of the jet database engine that most of us use with a MS access, and that of the MS access design tools.
As the other poster pointed out, you can use the backend mdb file on the web based solution, but any of the code, forms, and reporting system is not going to work through the web site at all. In other words, the only thing you really get by doing this is some tables on your website, but none of your coding or design tools can be used to create forms or reports for that web site.
> that I have to redevelop my entire application from scratch using > different web based design tools? Yes, the above is pretty much the case. Since you're not really using the MS access design tools in this case, there's little if any advantage to using the jet back end mdb file, or that of using SQL server. We're talking about a data repository here. Remember, when you use the jet database engine (the one we use with ms-access), or you use SQL server, in both cases you're not able to create forms or reports with that database engine. That data engine (JET or sql server) is only a place where the tables reside, and then you can use visual basic, c++, your web based tools, or even MS access to extract the data from those tables.
If you go to a web based solution, then you can use the backend mdb database for the web site, but you can not use any of the front and tools to build and design forms and a user interface in MS access for the web.
At the end of the day this really means that you really don't get to use any of the programming and design tools in MS access to help you build that web site, and therefore there is little if any advantage to using the jet database engine for a web based site.
MS access is simply the wrong tool and inappropriate in this case. You can as the other posters suggested find some web providers that allow you to place the mdb data file on that web site, but any interface to that data will be done through the web based design and programming tools, not the MS access design tools.
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 14 May 2008 15:39 GMT Albert, I think I probably knew already that Access would not help me to create the online form itself. That's what I assumed ASP, etc, would facilitate. I'm not expecting my existing Access database to run online. Instead, I was envisaging that a new mdb file might be created for use on the website - a mdb file that simply recorded the data entered via the online form. I then imagined that I would find some way to extract the tables from this mdb file and import them into my main Access database, residing on my own PC.
I was also hoping that data in the tables within the web-based mdb file might be used for various validation purposes in the online form, though I appreciate that such coding cannot be done in Access.
David
>> Are you suggesting that MS Access cannot form any part of my solution > [quoted text clipped - 40 lines] > will be done through the web based design and programming tools, not the > MS access design tools. Albert D. Kallal - 14 May 2008 16:23 GMT > Albert, > I think I probably knew already that Access would not help me to create [quoted text clipped - 5 lines] > from this mdb file and import them into my main Access database, residing > on my own PC. To be fair, in this situation if I had a fairly rich application with all kinds of reporting abilities etc, and want to salvaging keep that application running, but integrate it with a web based system, then what I would do in this case is move the backend data to SQL server, and place that on my website.
I would then designed a web based system to intervista SQL server, and if that point you'll have both a web based system part, and your MS access part can pretty much wealth function as it does now, except that the data for the backend would be from SQL server, and not a jet based mdb file.
in theory if you're running your own machine in your own web based server, then you could perhaps connect directly to the back end mdb file at the same time the web site is using this file, but for the most part it's not the really recommended approach. Since the chances are not good that you be hosting a web site, then you really don't have a way to connect to the mdb file over the Internet, and that's why I'm suggesting SQL server.
So there's no question that you can continue to use your application by simply moving the backend data to SQL server, because SQL server works well thought web site, and can both the web site + you + ms-access can connect to the database egine at the same time in this case...
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 14 May 2008 17:30 GMT Albert, SQL Server does have some appeal because I could then have several people using my main Access application from their own homes and sharing a common data repository. However, as a non-profit organisation, we cannot afford to spend about 1,400 GBP on SQL Server Standard Edition. Is there any reason why the free version, SQL Server Express, should not work in this scenario while also being accessed by an online form application? The database size limit of 4GB is unlikely to be an issue (we're not going to be storing any digital images in the database).
I seem to remember looking into this a year ago and hitting some problem with ISPs being prepared to support the full version of SQL Server (for a fee) but not being interested in supporting SQL Server Express, thus limiting the free product to use on your local network. Is this also your understanding?
David
>> Albert, >> I think I probably knew already that Access would not help me to create [quoted text clipped - 28 lines] > well thought web site, and can both the web site + you + ms-access can > connect to the database egine at the same time in this case... Albert D. Kallal - 15 May 2008 01:10 GMT > Is there any reason why the free version, SQL Server Express A great choice, and I never really made the distinction between 'express', and the corporate edtiion. The express edition now about as good as the full paid version was some years ago.
> I seem to remember looking into this a year ago and hitting some problem > with ISPs being prepared to support the full version of SQL Server (for a > fee) but not being interested in supporting SQL Server Express, thus > limiting the free product to use on your local network. Is this also your > understanding? Most ISPs often charge a little bit more to host SQL server. It's not the fact that the database engine is free as much as it's simply that you're using extra processing bandwidth, memory and resources of the server. I don't think it really ever was they will not charge to use sql server express vs that of the corporate edition.
If you're not hosting your own web site with your own server that you've purchased (which would mean that you can put anything you want on the server), then you don't have much choice. The cost of having SQL server per month by some ISP's is so dirt cheap as to not really change the cost of your monthly bill from that provider anyway.
I do know that some ISPs did say they don't want to use SQL server express because it does not scale well in their server farms (supposedly uses too much CPU, and this may actually be true, because you're not going to use of sql express in a server firm to service 200 companies each with 20 employees). Thus, the ISP can't hang fifteen to twenty customers off of one box if you're purchasing a virtual server from them and using express. A virtual server means that have your own copy of windows, and can install + run just about anything you want on that box. However if you start installing things that gobbles up too much processing time, they're not gonna be happy with you. on the other hand which edition of Internet services are you planning to use now anyway? (are you actually choosing the type of server, and software you going to run on the server at this point time, or simply leaving the choice up your ISP).
In place of purchasing a virtual server, you can also purchase a hosted box. Thus you can add additional hard drives, install more memory, and install any piece of software you want on that server. You won't incur additional charges because you're actually paying for the whole box, it's just not on location at your business anymore. (and they often provide a dialup phone line for when the internet is down so you can dial up into the server to do things like reboot the server, and do offline maintenance).
It's not clear if you're going to purchase and set up a server at your business location, and simply hook it up to the Internet. Again, in this scenario you can install and run any piece of software you want without additioal charges.
The advantages of having your own complete hosted box is you can do whatever you want to that box, and often when you add additional things like more memory or additional disk space you can do so without incurring a monthly additional charges. On the other hand, this also means you'd better have somebody who has experience running and setting up a server and Internet services.
A virtual hosted server is a little bit cheaper, because in that case you still have a full computer and operating system (you can log into), but you're sharing your hardware and memory with other people.
And of course the most common is simply that you get a hosted web site and some database space, and how that shared is not exactly clear in my mind, but you are sharing that resources with other people, and this is usually the cheapest monthly solution you will purchase from a provider (and for people that simply purchase a cheap monthly website space, this is what most of them use).
You should contact your current ISP as to what kind of services they offer, or perhaps find someone who can help you work through these issues and ideas. This MS access group is certainly not the right place, and unfortantly ms-access don't have much to do with the web...
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
, you don't incur monthly charges to do that
David Anderson - 15 May 2008 09:56 GMT Albert, Thanks a million for your extremely helpful reply. Despite having a background in IT, the whole topic of web hosting is outside my personal experience and I have much to learn before it all makes sense to me. I realise that this discussion is no longer relevant to this forum but your answer has been more useful than I got a year ago when I asked about SQL Server Express on the relevant MSDN forum!
I will seek advice elsewhere as you suggest (I have already emailed my ISP) but perhaps you could first clarify some remaining points. My current understanding is that some version of SQL Server is installed by me on the webspace provided by an ISP. That ISP then charges me an additional monthly fee to cover the costs of my presumed increase in processing load on their server. If that is correct, then I didn't quite understand your comment "....because you're not going to use sql express in a server firm to service 200 companies each with 20 employees". That comment seems to imply that it is the ISP who buys and installs SQL Server and customers then pay for a share of their SQL Server facilities. Both scenarios cannot be true, so which is it?
My requirement is associated with a non profit organisation, so we would be looking for the cheapest available solution that does not require us to have a lot of specialist technical skills. I guess that this would be the virtual server option from an ISP. We were not considering the option of buying our own web server. You asked about which edition of Internet services we were planning to use. I presume you mean software called Microsoft IIS. Is that something I need to learn about or can I leave that to the ISP?
David
>> Is there any reason why the free version, SQL Server Express > [quoted text clipped - 70 lines] > and ideas. This MS access group is certainly not the right place, and > unfortantly ms-access don't have much to do with the web... Albert D. Kallal - 15 May 2008 12:00 GMT Of course another possibility is to use share point services, and open up a place where users can drop their files on the server. (and, with access 2003, or 2007, you can place your data on sharepoint also).
> but perhaps you could first clarify some remaining points. My current > understanding is that some version of SQL Server is installed by me on the > webspace provided by an ISP. No, you don't install sql server your self. In most cases they sell you a montly hosting package that includes SQL server as part of a monthly fee. In some cases you'll find that some providers offer linux based hosting, and you get MySql in place of Microsoft's SQL server. In many cases you thus actually get to choose the platform and tools you're going to use or adopt. For example my provider offers both linux and windows web hosting packages. (don't confuse a hosted web site with a hosted box, or a virutal box you pay for...all 3 are differnt).
> I didn't quite understand your comment "....because you're not going to > use sql express in a server firm to service 200 companies each with 20 > employees". That comment seems to imply that it is the ISP who buys and > installs SQL Server and customers then pay for a share of their SQL Server > facilities. Both scenarios cannot be true, so which is it? Yes, it is the ISP that installs sql server for you (the only exception here is if you're buying your complete own hosted box, or virutal box). Furthermore, I don't think they really install it for you, they just *allow* you to use the one that they have running already. When I used the term "server firm", that was actually a typeo, and I meant to say "server farm". However the meaning is actually the same in both cases! So, what I'm saying is that if you need a couple hundred people to use your SQL server database application, you can't use sql express because it does not scale well. The express edition will max out the comptuer faster then the Enterprise/corporate edition of SQL server will.
SQL server express cannot utilize multiple processors for example. So, the express edtion is fine for 50 users no problem. However don't expect a SQL server express to service 500 users. It's the difference between a car and a truck, the car and the truck can go the same speed, but the truck is designed to carry a heavier load.
This simply means that some ISPs don't want to use and installed SQL server express on their boxes, because they can NOT hang a lot of users off of it. (it starts gobbling up too much processing, and if you have a server with four or eight processors in it, SQL server express can only utilize one of the processors). The express edition just doesn't scale as well the big version, so some of these providers simply don't want people running on their boxes. However, as I just finished pointing out, in most cases you not running a hosted server, but only buying web space and use of SQL server. you actually don't install anything at all these cases.
> My requirement is associated with a non profit organisation, so we would > be looking for the cheapest available solution that does not require us to > have a lot of specialist technical skills. I guess that this would be the > virtual server option from an ISP. no, actually it would be the third solution I suggested, and that is simply just a hosted web site, with some SQL server space thrown in there. For example for one of my sites, I'm www.dotster.com
The have thier plans here:
http://www.dotster.com/hosting/plans/
For $5.95 you get 5GB disk space, 10 email accounts, and MySql for your sql server. It linux based, and it supports pearl + php for the programming lanauge.
The windows plans are here: http://www.dotster.com/hosting/plans/windows
For $6.25 you get a plan, and I see they offer MySql *or* MsSql for that price. So, for these low cost monthly hosting plans, you don't install any software, you simply choose the services you want just like choosing food at a restaurant.
And keep in mind as the other poster suggested, some of these companies will also allow you to copy (upload) a mdb file to your web site and use that. You of course then will be using ADO code in whichever programming language you decide for that web site to read that data from the mdb file.
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 15 May 2008 17:47 GMT Albert, I will read up about Sharepoint services to see if it is relevant for my needs.
You made the statement "keep in mind as the other poster suggested, some of these companies will also allow you to copy (upload) a mdb file to your web site and use that. You of course then will be using ADO code in whichever programming language you decide for that web site to read that data from the mdb file". The programming language I associate with a website is HTML but I suspect that you are referring to PHP or ASP. Is that correct?.
It seems to me that if I have to go to the trouble to find or write a script that enables an online form to dump its data into a database I might as well do it for SQL Server (rather than just an mdb file) so that I could then web enable my whole application by using Access only as a front end.
Thanks again for so kindly providing me with all this assistance.
David
> Of course another possibility is to use share point services, and open up > a place where users can drop their files on the server. (and, with access [quoted text clipped - 75 lines] > that. You of course then will be using ADO code in whichever programming > language you decide for that web site to read that data from the mdb file. Albert D. Kallal - 15 May 2008 21:31 GMT "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
> The programming language I associate with a website is HTML but I suspect > that you are referring to PHP or ASP. Is that correct?. HTML is not a programming language. You can't declare variables, run loops, and call subroutines. It is ONLY a display system. (they could have used word for the web, but they used HTML). So, HTML is how you set your fonts size and display text on a web site, but you still have to write code to generate that HTML (or, just like a word doc, or PDF, or HTML, you can have a static web site that don't run code). Remember, word has a "save as HTML" option built in. You would not say that when you "save as HTML" in word you now generating computer code.
> It seems to me that if I have to go to the trouble to find or write a > script that enables an online form to dump its data into a database I > might as well do it for SQL Server (rather than just an mdb file) so that > I could then web enable my whole application by using Access only as a > front end. Yes, that is a good idea. if you had SharePoint running, that would be another good canadate here...
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 15 May 2008 23:50 GMT Albert, I tried to read a few of Microsoft's web pages describing SharePoint. There was a lot of enterprise level marketing guff about all the miraculous benefits but very little to help you understand exactly what it is and where the software should be installed and by whom. Microsoft also appears to sell several different products all using the SharePoint name, which adds to the confusion. One of those products is 'Microsoft Office SharePoint Server 2007 for Internet Sites'. Is that the relevant one in this context?
Because this software runs under Windows Server 2003 and is associated with the Internet, I assumed that that this is another service you could buy from an ISP for a monthly fee (like SQL Server). However, I have checked the websites of three different ISPs and none of them mention SharePoint. If SharePoint is only relevant to users with their own web server then it would certainly be much too expensive for me to consider.
If I were to go down the SQL Server route, which is looking more likely, do you know any good sources of scripts that would assist me to extract information from an online form and store it in an SQL Server database?
David
> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message > [quoted text clipped - 21 lines] > Yes, that is a good idea. if you had SharePoint running, that would be > another good canadate here... Albert D. Kallal - 16 May 2008 00:50 GMT > Albert, > I tried to read a few of Microsoft's web pages describing SharePoint. [quoted text clipped - 5 lines] > SharePoint Server 2007 for Internet Sites'. Is that the relevant one in > this context? Yes. MOSS as it often called is what you would be looking at. I recommend SharePoint just in case you/they had it running already (it would be a crying shame for me to give you all this information, and you had a share point server sitting around that was available for your use).
You can also look at and try office live, it is free right now:
http://www.officelive.com/default.aspx
> If SharePoint is only relevant to users with their own web server then it > would certainly be much too expensive for me to consider. There are companies offering hosted share point systems.
> If I were to go down the SQL Server route, which is looking more likely, > do you know any good sources of scripts that would assist me to extract > information from an online form and store it in an SQL Server database? Those so called script or programming languages is a decision that you make yourself. Just like when I run a computer, I can choose to use MS access, SQL server, FoxPro, or Sybase, Filemaker etc.
I can use c++, vb, vb.net etc to write code in. You have the same wide range of choices when you write and build software on a web site. You have as many variety of programming languages and web servers that you can choose use just like on the desktop. You just have to choose one particular programming language (likely the one that your most familiar with, or one that suits your style of development).
I think in your case your best bet would be visual studio and .net. There are free editions available for download.
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 16 May 2008 12:39 GMT Albert,
> I recommend SharePoint just in case you/they had it running already (it > would be a crying shame for me to give you all this information, and you > had a share point server > sitting around that was available for your use). We are an amateur photographic society that happens to run an annual international photo competition. We have no servers of any kind.
> You can also look at and try office live, it is free right now I had never heard of Office Live before, so thanks for that suggestion. It appears to offer a simple website generation tool and some business services based on SharePoint. I've signed up for the Small Business version but have not yet found an Access related facility (it must be in there somewhere because I viewed a Microsoft video on Office Live that makes a passing reference to Access). I will keep looking....
> I think in your case your best bet would be visual studio and .net. There > are free editions available for download. When I asked you for any good sources of scripts that would assist me to extract information from an online form and store it in an SQL Server database, I was really meaning pre-written scripts rather than suggestions for languages in which I could write my own. For example, at the link http://mindpalette.com/scripts/index.php#PFAnchor, you will find a PHP script called ProcessForm that can take info from an online form and store it in a MySQL database. I was hoping to find something similar for a Windows hosting environment that connected to SQL Server. I've found a few sources of ASP scripts so maybe I'll find something suitable there.
I'm trying to put off the day when I have to learn all about Visual Studio and .Net. Access itself swallows enough of my time as it is!
Best regards, David
David Anderson - 16 May 2008 23:52 GMT Despite what I said at the end of my last post, I've now downloaded Microsoft Visual Web Developer 2008 Express Edition and SQL Server 2005 Express Edition and I'm working my way through a 1 hour training video entitled 'Connecting your Web Application to SQL Server Express Edition' (http://www.microsoft.com/sql/editions/express/learnvideos.mspx#lv2). It looks like I might be getting somewhere!
David Anderson - 19 May 2008 15:29 GMT Albert, I have now done some testing to prove that I could store data entered via an online form in an SQL Server Express database. A SQL Server solution therefore was looking promising, mainly because of my other wish to enable the users in my own group to use this SQL Server database as a web-located back end to my Access 2003 front end - an approach that you described below as "a good idea".
However, I did have some concerns about response time in this scenario and I asked questions about this in the sqlserver forum. (http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/t hread/514a46576a5f5492) I have had a response there that throws doubt on the wisdom of this approach. Has your own experience with accessing a SQL Server back end over the Internet been more positive?
David
> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message > [quoted text clipped - 6 lines] > Yes, that is a good idea. if you had SharePoint running, that would be > another good canadate here... Albert D. Kallal - 21 May 2008 01:12 GMT "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
> However, I did have some concerns about response time in this scenario and > I asked questions about this in the sqlserver forum. > >(http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/t hread/514a46576a5f5492) > I have had a response there that throws doubt on the wisdom of this > approach. Has your own experience with accessing a SQL Server back end > over the Internet been more positive? I have personally talked to some people that created workable performance over a dialup modem with MS access to SQL server.
You certainly have to pay attention to the amount of data you transfer into a form. However any good access developer will do that even when not using SQL server.
For example if you have a table with 5000 records in it, you must never ever open up a form bound to that table of that many records *without* using the where clause to restrict the number of records loaded.
In the case of a web based application, what's the difference between displaying a web page that gets transferred to your computer with some fields of information to be displayed, as opposed to telling SQL server to send one record down the wire, and you display locally? (The answers is very little if any difference here and performance). The problem is you'll never see that website display 5000 records in a webpage will you?
Time and time again I see MS access applications that simply have a form bound to a large table, you open the form, and then you let the user have at it with this large table with thousands of records. This type of design approach is bad in MS access, or in ***any*** development environment. One of the reasons why some many developers state that MS access doesn't perform well is not because MS access is any slower than vb or whatever when pulling data from SQL server, it's just that we tend to have poor designs that transfer a bunch of records into a form, and then say the person OK search the form.
SQL server doesn't know if you're using the vb.net, MS access, c++ etc, or even if a web site that's pulling data from SQL server. The server don't all of a sudden say "hey", this is MS access let's transfer data slower!. Things just simply don't work this way at all.
I recall some time ago I asked an 88 year old grandmother if it makes sense to download all the names into instant teller machine, and then have the person enter their account number? Or, is it better to simply ask for the account the data have the number first, and then download one account number? This old lady was able to perfectly and crystal clear answer this question correctly. If a old lady can answer this question, then why do I see so many ms-access applcations that go against the advice of a old lady who don't even work in IT?
So will the application perform well? the answers it depends, and if you have a design that limits the number of records transferred into a form, then just have no problem at all.
I think the more difficult problem to solve here is where you plan to put the pitchers, since you can't put them inside of MS access, and they'll likely be files placed on your web server. What this means is you have a difficult challenge to have those pictures related or displaed inside of an MS access form on the client side of this application.
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 21 May 2008 11:37 GMT Albert, I can't thank you enough for all your assistance. You are shedding a great deal of light on what is an unfamiliar topic for me. Because of comments elsewhere I was getting very worried about possibly spending a lot of time on converting my current Access/Jet app and then finding that response time over an Internet connection made it unusable. You are now offering me a clear focus for my upsizing work so as to make the performance acceptable.
I have been faced with the difficult decision about whether to pursue an Internet-connected SQL Server solution or replication via the Internet. The trade-off is between always having only one version of the data tables versus response time. Since either method would take me a lot of time to implement, my worst fear was going with SQL Server and then being forced to change to replication and thus taking twice as long to reach a workable conclusion! However, you have now given me the confidence to pursue an Internet-connected SQL Server solution.
I will now have to start the process of converting my Access mdb front end into an ADP and checking that I make full use of 'where' clauses. I will probably start by testing with SQL Server Express on my own PC before signing up for a Windows hosting agreement with SQL Server 2005. What about security with such a solution. Do I also need to learn all about VPNs?
BTW, there is no issue with the storage of digital images as it was never the intention to display the pictures on the website. The online entry form is intended to take the place of our previous paper entry form, which was posted to us together with the related prints or digital images on a CD. We will now have to design a process for linking the online entry form with the images received via other channels. We do have to consider ways to make it easy for digital images to be submitted electronically but I have not begun to think about that yet (though I will look into your earlier suggestions ref Flickr, etc).
Thanks again, David
> I have personally talked to some people that created workable performance > over a dialup modem with MS access to SQL server. [quoted text clipped - 47 lines] > difficult challenge to have those pictures related or displaed inside of > an MS access form on the client side of this application. Albert D. Kallal - 22 May 2008 05:31 GMT "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
> I will now have to start the process of converting my Access mdb front end > into an ADP No, you don't have to convert to a adp. You *can* do so, but I was suggesting you don't re-write your code, and you simply use linked tables.
if you have very little coding, or at least very little reocrdset code, then by all means consider a ADP, but you don't have to change to a adp, you simply use linked tables to sql server....
There's no question that with less effort, an ADP will generally performed better, especially when you're dealing with queries that join multiple tables together. Other than some sql performance issues, for the most part however it's not usually worth the effort and time to convert a well made and well done application from a mdb to a ADP.
 Signature Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com
David Anderson - 22 May 2008 09:18 GMT Albert, This is such a new subject for me that I didn't realise that using linked tables with SQL Server would remove the need to convert to an ADP. That would certainly be worth trying as I would get a feel for any performance issues much more quickly that way. I would still have to focus on the use of 'where' clauses but that is much less work than rewriting all my DAO code. If, after that, performance was still poor I would then have a better reason to invest the effort in an ADP rewrite. Thanks for that excellent suggestion.
What about my security concerns? Is this a situation where I need to exploit a VPN, yet another topic where I have little or no knowledge and experience?
I also asked about the the viability of connecting to SQL Server via the Internet on another forum. I've had very little response but one person has just told me that most ISPs will only allow you to connect to SQL Server from within their own environment, i.e. via a website they host for you, and will not expose SQL Server to the Internet. In other words, SQL Server will be hidden behind the ISP's firewall. What is your reaction to this comment?
David
> "David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message > [quoted text clipped - 13 lines] > however it's not usually worth the effort and time to convert a well made > and well done application from a mdb to a ADP. Wayne-I-M - 14 May 2008 14:06 GMT Sorry just re-read your post.
Go to the pay-pal site and open the merchant section. Fill in your details then go to "create the button" page. The code will be written for you. Just paste in the script where you need it on your form.
Note - it will take a few weeks to set up the pay-pal account to work OK. They will send a small amount to an account then you need to fill in another form to verify this amount.
You should "not" use the asp form to upload the pictures. It is best to link to them (if they are online) - format a field as a hyperlink. If not then you need to find another method of loading them, or your DB will get to a silly size "very" quickly. I would suggest than you only take scanned pictures up to a certain limit - use another form for this to be emailed to you (outlook is good for this).
Something like this (which will open your default mail programme)
<script language=javascript> <!-- var AandB = "some"; var CandD = "person"; var EandF = "."; var GandH = "name"; var IandJ = "."; var KandL = "com"; var linktext1 = "Se"; var linktext2 = "nd"; var linktext3 = " e "; var linktext4 = "M"; var linktext5 = "a"; var linktext6 = "i"; var linktext7 = "l"; var subj = "Picture attached" var bod = "Attach your picture here." document.write("<a href=" + "mai" + "lto:" + AandB + "@" + CandD + EandF + GandH + IandJ + KandL + '?subject=' + escape(subj)+ '&body=' + escape(bod) +">" + linktext1 + linktext2 + linktext3 + linktext4 + linktext5 + linktext6 + linktext7 + "</a>") //--> </script></td>
Use this instead of your e mail address or you will get LOTS of spam very quickly
- Of course change this to your real (broken up) e mail address "Don't" use the @ sign in this code or you will be spamed.
var AandB = "some"; var CandD = "person"; var EandF = "."; var GandH = "name"; var IandJ = "."; var KandL = "com";
 Signature Wayne Manchester, England.
> I have created an Access 2003 database solution for an annual international > photographic competition. In it's first year of operation, all the entry [quoted text clipped - 17 lines] > > David
|
|
|