Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with clientside-only solution

    I am a govt. employee wanting to make a multi-user database. This is a replacement for the current solution:

    1. Single-user Excel sheet on a shared network folder.

    Here are my design limits:

    1. The system uses Windows XP only.
    2. I do not have communication with the network admins.
    3. I will never have permission to install servers anywhere. They are forbidden.
    4. I do not have permission to install any software on any computer.
    5. My group only has access to one shared network folder.

    I've brainstormed some solutions, including:

    A. Marrying a VB Application with SQLite, with the SQLite file sitting in the shared folder.

    B. Using how to use SharePoint to develop my application.

    C. Using Microsoft Access as a client-side GUI which connects to another .mdb on the shared folder.

    D. Using Internet Explorer with Javascript and ADO ActiveX Objects to manipulate records in a .mdb on the shared folder.

    I choose D. Choice A is out because I can not install any software development tools. B may not work for me because my database is heirarchical in nature and I want more control over the GUI. Same reason for C, and Access is a little micky-mouse.

    Amazingly, the security settings on IE allow the creation of ActiveX objects through Javascript. I can also develop with javascript because I have Wordpad. To test the code I can simply run it by launching the HTML. This allows users to open the same HTML file in the shared folder instead of distributing front-ends.

    The javascript must all be client-side, including all data manipulation. However, I am not concerned about security issues because only our group will be using the system, and all traffic is already contained within Navy networks. So I don't care if I use window.location for storing variables since it works for this case.

    Does anyone have anything to say about this? I'm looking for pointers, gotchas, guidelines, and any other, better solutions. Thanks.

  • #2
    Senior Coder jmrker's Avatar
    Join Date
    Aug 2006
    Location
    FL
    Posts
    3,077
    Thanks
    38
    Thanked 498 Times in 492 Posts

    Question Need more information and clarification...

    This is not a suggested solution, just a few more requirement questions about the project.

    1. Is this to replace the Excel worksheet or will you continue to use it?
    2. Are you expecting to change values within the database or is it for display purposes only?
    3. Can you use ajax functions to read text data or do you plan on using external JS files?
    4. How often will the database being used change contents?
    5. Are you limited to a certain browser or can you use some of the new HTML5 features?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    jmrkr: I can answer one of those question, as he already answered it.

    > 3. Can you use ajax functions to read text data or do you plan on using external JS files?

    He said he can *NOT* install *ANY* server-side software, so AJAX is clearly out of the question.

    *******

    I'm not clear on how your question 5 is relevant.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    My comments: It's an ugly solution (option D) but I agree with you that it is likely the only viable one.

    I might only note that, since you will be forced to use MSIE, anyway, you might consider coding this in VBScript instead of JavaScript. Reason: There are a few ADO capabilities that are clumsy to use in JavaScript, primarily because they were designed back in the mid 1990s and were directly intended for use with VB and VBScript. But if you feel more comfortable with JavaScript, by all means stick with it.

    The one issue you will need to watch out for is making sure you don't leave connections open any longer than needful. Access is *NOT* a true multi-user database, and it's easy to have one user that leaves a connection open end up locking out all other users.

    So create the ADODB.Connection object once per page, say, but only call Open on it when you are ready to perform some operations and then call Close as soon as the operations are done. And make sure you trap errors (i.e., use try...catch... with JS coding) so you can close the connection if there is an error.

    Other than the caveat about connections, I see no reason this can't work just fine.

    Oh...you *will* have to be sure that all users have read/write access to the shared folder containing the MDB file. Will this violate any of the strictures you are working under?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    By the by, if you didn't know, you can *ALSO* treat your existing Excel file as a database, using the JET OLEDB driver.

    That is, you can open an ADODB.Connection to the Excel file and perform SQL queries on it, using ADODB.Command and/or ADODB.Recordset.

    I don't know if that buys you anything useful or not, but remember that the possibility is there.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jmrker View Post
    This is not a suggested solution, just a few more requirement questions about the project.

    Q1. Is this to replace the Excel worksheet or will you continue to use it?
    Q2. Are you expecting to change values within the database or is it for display purposes only?
    Q3. Can you use ajax functions to read text data or do you plan on using external JS files?
    Q4. How often will the database being used change contents?
    Q5. Are you limited to a certain browser or can you use some of the new HTML5 features?
    A1. This is to replace the Excel worksheet.

    A2. I am expecting changes. Users can select, update, insert, and delete.

    A3. I'm an EE with pockets of computer science knowledge, and confidence to expand them. I am new to javascript. I am unfamiliar with AJAX. From the Wikipedia article's first paragraph, it seems that AJAX needs a server to implement, so I skipped researching it. So far I've had success using the modified preorder tree traversal algorithm to display data in a table generated by jQuery's collapsible/expandable tree table. I've done some PHP programming in the past, so my approach is to load HTML content on the $(document).ready function, and pass variables to other pages through the window.location (since I have no server). I am currently working on the code for form population, validation, and submission.

    A4. The database will be updated many times per minute at most, but the number of simultaneous users is at most 5.

    A5. Help->About Internet Explorer says: Version 7.0.5730.13. This is all I can use, no plugins or modifications allowed.

    I am also novice with standard practices with this type of work. I usually do what I need to do to get it to work properly.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    One of the really nice operations available with ADODB.Recordset is the GetRows method.

    This returns a VBScript-style two-dimensional array of all the data in the recordset. It's quite fast and so a code sequence such as
    Code:
        open connection
        do the SQL query
        call GetRows on the recordset
        close the recordset
        close the connection
    can be done in (typically) milliseconds and you will minimize any contentious locking problems.

    But as noted, the result is a VBScript array.

    JScript includes a special object for handling VBScript arrays, named (what else?) VBArray.

    So you can do:
    Code:
         conn.Open();
         var rs = conn.Execute( sql );
         var vba = rs.GetRows();
         rs.Close();
         conn.Close();
         var jsArray = vba.toArray();
    That converts the VB 2-D array to a JS 1-D array.

    Exampple: Suppose the recordset had 3 records and each record had 3 integer field.
    So after calling GetRows, the vba variable contains the 2D array ((1, 2, 3), (4, 5, 6), (7, 8, 9)).
    After translation via toArray, the JScript array contains: [1, 2, 3, 4, 5, 6, 7, 8, 9].

    That's not the most convenient of conversions, but you can see that it wouldn't be hard to write some functions to pass that data to your jQuery code for display.
    Last edited by Old Pedant; 01-03-2012 at 08:16 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    My comments: It's an ugly solution (option D) but I agree with you that it is likely the only viable one.

    I might only note that, since you will be forced to use MSIE, anyway, you might consider coding this in VBScript instead of JavaScript. Reason: There are a few ADO capabilities that are clumsy to use in JavaScript, primarily because they were designed back in the mid 1990s and were directly intended for use with VB and VBScript. But if you feel more comfortable with JavaScript, by all means stick with it.

    The one issue you will need to watch out for is making sure you don't leave connections open any longer than needful. Access is *NOT* a true multi-user database, and it's easy to have one user that leaves a connection open end up locking out all other users.

    So create the ADODB.Connection object once per page, say, but only call Open on it when you are ready to perform some operations and then call Close as soon as the operations are done. And make sure you trap errors (i.e., use try...catch... with JS coding) so you can close the connection if there is an error.

    Other than the caveat about connections, I see no reason this can't work just fine.

    Oh...you *will* have to be sure that all users have read/write access to the shared folder containing the MDB file. Will this violate any of the strictures you are working under?
    I didn't consider using VBScript, but since I now have a working and beautiful jQuery table I might as well stick with it. The only speed bump I did run into so far is along the lines of what you mentioned. Executing an ADODB.Command object (for update,insert actions) is faster than creating a recordset object, but when called by javascript generates a message box that isn't avoidable with the Navy MSIE settings. The only other solution is to update the database with pure SQL strings. This isn't awful though, because again, I'm not concerned about hackers. I just have to make sure I add #'s for dates.

    I also want to be aware of user collisions, so I want to limit update and insert statements to as few as possible. Any advice on this from seasoned developers? I figure the change of a collision is very low.

    When I used MS Access a long time ago, the databases would grow huge after moderate use. Should I be concerned about this?

    I guess basically I don't trust the stability of an MS database, even sitting by itself. Can anyone make me feel better about it?

  • #9
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    One of the really nice operations available with ADODB.Recordset is the GetRows method.

    This returns a VBScript-style two-dimensional array of all the data in the recordset. It's quite fast and so a code sequence such as
    Code:
        open connection
        do the SQL query
        call GetRows on the recordset
        close the recordset
        close the connection
    can be done in (typically) milliseconds and you will minimize any contentious locking problems.

    But as noted, the result is a VBScript array.

    JScript includes a special object for handling VBScript arrays, named (what else?) VBArray.

    So you can do:
    Code:
         conn.Open();
         var rs = conn.Execute( sql );
         var vba = rs.GetRows();
         rs.Close();
         conn.Close();
         var jsArray = vba.toArray();
    That converts the VB 2-D array to a JS 1-D array.

    Exampple: Suppose the recordset had 3 records and each record had 3 integer field.
    So after calling GetRows, the vba variable contains the 2D array ((1, 2, 3), (4, 5, 6), (7, 8, 9)).
    After translation via toArray, the JScript array contains: [1, 2, 3, 4, 5, 6, 7, 8, 9].

    That's not the most convenient of conversions, but you can see that it wouldn't be hard to write some functions to pass that data to your jQuery code for display.
    oooOO. Is this superior to, say:

    Code:
    //Generate recordset and populate all fields with matching field names
    var cn = new ActiveXObject("ADODB.Connection");   
    var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "+warg.dbPath;					
    var rs = new ActiveXObject("ADODB.Recordset"); 			
    try {
    	cn.Open(strConn)
    	try {
    		rs.Open(SQL, cn);					
    		var elem = document.getElementById('form1').elements;
    		for(var i = 0; i < elem.length; i++)
    		{
    			if (elem[i].name.length>0) {
    				try {elem[i].value=rs(elem[i].name);}
    				catch(err) {}
    			}
    		}					
    		rs.Close();
    		cn.Close();
    	}
    	catch(err) {
    		tblHTML = "Could not execute SQL statement: "+SQL+"<br>";
    		cn.Close();
    	}
    }
    catch(err) {
    	tblHTML = "Could not connect to database "+warg.dbPath+"<br>";

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    > When I used MS Access a long time ago, the databases would grow huge after moderate use. Should I be concerned about this?

    Yes and no. Just watch the growth for a while and then come up with a schedule for running a "compact and repair".

    You can do compact and repair from ADO, but I have found that to fail on rare occasions. So if you go that route, I'd recommend that you first use the Scripting.FileSystemObject to make a backup of the ".mdb" file. If you do the compact and repair from within Access [that is, run Access itself, after hours, say] I've never had it fail. But I'm still paranoid enough that I always make a copy of the ".mdb" file first.

    I have one Access DB that runs around 85MB and will occasionally grow to 120MB or so. I run compact and repair on it about once a week and it's been running for 2 years now.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    > Is this superior to, say: ...

    Yes, but if you only expect one record it wouldn't make that much difference. And keeping the JS as simple as you show there would probably be worth minor performance hit.

    I was envisioning displaying a grid of dozens or more records, each with several fields. *THEN* it would be worth using GetRows.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll upload screenshots later in the process to show you. That should help with your comments. Thanks!

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Actually, your code there is hiding a potentially nasty bug.

    Suppose that the error occurs on the line in red below:
    Code:
    		rs.Close();
    		cn.Close();
    	}
    	catch(err) {
    		tblHTML = "Could not execute SQL statement: "+SQL+"<br>";
    		cn.Close();
    	}
    }
    catch(err) {
    	tblHTML = "Could not connect to database "+warg.dbPath+"<br>";
    }
    So you'll get the "Could not execute SQL statement" error, even though the SQL may have executed perfectly okay, and then (since the first attempt to close the connection failed and we can assume the second one will also) you will replace that error with the "Could not connect to database" error.

    If you want to be really and properly paranoid, how about:
    Code:
    function fillFormFromSQL( SQL )
    {
        //Generate recordset and populate all fields with matching field names
        var cn = new ActiveXObject("ADODB.Connection");   
        var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "+warg.dbPath;					
        try {
            cn.Open(strConn)
        } catch( err ) {
            return "Error connectiong to database: " + err.toString();
        }
    
        try {
            try {
    	    var rs = cn.Execute( SQL );
            } catch( err ) {
                return "Error retrieving record: " + err.toString();
            }
    
            try {
                var elem = document.getElementById('form1').elements;
                for(var i = 0; i < elem.length; i++)
                {
                    if (elem[i].name.length>0) {
                        elem[i].value=rs(elem[i].name);
                    }					
                }
            } catch( err ) {
                return "Error retrieving fields from record: " + err.toString();
            }
        } catch( err ) {
            return "Unexpected error: " + err.toString();
        } finally {
            try {
                rs.Close();
            } catch( ignored ) { }
            try {
                cn.Close();
    	} catch(err) {
                return "Error closing connection: " + err.toString();
            }
        }
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd like to create a .js file that contains a function:

    UpdateTable(conn,doc)

    where conn is an ADODB.COnnection object, and doc is some way of passing a form (or document, etc).

    The function will loop through each form element and construct an INSERT or UPDATE sql statement based on the form element values. The "id" of each element will specify "string","date","bool","number" for formatting.

    How to I pass a document or form to a function?

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Lots of ways.

    One good one: Do it via the onsubmit of the <form>.

    Code:
    <form onsubmit="UpdateTable(this); return false;">
    ...
        <input type="submit" value="Update"/>
    </form>
    And then:
    Code:
    function UpdateTable(form)
    {
        for ( var e = 0; e < form.elements.length; ++e )
        {
            var formField = form.elements[e];
            ...
        }
    }
    BUT...

    Remember that id's must be unique on a page. Names don't have to be. So if you have, for example, a set of radio buttons or checkboxes they can indeed all have the same name. You'll have to handle them slightly special in the UpdateTable function.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •