Handling Remote Data Entry

[Mihail]

Hello !

I have a database (Access 2007).
In this database a form (a table) must be filled each month.

Can I define an application in SQL Server to collect the input via Internet ?
I think so (but I need an answer to be sure).

The main question is if I can update my tables (.mdb) using the SQL Server's table.

I ask because I don't wish to "waste" my time now to learn SQL Server. (I know that is not a wasted time. But not NOW)

Thank you !

[nico5038]

For the internet data scraping you're better off in the MS SQL topic area.
about: 'The main question is if I can update my tables (.mdb) using the SQL Server's table.'

This is easy to achieve by defining an ODBC connection and use that to link an Access table to.

Use in the Start menu the "Data sources(ODBC)" application and define a connection. Next in MS Access select a new linked table and in the filetype combo select "ODBC" and your defined connection.

Nic;o)

[Mihail]

Thank you for reply, Nico !
Is the first time you answer to a question of mine, so I think that you are an expert to this area. Glad to meet you !

I understand from you (my English is not very good, so I am not sure) that MS SQL is a better choice for my problem. Is this your advice ?

Thank you again !

[Mihail]

After I post I google a little bit looking for MS SQL and I realized that is the same thing with SQL Server. Sorry for that, but I am more than newbie here.

[NeoPa]

Mihail:
Can I define an application in SQL Server to collect the input via Internet ?

This makes sense, but there are so many things it could mean - as the internet is a word which covers the connecting together of so many machines and networks. Even if the term 'Web' were used, this could still cover an enormous array of possibilities.

Nico's answer reflects an expectation that you're referring to web scraping - a technique whereby information is read from web pages as HTML by your program and parsed into data that makes sense. This may well be what you're thinking of, but it would help if you explained exactly what you mean by your question in this respect. Web scraping is a pretty advanced and particular subject and the code may need to be specific to the individual pages that you're expecting to scrape. General purpose web scraping can get very complicated indeed and is very hard to get reliably right (as there are so many different web pages in the world with so many different approaches used).

Email is another one of many options that could be described as 'via the internet'. This may, or may not, have pre-formatted data. You can see that without something more specific it is hard to deal very directly with your question.

PS. I also get confused sometimes over MySQL, MS SQL and SQL Server. The first two look very similar, but the second two are the same. Confusing.

[Mihail]

Hi, NeoPa.
While myself I don't know exactly what I am looking for is very difficult to explain.
I'll try but sorry if my poor understanding (combined with my poor English) will not allow you to understand what I mean.

Simplified, this is the sketch of real fact:
My database is designed to compute how much energy consume a house.

The structure of the database is:
First branch:
Towns -> Streets -> Houses -> Rooms -> Bulbs
Second branch:
DateOfReadings -> ReadBulb(ID_ReadBulb, ID_DateOfReadings, ID_Bulb, Energy)

There are a lot of "readers" for that. One "reader" for each street. They read the energy for each bulb, write to the paper and come to the headquarter (with this papers). Here other employes fill in a form form_ReadBulb, for every bulb, the energy.
This form is based to ReadBulb table. It allow to select a street then to fill all readings for all bulbs from that street.

My purpose is to avoid extra times (wasted times) for all this employes. So I think that the readers can be easily trained to fill itself, at their home, a form with their "readings".
For that I need to connect they, somehow, to my database.
My "genial :))" idea is to define a simple application (maybe in SQL Server but not necessary) to do that.
After all I'll see how to update my database (Nico say that is possible. I'll ask later how if I can't teach myself)

How I see now the situation I'll need to have users (readers) accounts and passwords. Based on this accounts the form I'll use must filter data to show only bulbs from one street (one reader). But this is other story. Not for now.

For now I must start and I don't know from where.

Note please that collecting data via e-mail is the last option for me (I don't like MS Outlook and I wish to avoid using it as much as I can).

Thank you for interest !

[NeoPa]

So, you want the various employees to be able to enter the data they've made notes of from remote sites yes?

The two main ideas that come to mind are :

1. Design a web site yourself to allow them to enter the data directly into forms on the page. You would need to design it such that security is handled of course, as well as to ensure that the data entered is correctly identified as pertaining to the objects it is for (The correct bulb for instance). The main, and only, database would then be on the server and updated by the web page directly.
   
2. Design an Access database project consisting of an FE and a BE such that the user could run this at their remote location and then transmit the BE database compressed (and possibly encrypted) to the central point for incorporating into the main system. The main system would have a database with pre-built links to the client BE (the one transmitted) which would allow it to upload the data intelligently.
   
   As the compressed client BE file is simply a file, how you handle the transfer can be very flexible. Email would work, but so would an upload via a web page if that approach is preferred. In truth, there are various options for this.

I hope these thoughts are of some help to you.

[Mihail]

Yes, NeoPa. This is the final purpose.

I have no skills in web design. No HTML, no Java. So, if I chose to design a web sit I must start from zero. I don't know if it is possible to do that by using only the controls provided by a FREE site, without programming. FREE because this is a (very) small business and the money are a problem.

This problem (Remote Data Entry - thank you for help with title) do not burning me. So I I have time to stay in standby until I receive more ideas.
My skills are in VB, VBA, Excel, Access. Can I touch my purpose based on this skills (or very similar ?).
Any way, any idea is welcome.

Thank you in advance.

[NeoPa]

I think #2 fits into that category. Mostly Access, but somewhere you need to manage transferring the client BE database. Email could be used, or even FTP (as long as you can get someone to set up an FTP site for you such that you can make the uploaded files available on your central network). I have done work before to enable a database project to :

1. Enter the data.
2. Compress the BE file using the WinZip Command Line executable.
3. FTP the resultant compressed and encrypted file to an FTP site.

So, all that is possible from within Access using standard Windows executables and a single installable download from WinZip.

At the central end it only needs the FTP server to be set up to make the files available somewhere that is accessible to the network (It could prbably even be done using FTP if organising the files to be available locally were too much of a problem) and your code could process these files individually. It may involve each client file being renamed originally with a unique ID in the name to ensure multiple files don't clash, but changing the BE link for the central database is something I believe you've already dealt with (and if not then we certainly have done so here a few times - I believe Nico was one of the first to offer advice in that area if I remember correctly, though before your time).

In short, I believe such a project is certainly possible, if admittedly quite complex in places.