inserting rows into database - no access installed

[darrenallen]

This is probably an odd request but I will do my best to explain.
In work I need to develop webpage to transfer some data to a database (insert a new row on the database with this data) but the users do not have microsoft access installed.
They do not need to pull back any data, just insert new data into the database.
I know how to create the appropriate form on the web page but is it possible to transfer the fields to a database if they don't have Microsoft Access installed. It has to be Access as our works only use this.
An initial starting point would be great, if it is possible.
Any help would be grateful.

 

[HTMLHugo]

could maybe write the data to a csv file and import it once in a while ?

depends on the overall project

think we'd need some more information

 

[Ed]

By the nature of your query I'd use this to get going pretty quickly.

 

[darrenallen]

Thats the way I have it running at the moment.
Just thought it would be better to have the data sent to the database direct.
Forgot to mention that there are multiple users.
It would literally be a case of a sending fields to eg. field1, field2, field3..... to the database as a new row. One of my colleagues was about to start work on it but left the company before he started, typical.

 

[darrenallen]

I looked at visual studio web developer but it far exceeds what I need. It looks to be more than I could cope with.
All im after is a simple web page with a few drop down boxes and then to send the results to a database, as a new row.
At the moment I have the same thing set up on an excel sheet that sends the results to a new row on another spreadsheet.
It would be easy enough if everyone in work had access installed but they won't pay for the license.
Doing the webpage is fine,already done, but getting it sent to a database is tricky. But i'm sure it can be one.

 

[oneilldo]

I would just use PHP and MySQL as both are free

 

[JIMA]

Hi,

Not sure if you're already using this, but here's how I'd tackle the problem...

You could write a Servlet that takes as input the data you need to insert onto the database. I'd send this in a SOAP message as an XML document, though there are other ways of doing it. The servlet runs on a server that everyone can see and send to.

The client-side application would then send the data to the servlet (using http etc). The servlet unpacks the data from the SOAP message, connects to the database, and inserts the data into the tables.

Sending the data in XML allows you to structure the data so you can more easily handle situations where multiple tables need to be updated. You could also validate the XML doc sent using a schema/DTD so you can tell if the data you're about to insert is valid.

This arrangement also gives you more freedom in what language you write the Servlet in. For example, you could write it in Java with a .net client-side app sending it the data. There are plenty of examples of Java servlets in the Sun tutorials.

Although it sounds quite complex it shouldn't be hard to write in whatever language you use as both Java and Microsoft have decent XML, SOAP, Servlet facilities.

Hope that helps.
Jim

 

[JimmyEatWorms]

Jima, isn't that unnesecarily complicated just to add a row into a database? If you're using an IIS server then you can write a fairly small and simple ASP page to insert the data.

Something like this?

 

[JIMA]

That's interesting. I don't use IIS or ASP so I'll have a look and maybe learn a bit more about it.

Having said that, the Servlet wouldn't be much more complex than the ASP described in the link, the only additional bit required would be getting the data to insert out of the SOAP message.

Jim

 

[Ed]

JimmyEatWorms has the most valid response here; I can't believe the others. As for Visual Studio Express, once you get past the OMFG of the interface it actually makes a lot of sense and solves the problem you have in about 5 mins but given the situation and assuming the Windows platform, you'll need to write an ASP page as Jimmy links to but instead of writing to an Access database the clients won't use you can persist the data to XML as it's native to ADO which is the object used to hold the data. There's loads of solutions to this so best start here. The beauty of the XML format is interoperability which means Access can import/export the data from the XML file you generate.

 

[HTMLHugo]

heh completely misread the OP

you do have a machine with IIS + access on it yes ?

if yes :
setup an access dsn though control panel / administrative tools / datasources

then use asp code as below to write the row to a database.

the users do not need access to submit a row

 

[darrenallen]

JimmyEatWorms, your solution is perfect, does the job exactly as I need.
You're a lifesaver, many thanks.

 

[darrenallen]

I spoke too soon.
The route JimmyEatWorms links to is the right thing for my purpose but the users will not have a Personal Web Server or IIS installed.
The code seems right, but is there a way to put this into javascript or vbscript on a single webpage and still keep the functionality there.
I'll submit any code that i've done but effectively it is a form on a webpage that needs the data sent to a database stored on a shared drive. The users will not have any sort of database software installed on their machines.
Sorry for taxing you further.

 

[noob]

Darren, first you need a webserver whether you setup a machine in work to act as a webserver or pay a small amount per month to a webhost. I would definately recommend using ASP.NET, you could do what you want within a few hours, easily! Using Microsoft Visual Web Developer you could drop a form view onto a page and you're done. All you do is put your website on the host or your PC in the workplace as well as the .mdb (access) database and any user can access this.

http://www.asp.net/learn/videos/default.aspx?tabid=63#beginners - These videos are excellent.

Look at the first couple and then Lesson 7: Databinding to User Interface Controls and Lesson 8: Working with the GridView and FormView and you will surprise yourself what you can do.

 

[JIMA]

Do you have (or could have) a web-server running on the server you're going to use?

Alternatively, could you use an ActiveX control, as shown here

I'm no expert on Javascript but that looks like it could do the job.

Hope that's of some help.
Jim

 

[darrenallen]

I've finally got there but i've got stuck on such a stupid point I feel embarressed to ask.
My code is currently, as a javascript sub is:

var adoConn = new ActiveXObject("ADODB.Connection");
var adoRS = new ActiveXObject("ADODB.Recordset");
adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/a/tblTest.mdb");
adoRS.Open("Select * From tblTest", adoConn, 1, 3);
adoRS.AddNew;

It does everything I need i.e. updating the database with the required information but as it stands the datasource path relates to a database held on the root of the "C" drive. I've tried to change the entry from '/tbltest.mdb' to another drive and I cannot get the correct format for the string.

How do I get it to look at a specific drive/directory for the database??

 

[JIMA]

From reading some more stuff it would seem that ADO cannot access remote databases. Instead, have a look at this

and possibly this

Both of these describe ADOX (the second link in much more detail) which seems to allow remote database access. Whether this works from Javascript on the client I don't know but it's probably worth a go.

Jim

 

[darrenallen]

Thanks for the advice but it is probably easier for me to make sure the database is located on the same shared drive as the webpage. This will probably solve my last issue.
Thanks all the same.