Javascript page connecting to MS Access

@austenrAug 17.2007

I have a jscript page a co-worker made that has a search box on it. It needs to be able to search all the tables in my Access mdb with the search string and if a match is found, retrieve the fields for the web page. I really dont havfe a clue how to start doing something like this and it has to be done in a week. Anyone have a starting spot they can suggest? Thanks

to post a comment

JavaScript

8 Comments(s) _↴

@Angry_Black_ManAug 17.2007 — #this may work, but i havent tested it.. just something i pulled from a google result. this wouuld only work in IE:

&lt;html&gt;
 &lt;head&gt;
 &lt;title&gt;Entitled Document&lt;/title&gt;
 &lt;script language="JavaScript"&gt;
 function getdetails()
 {
 i=1;
 var cn = new ActiveXObject("ADODB.Connection");
 var strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:acrotips.mdb;"
 cn.Open(strConn);
 var rs = new ActiveXObject("ADODB.Recordset");
 var SQL = "select * from Customers;"
 rs.Open(SQL,cn);
 
 
 rs.MoveFirst
 while(!rs.eof)
 {
 document.write( "&lt;center &gt;&lt;h5 color='blue'&gt;"+i,rs('FirstName'),rs('LastName'),rs('SSN'),rs('Comments')+"&lt;/h5&gt;&lt;/center&gt;");
 rs.movenext;
 i=i+1;
 }
 rs.Close();
 cn.Close();
 
 
 }
 
 &lt;/script&gt;
 &lt;/head&gt;
 &lt;body bgcolor="#FFFFFF"&gt;
 &lt;center&gt;
 &lt;h2 style="font-family:algerian; color:#A06D3F"&gt;Want to See the daetails!! &lt;/h2&gt;
 &lt;input type="button" value="Click here" onclick="getdetails()"&gt;
 &lt;/body&gt;
 &lt;/html&gt;

@austenrauthorAug 17.2007 — #Thanks. I think I follow it but where is the part where the search input from the webpage searches the tables in the Access DB? Sorry, not that familiar with jscript.

@Angry_Black_ManAug 17.2007 — #i tested it, and it works just fine. that script was just a random database script i pulled from the web. it would be an extremely alarming coincidence that would probably signal the second coming of Christ if it was already designed to do what youre asking about...

all that to say that you are diving headlong into some pretty complicated stuff. not having any javascript experience is a massive red flag. you may find someone who is totally willing to write the entire script for you, but im not that guy. i teach instead.

but heres the breakdown:

&lt;body bgcolor="#FFFFFF"&gt;
 &lt;center&gt;
 &lt;h2 style="font-family:algerian; color:#A06D3F"&gt;Want to See the daetails!! &lt;/h2&gt;
 &lt;input type="button" value="Click here" onclick="getdetails()"&gt;
 &lt;/body&gt;
 &lt;/html&gt;

this part is pretty much where you would design your search input. youd create a text field that would contain your search string, and then a button, like the one above, that invokes the database query.

<i>
 </i>function getdetails()
 {

is where the database query function is defined

<i>
 </i>i=1;

is a counter that increments as we loop through each record pulled from the table in our database.

<i>
 </i>var cn = new ActiveXObject("ADODB.Connection");

creates an object to allow us to connect to the data store. now we can run methods associated with the database to pull information

<i>
 </i>var strConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source = D:acrotips.mdb;"

is the database connection string. this one is formatted for a microsoft access database

<i>
 </i>cn.Open(strConn);

opens the database with the connection we established before

<i>
 </i>var rs = new ActiveXObject("ADODB.Recordset");

the "result set" is created. this will contain the results of our query that will soon be invoked.

<i>
 </i>var SQL = "select * from Customers;"

the actual SQL query language is then stored (and only stored) in a variable for easy reference

<i>
 </i>rs.Open(SQL,cn);

now we run the actual SQL query against the connection we made above. these results will only be available to the recordset object we created above.

<i>
 </i>rs.MoveFirst

on to the first record.

<i>
 </i>while(!rs.eof)
 {

this loop ensures we go through all the records in the result set, and nothing more. if there are 6 records, and then we loop once more, this will not try to pull information from the result set because we've reached the end of the data (EOF).

<i>
 </i>document.write( "&lt;center &gt;&lt;h5 color='blue'&gt;"+i,rs('FirstName'),rs('LastName'),rs('SSN'),rs('Comments')+"&lt;/h5&gt;&lt;/center&gt;");
 rs.movenext;
 i=i+1;
 }

if there is a record during this loop, we spit out the information based on the name of the field. also, we increment the i variable we set before. the i stands literally for the record number.

<i>
 </i>rs.Close();
 cn.Close();
 }

once weve read all the information that was contained in our recordset, we close it. and then since this is all we want from the database, we close the database as well.

so to use your search string, youd have to pass the string to the "getDetails()" function. then youd have to incorporate it into your SQL query string (instead of simply grabbing all the fields). oh, and remember that this is a javascript forum and not an SQL forum, so youd have to figure that one out somehow. once you grab your query results, then you just output to the page. the way it is done in the code does the trick in a way.

but if after reading all this, you are overwhelmed, then im sorry. you have a great deal to now learn to get this to work. javascript, html, sql, activex. id recommend http://www.w3schools.com on all those (except activex... i dont think they cover that?)

@felgallAug 18.2007 — #That of course is using JScript and ActiveX and therefore only works on Internet Explorer. That will be okay if it is for your own use or an intranet where everyone is using IE but will not work on the internet where people use whichever web browser they like. If you want it to work on the internet then you will need a server side solution as JavaScript has no access to files at all.

@Angry_Black_ManAug 18.2007 — #That of course is using JScript and ActiveX and therefore only works on Internet Explorer.[/QUOTE]

this may work, but i havent tested it.. just something i pulled from a google result. this wouuld only work in IE:[/quote]

truer words were never spoken

@austenrauthorAug 20.2007 — #The app is an intranet app. Would I be better off using something like PHP?

@Angry_Black_ManAug 20.2007 — #of course. ANY server side solution is 10000x better. not only is this solution an IE-only solution, it is dependent on a client-side technology that can be turned off or otherwise circumvented.

PHP gives you transparent-to-the-client access to your database. everyone that comes to your page will get the same results since the server handles the processing. it will no longer be platform dependent.

but it is still a major undertaking regardless of the route you use. you still need knowledge of the server side language you choose, as well as SQL knowledge (and how to make the two play together)

@austenrauthorAug 20.2007 — #Yea I know. ?

Also in #JavaScript _↴

Problem in implementing ajax Help - Form Trouble Javascript menu does not show up inside a frame set

Success!

Help @austenr spread the word by sharing this article on Twitter...

Tweet This

Javascript page connecting to MS Access

8 Comments(s) _↴

Also in #JavaScript _↴

Success!

Social

Version

Javascript page connecting to MS Access

8 Comments(s) ↴

Also in #JavaScript ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

8 Comments(s) _↴

Also in #JavaScript _↴