Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
VBScript / Javascript accessing local Microsoft Access Database

Answered VBScript / Javascript accessing local Microsoft Access Database

  • Saturday, November 17, 2012 8:54 AM
     
     
    Sign In to Vote
    0

    Hello Experts,

    1. I plan to make a simple web page with Javascript / VBscript embedded in it.
    2. Script controls what text/data is going to be displayed in text-area (no other HTML element is present) from MS-Access table. (on LOCAL MACHINE only)
    3. Tried a bit, but getting confused with various methods of connection (DAO, ADO, Access Object etc).
    4. There seems vast difference in opening a connection to a database and opening a database itself.
    5. With VBA it was fine, but with VBScript / Javascript I have found no success.
    6. Could any one share some code about :
    7. Passing information to and fro from Access to HTML (text area), and calling Access object like Macros, Forms, SP etc.

    VBA Code : (Used for opening same database in which this module was written)

    1. Dim objConn As New ADODB.Connection
      Dim objRec As New ADODB.Recordset

      Set objConn = New ADODB.Connection

      Set objConn = CurrentProject.Connection
      Set objRec = New ADODB.Recordset

      strSql = "tblMain_Bank"
      objRec.Open strSql, objConn, adOpenDynamic, adLockOptimistic, adCmdTable

    2. How do I put desired database address (Access file path) in place of CurrentProject.Connection

    With regards : Ajay Check

     

All Replies

  • Saturday, November 17, 2012 1:56 PM
    Moderator
     
     
    Sign In to Vote
    0
     
  • Saturday, November 17, 2012 2:46 PM
     
     
    Sign In to Vote
    0

    Hi, AbqBill

    1. My problem revolves around Ms-Access (database) and Vb-Script / Java-Script (scripting).
    2. That's the reason I have posted same question, in Scripting Guys and Ms Office Access forum only.
    3. No matter, wherever, I get answer, I will mark them both as solved with appropriate note.
    4. So, apart from posting question, simultaneously in two forums, what I have missed with "way of asking questions in forum".

    With regards : Ajay Check

     
  • Saturday, November 17, 2012 2:54 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hi,

    You cannot access local files on the local file system for security reasons. To work around this, you can write your application as an HTA instead.

    Bill

    • Marked As Answer by Ajay Check Saturday, November 17, 2012 3:55 PM
    •  
     
  • Saturday, November 17, 2012 3:10 PM
     
     
    Sign In to Vote
    1



    1. How do I put desired database address (Access file path) in place of CurrentProject.Connection

    With regards : Ajay Check


    Const adLockReadOnly = 1
    Const adOpenForwardOnly = 0
    Dim strConnect As String, strSQL As String

    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data " _
    & "Source=C:\Program Files\Microsoft " _
    & "Office\Office12\SAMPLES\Northwind.mdb;" _
    & "Persist Security Info=False"


    strSQL = "SELECT Customers.ContactName " _
    & "FROM Customers " _
    & "WHERE (((Customers.CustomerID)='" & Sheets(1).Range("b1") & "')); "

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

    objConnection.Open strConnect
    objRecordset.Open strSQL, objConnection, _
    adOpenForwardOnly, adLockReadOnly

    do some stuff

    objRecordset.Close
    objConnection.Close

     

     

     

    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows]

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.

     
  • Saturday, November 17, 2012 3:52 PM
     
     
    Sign In to Vote
    0

    You cannot open a database from clientside code.

    You cannot open a database from serverside code without making special arrangements for proper authentication.

    This question belongs in the IIS developers forum and not in this admin scripting forum.

    ¯\_(ツ)_/¯

     
  • Saturday, November 17, 2012 4:06 PM
     
      Has Code
    Sign In to Vote
    0

    Hello Experts,

    1. AbqBill : As pointed out by, him, the problem was with security feature. Things worked when I turned it into .HTA application. Say thanks AbqBill, had he not pointed out security issue, I would have been throwing stones in all wrong direction.
    2. Things to remember / I learned : HTA and HTML works in different way, when it comes to security.
    3. While opening a connection to database or database itself, pay special attention to your Ms-Access version and provider version associated with it.
    4. Refer : http://msdn.microsoft.com/en-us/library/ms524771(v=vs.90).aspx. 
    5. For reference purpose, who are going to stumble upon same question, which I found a bit tricky, below is complete working code of HTA:
    <html>

<head>
<title>Demo project</title>
<HTA:APPLICATION 
     APPLICATIONNAME="HTA Test"
     SCROLL="yes"
     SINGLEINSTANCE="yes"
>

<!-- Script starts here -->
<script language="VBScript">
Dim objText

Sub subStart
'	Opening a connection to database
strConnectionString  = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\ZenCode.accdb"   
Set cnn = CreateObject("ADODB.Connection") 
cnn.Open strConnectionString 

'	Open a recordset using the Open method and use the connection established by the Connection object. 
Set rstCustomers = CreateObject("ADODB.Recordset") 
strSQL = "SELECT SubGroup FROM tblCode" 
rstCustomers.Open  strSQL, cnn  

'	Putting value from database to hta
rstCustomers.MoveFirst
Set objText = document.getElementById("area")
objText.value = rstCustomers("SubGroup")

'	Looping through database and printing data values
Do Until rstCustomers.EOF
Msgbox rstCustomers("SubGroup")
rstCustomers.MoveNext
Loop
End Sub
</script>
<head>


<!--Body starts here-->
<body onload="subStart()">
<center>You have to insert your access file path and table fields name to make it work with your code</center>

<hr>
<center><textarea id="area" rows="25" cols="50"></textarea></center>
</body>
</html>

    With regards : Ajay Check


    • Edited by Ajay Check Saturday, November 17, 2012 4:12 PM Adding more info
    •