Announcement

Collapse
No announcement yet.

Vbscript With Dao

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Vbscript With Dao



    I'm trying to write some VBscript to pull data from a Jet database. I'm somewhat familiar with DAO, but not with VBScript. It seems like the following code should work.

    It doesn't create an error, it just returns a blank page. Does anyone have any suggestions.

    Code:
    <html>
    <head>
    </head>
    <body><script type="text/vbscript">
    Dim Dbs
    Dim Tdf
    Dim Rst
    
    Set Dbs = OpenDatabase("\\lcw00086676xp\Shared Folder\Tools\DailyReport\Back End\ScheduleData_be.mdb")
    Set Tdf = Dbs.TableDefs("Select_EmpList")
    Set Rst = Dbs.OpenRecordset("Select_EmpList")
    
    With Rst
        .MoveFirst
       document.write !Name
    End With
    
    
    
    </script></body>
    </html>
    I hope this helps.
    Check out theseTemplates & Calculators!

  • #2
    Re: Vbscript With Dao

    I think you need to loop through the recordset. In VBA to output a list of the first field in a table you would need :

    Code:
    Dim Dbs As DAO.Database
    Dim Rst As DAO.Recordset
     
    Set Dbs = OpenDatabase("c:\test.mdb")
    Set Rst = Dbs.OpenRecordset("Table1")
     
    Rst.MoveFirst
    Do While Not Rst.EOF
        MsgBox (Rst(0))
        Rst.MoveNext
    Loop
    
    Rst.Close
    Set Rst = Nothing
    Dbs.Close
    Set Dbs = Nothing
    It depends what you are trying to write but if you replace the msgbox with document.write and the 0 with the field number you should be close.

    HTH
    Carl
    locii Insight
    Locii Solutions. Business IT Training. Business Consulting. Excel Training. Business Intelligence using Excel 2010. Cyprus.

    Comment


    • #3
      Re: Vbscript With Dao

      Your VBscript needs to be in the Head of your HTML code... it will never work otherwise...

      Code:
      <html> 
      <head> 
      <script type="text/vbscript"> 
      Function showRrst()
      
      Dim Dbs As DAO.Database 
      Dim Rst As DAO.Recordset 
       
      Set Dbs = OpenDatabase("c:\test.mdb") 
      Set Rst = Dbs.OpenRecordset("Table1") 
       
      Rst.MoveFirst 
      Do While Not Rst.EOF 
          strHtml += "<tr>";
      strHtml += " <td><Font face ='tahoma'>" + rst.fields(0).value + "</font></td>";
      strHtml += " <td><Font face ='tahoma'>" + rst.fields(1).value + "</font></td>";
      strHtml += " <td><Font face ='tahoma'>" + rst.fields(2).value + "</font></td>";
      strHtml += " <td><Font face ='tahoma'>" + rst.fields(3).value + "</font></td>";
      strHtml += "</tr>";
          Rst.MoveNext 
      Loop 
      
      Rst.Close 
       
      document.write(strHtml);
      
      Set Rst = Nothing 
      Dbs.Close 
      Set Dbs = Nothing 
      end function
      </script>
      
      </head> 
      <body>
      <script type="text/vbscript"> 
      showrst();
      </script>
      </body> 
      </html>
      will a little tweaking you should get your desired result...

      HTH

      z

      Comment


      • #4
        Re: Vbscript With Dao

        None of those examples worked for me. It looks like you have to create a DAO 3.6 engine to use to open the database. This code works (provided your Northwind database is in the same folder as mine).

        Code:
        <html> 
        <head> 
        <script type="text/vbscript"> 
         Function showrst()
             
            Dim Dbs 'As DAO.Database 
            Dim Rst 'As DAO.Recordset 
            DIM db
         
            Set Dbs = CreateObject("DAO.DBEngine.36")
            Set db = dbs.OpenDatabase("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb") 
            Set Rst = Db.OpenRecordset("Customers") 
             
            Rst.MoveFirst
            strHTML = "<Table>"
            Do While Not Rst.EOF 
                strHtml = strHtml & "<tr>" 
                strHtml = strHtml & " <td><Font face ='tahoma'>" + rst.fields(0).value + "</font></td>"
                strHtml = strHtml & " <td><Font face ='tahoma'>" + rst.fields(1).value + "</font></td>" 
                strHtml = strHtml & " <td><Font face ='tahoma'>" + rst.fields(2).value + "</font></td>" 
                strHtml = strHtml & " <td><Font face ='tahoma'>" + rst.fields(3).value + "</font></td>" 
                strHtml = strHtml & "</tr>"
                Rst.MoveNext 
             Loop 
            strHTML = strHTML & "</Table>"
            Rst.Close 
             
            document.write(strHtml)
             
            Set Rst = Nothing
            db.close
            Set Dbs = Nothing 
        End Function 
        </script> 
         
        </head> 
        <body> 
        <script type="text/vbscript"> 
        showrst()
        </script> 
        </body> 
        </html>

        Comment


        • #5
          Re: Vbscript With Dao

          The following link has some really good information on it concerning DAO in VBScript. I don't know why but I couldn't seem to access any other chapters from this book. I tried navigating there properly as well as changing the chapter number in the URL but had no luck accessing any more information.

          http://www.oreilly.de/catalog/progac...pter/ch02.html

          Comment


          • #6
            Re: Vbscript With Dao

            Thank Mavyak you very much for your help. I'm still returning a blank page. Do i have to have ASP or PHP and a web server installed for this to work?

            I thank you again for your time answering my questions.

            Have a great day.
            I hope this helps.
            Check out theseTemplates & Calculators!

            Comment


            • #7


              Re: Vbscript With Dao

              Looks like I had some concatenation problems on that last batch of code. I also had the wrong path to my Northwind database (I may have posted the original from my work pc). This code works, but only in Internet Explorer (I use SeaMonkey).

              Code:
              <html> 
              <head> 
              <script type="text/vbscript"> 
               Function showrst() 
                   
                  Dim Dbs 'As DAO.Database
                  Dim Rst 'As DAO.Recordset
                  Dim db 
                   
                  Set Dbs = CreateObject("DAO.DBEngine.36") 
                  Set db = dbs.OpenDatabase("C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb") 
                  Set Rst = Db.OpenRecordset("Customers") 
                   
                  Rst.MoveFirst 
                  strHTML = "<Table>" 
                  Do While Not Rst.EOF 
                      strHtml = strHtml & "<tr>" 
                      strHtml = strHtml & " <td><Font face ='tahoma'>" & rst.fields(0).value & "</font></td>" 
                      strHtml = strHtml & " <td><Font face ='tahoma'>" & rst.fields(1).value & "</font></td>" 
                      strHtml = strHtml & " <td><Font face ='tahoma'>" & rst.fields(2).value & "</font></td>" 
                      strHtml = strHtml & " <td><Font face ='tahoma'>" & rst.fields(3).value & "</font></td>" 
                      strHtml = strHtml & "</tr>" 
                      Rst.MoveNext 
                   Loop 
                  strHTML = strHTML & "</Table>" 
                  Rst.Close 
                   
                  document.write(strHtml)
                   
                  Set Rst = Nothing 
                  db.close 
                  Set Dbs = Nothing 
              End Function 
              </script> 
               
              </head> 
              <body> 
              <script type="text/vbscript"> 
              showrst() 
              </script> 
              </body> 
              </html>

              Comment

              Working...
              X