Solved

Running VB code from table access

Posted on 2004-09-21
14
419 Views
Last Modified: 2012-05-05
Hi all
I am looking at creating a new database from scratch in access for the backend of a web application. I have done a bit of database design in the past and know that if your users are working with "forms" that you have created you can create VB code that runs when the users access the forms or do particular things.

Now because my users are coming in over the web (using asp) they wont be interfacing with forms, they will be directly interfacing with the tables. What I am wanting to know is if there is any way of running VB from within access when a user opens a "table" and focus on a particular record. For example, i could be a products table and every time a user access a particular record it runs some VB that runs a counter.
   
The reason for this is that i am trying to make my web application as efficient as possible. and i would prefer the database to run some off the code its self rather than creating a million line of code to do it from a web page.
Thanks in advance Anthony
0
Comment
Question by:vdhant
  • 7
  • 5
  • 2
14 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12118532
in ASP.Net you coould do sometihng like

sub page_load(Sender As object, e As eventArgs)

try
Application("someCount") = Application("someCount").tostring + 1
catch
'variable not there, create it
Application.Add("someCount", 1)
end try

You can then set the number of time to a label like

Me.lblCount.Text = Application.("someCount")

I dont think you can do it directly with VBA from ASP/ASP.Net

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12118551
the code in the webpage is run the on the server anyway and turned into html... Im no expert, but is there really gonig to be a difference??
0
 
LVL 2

Author Comment

by:vdhant
ID: 12119667
So in terms of how long the user has to weight, you don’t think there is any difference in time or efficiency to "if" i could embed some VB into the database to just placing it in a page like you would normally do.
thanks ant
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12119705
Hi Anthony,

when you connect to a Jet catalogue using ASP/ADO the catalogue is a dumb catalogue, the access application object is not instantiated at all, therefore no events can be triggered on the access application side. What you could do is have some stored queries that perform the actions you desire, and execute them by name using an ADO.Connection object from your ASP page.

eg... if you have a stored query in the catalogue you are connected to you can execute that query directly off the connection object. cn.execute spYourQuery

if you want you make your stored query/procedure to expect input parameters, you will need to instantiate an ADO.Command object to append input paramaters.

heres an example I use on an asp page to log visits to the web site.


  cn.Open sDataConnectLogVisit
 
      With cmd
        .ActiveConnection = cn
        .Commandtext = "spDoVisitLog"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@VisitTime", adDBTimeStamp, adParamInput, 4, dVisitTime)
        .Parameters.Append .CreateParameter("@RemoteHost", adVarChar, adParamInput, 20, sRemoteHost)
        .Parameters.Append .CreateParameter("@SessionID", adVarChar, adParamInput, 50, sSessionID)
        .Parameters.Append .CreateParameter("@AppRoot", adVarChar, adParamInput, 40, sAppRoot)
        .Parameters.Append .CreateParameter("@PageName", adVarChar, adParamInput, 50, PageName)
        .Parameters.Append .CreateParameter("@Referrer", adVarChar, adParamInput, 255, sReferrer)
        .Parameters.Append .CreateParameter("@UserAgent", adVarChar, adParamInput, 255, sUserAgent)
        .Parameters.Append .CreateParameter("@AdditionalText", adVarChar, adParamInput, 255, AdditionalText)
        .Execute , , adExecuteNoRecords
      End With
      Set cmd = Nothing
      cn.Close
      Set cn = Nothing

When executed it triggers this query in the access database to which the asp/ado connection is made.
PARAMETERS [@VisitTime] DateTime, [@RemoteHost] Text ( 255 ), [@SessionID] Text ( 255 ), [@AppRoot] Text ( 255 ), [@PageName] Text ( 255 ), [@Referrer] Text ( 255 ), [@UserAgent] Text ( 255 ), [@AdditionalText] Text ( 255 );
INSERT INTO tbl_VisitLog ( VisitTime, RemoteHost, SessionID, AppRoot, PageName, Referrer, UserAgent, AdditionalText )
VALUES ([@VisitTime], [@RemoteHost], [@SessionID], [@AppRoot], [@PageName], [@Referrer], [@UserAgent], [@AdditionalText]);


Hope this helps you

Regards Alan


0
 
LVL 2

Author Comment

by:vdhant
ID: 12119711
also I know how to make a counter i was just using it as example to try and improve time or efficiency by running code within the database on the server while the user is free to continue with what they are doing. Because i am looking at implementing some pretty hefty code and I don’t want the user to have to weight for it to run through finish.
thanks ant
0
 
LVL 2

Author Comment

by:vdhant
ID: 12119737
thanks alan
just a quick one is the above for normal asp or for .net
thanks ant
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12119755
Haven't had the pleasure of .net yet anthony, the code I posted is classic ASP/Vbscript

Alan
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 2

Author Comment

by:vdhant
ID: 12120005
thanks again
Unfortunatly i haven't worked with this faset of asp as yet (but now is as gooder time as ever). So if i understand you correctly the "Parameters" that you have set, execute particular queries in the access database.

So does this mean i will be saving time, by not haveing to use complex sql strings or can i run code from a qurie (i haven't done that before). Because traditional i have used quire to fill a table with a predefined sql quire, do you then use this to get the data from???
let me know
ant
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12120054
Hi ant,

how much do you know about connecting to a Jet MDB from an asp page?
do you know how to establish a connection?

what sort of development tools do you have?
have you got visual interdev?

Happy to help

Alan
0
 
LVL 2

Author Comment

by:vdhant
ID: 12120325
Well i am well verced in connecting to a database and retiveing data as follows

<% Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("db/database.mdb") & ";"
strSQL = "SELECT * from tblTable;"
set Rs = conn.execute(strSQL) %>

I know how the sql works and how to make it do what i want it to do (most of the time :D ), but as far as the connection to the database, i know that i need to use the following and i know how to change the path and stuff, but i have no idea how to implement what you are talking about. up till now i have just connected to the database retive the data i need directly from the relevent tables and put it on the page (as well as updateing, deleting and created records).

As far as development tools most of the time i use dreamweaver, with a little bit of notepad in there for on the fly stuff. and i have visual studio .net 2003 somewere but i have never used (i think interdev is a part of VS, but i am not sure).
thanks ant
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 200 total points
ID: 12121231

ok tis easy for a man of your obvious capabilties.
I have just created a database.mdb and hoted it in a DB folder in the root directory of my web.
I created a table called tbl_VisitLog
Fields:
VisitID - Autonumber - pkey
VisitTime - DateTime
RemoteHost - text - 20
SessionID - text - 50
AppRoot - text - 40
PageName - text - 50
Referrer - text - 255
UserAgent - text - 255
AdditionalText - text -255

Create a new query called spDoVisitLog
With the query opened in design view, on the query menu choose parameters to invoke the parameters dialogue.
In the parameters dialogue define some named parameters. they dont have to have @ symbol prefix, this is just a habit I picked up from sql server parameters, square brackets too are optional unless the parameter name has spaces.

[@VisitTime]          Date/Time
[@RemoteHost]      Text
[@SessionID]         Text
[@AppRoot]           Text
[@PageName]        Text
[@Referrer]           Text
[@UserAgent]        Text
[@AdditionalText]   Text

Next from the query menu choose Append query and select tbl_VisitLog as the destination table.

Next  add some fields, in the fields row of the first column type         Expr1: [@VisitTime]
This tells Jet sql that the value  to append will be coming from the named input parameter @VisitTime.
Then choose a destination field from the dropdown in the Append To row of the first column, the field VisitTime looks good here.

repeat this process for each of the parameters you defined.


 Expr2: [@RemoteHost]      
 Expr3: [@SessionID]        
 Expr4: [@AppRoot]          
 Expr5: [@PageName]        
 Expr6: [@Referrer]          
 Expr7: [@UserAgent]        
 Expr8: [@AdditionalText]  

If you open the  query in sql view it should look like this:
PARAMETERS [@VisitTime] DateTime, [@RemoteHost] Text ( 255 ), [@SessionID] Text ( 255 ), [@AppRoot] Text ( 255 ), [@PageName] Text ( 255 ), [@Referrer] Text ( 255 ), [@UserAgent] Text ( 255 ), [@AdditionalText] Text ( 255 );
INSERT INTO tbl_VisitLog ( VisitTime, RemoteHost, SessionID, AppRoot, PageName, Referrer, UserAgent, AdditionalText )
VALUES ([@VisitTime], [@RemoteHost], [@SessionID], [@AppRoot], [@PageName], [@Referrer], [@UserAgent], [@AdditionalText]);

Now if you save the query and run it, you will be prompted for the 8 input parameter values.
Once you have tested it and know that you can append records by manually entering the input parameter values, you can close access and try to invoke the parameter query spDoVisitLog from your ASP script using ADO

Create a new ASP page called LogVisit.asp and paste the following script.

<script language=VBScript runat=Server>

Public Sub LogThisPage()
  Call WriteLog(Request.ServerVariables("PATH_INFO"), "")
End Sub

Public Sub WriteLog(ByVal PageName, ByVal AdditionalText)
      
      Const adCmdText = 1
      Const adCmdStoredProc = 4
      Const adParamInput = 1
      Const adDBTimeStamp = 135
      Const adDate = 7
      Const adVarChar = 200
      Const adExecuteNoRecords = 128
      
      Dim sAppRoot
      Dim sReferrer
      Dim sRemoteHost
      Dim sUserAgent
      Dim cn
      Dim cmd
      Dim sConnect
      Dim dVisitTime
      Dim sSessionID
      
      
      dVisitTime = FormatDateTime(Now(),vbGeneralDate)
      If dVisitTime = "" Then
        dVisitTime = Null
      End If
      
      On Error GoTo 0
      
      sRemoteHost = left(Request.ServerVariables("REMOTE_HOST"),20)
      If sRemoteHost = "" Then
        sRemoteHost = Null
      End If
      
      On Error GoTo 0
      
      sSessionID = left(Session.SessionID,50)
      If sSessionID = "" Then
        sSessionID = "Unknown"
      End If
      
      On Error GoTo 0
      
      sAppRoot = left(Application("AppRoot"),40)
      If sAppRoot = "" Then
        sAppRoot = "Unknown"
      End If
      
      On Error GoTo 0
      
      If PageName = "" Then
              PageName = Request.ServerVariables("PATH_INFO")
        If PageName = "" Then
                PageName = "Unknown"
        End If
        On Error GoTo 0
      End If
      PageName = left(PageName,50)
      
      On Error GoTo 0
      
      sReferrer = left(Request.ServerVariables("HTTP_REFERER"),255)
      If sReferrer = "" Then
        sReferrer = Null
      End If
      
      On Error GoTo 0
      
      AdditionalText = left(AdditionalText,255)
      If AdditionalText = "" Then
        AdditionalText = Null
      End If
      
      On Error GoTo 0
      
      sUserAgent = left(Request.ServerVariables("HTTP_USER_AGENT"),255)
      If sUserAgent = "" Then
        sUserAgent = Null
      End If
      
      On Error GoTo 0
      Set cn = Nothing
      Set cn = Server.CreateObject("ADODB.Connection")
      Set cmd = nothing
      Set cmd = Server.CreateObject("ADODB.Command")
      
   
  dim sDatapath, sDataConnectLogVisit
  sDatapath = Server.MapPath("./db/Database.mdb")
  sDataConnectLogVisit = adoConnectJet40(sDataPath, "")

 
  cn.Open sDataConnectLogVisit
 
      With cmd
        .ActiveConnection = cn
        .Commandtext = "spDoVisitLog"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@VisitTime", adDBTimeStamp, adParamInput, 4, dVisitTime)
        .Parameters.Append .CreateParameter("@RemoteHost", adVarChar, adParamInput, 20, sRemoteHost)
        .Parameters.Append .CreateParameter("@SessionID", adVarChar, adParamInput, 50, sSessionID)
        .Parameters.Append .CreateParameter("@AppRoot", adVarChar, adParamInput, 40, sAppRoot)
        .Parameters.Append .CreateParameter("@PageName", adVarChar, adParamInput, 50, PageName)
        .Parameters.Append .CreateParameter("@Referrer", adVarChar, adParamInput, 255, sReferrer)
        .Parameters.Append .CreateParameter("@UserAgent", adVarChar, adParamInput, 255, sUserAgent)
        .Parameters.Append .CreateParameter("@AdditionalText", adVarChar, adParamInput, 255, AdditionalText)
        .Execute , , adExecuteNoRecords
      End With
      Set cmd = Nothing
      cn.Close
      Set cn = Nothing
End Sub


Public Function adoConnectJet40(psDataPath, psFilePassword)
 
  ' returns Jet 4.0 ADO connect string:
  '   "Provider=Microsoft.Jet.OLEDB.4.0
  '     ;Data Source=C:\xxx\Some.mdb
  '     ;Jet OLEDB:Database Password=xxx"
 
  Dim sProvider, sDataSource, sDBPassword

  sProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
  sDataSource = ";Data Source=" & psDataPath
  If psFilePassword = "" Then
    sDBPassword = ""
  Else
    sDBPassword = ";Jet OLEDB:Database Password=" & psFilePassword
  End If

  adoConnectJet40 = sProvider & sDataSource & sDBPassword

End Function

</script>



Then save the page.
To invoke it in any other page in your web include the following in the head section

<HEAD>
  <!-- #include file ="LogVisit.asp" -->
  <%Call LogThisPage()%>
</HEAD>


Thats it ant !!!
Sorry if I have over explained.

Oh if you password protect your mdb you can pass the password to the adoConnectJet40 function
sDataConnectLogVisit = adoConnectJet40(sDataPath, "The password")



Great help library on ADO here:
Microsoft Data Access Components (MDAC) 2.6 SDK
http://www.microsoft.com/downloads/details.aspx?familyid=cf5bf48d-9bbb-4ca2-9b03-4ee000db37ff&displaylang=en

There are later SDK's but this one has 14 meg of how to's and samples with it.

The key with appending parameters is to define the types and sizes correctly, the following table ADO Data Types Enum is excellent for reference http://www.able-consulting.com/ADODataTypeEnum.htm


Success with your app.



Alan
















0
 
LVL 2

Author Comment

by:vdhant
ID: 12122004
Thanks very very much
this is great and no you haven;t over explained it s just great and it all makes sense. I didn't know you could do stuff like this. just one thing though and don't take this the wrong way, but i am interested to know why you would use this as a method of putting data into a database as to using something like the following:-

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("db/databse.mdb") & ";"
strSQL = " SELECT tblAccess.* FROM tblAccess;"
Set rs = Server.CreateObject("ADODB.Recordset")       
rs.CursorType = 2
rs.LockType = 3      
rs.Open strSQL, conn
              rs.AddNew
              rs.Fields("fldVisitTime") = dVisitTime                      
              ect, ect..........
              rs.Update  
rs.Close
Set rs = Nothing %>

Is it more efficient, is it simply that you don;t have to set multiple sql strings and record sets because you can do it with the quire, or is it if you do it this way the work is all done on the server side and the user doesn't have to weight for the code to fully run, or is it simply another way of entering data into a database  
thanks again
ant
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12122448
Its all part of preparing to upsize to sql server really I suppose ant. In sql server you can assign permissions to individual database procedures and functions so you would give execute permissions to the stored proc spDoVisitLog, it means anonymous users cant access tables directly either to read or write, only the stored procs can and then only the ones that you have assigned execute permissions to.

Unfortunately when using Jet mdb's the IUSER account needs special permissions in order to create the LDB file.
There is nothing wrong with  using dynamic sql and returning recordsets, but because Jet only supports a client side cursor, if possible it good to do stuff without instantiating a recordset object. Though I do believe that any instantiation of an ADO command object also instantiates an ADO recordset object in order to return the ReturnParameter.

You can still return a recordset using a command object to execute a stored select query that generates a resultset.
Set rs = Cmd.Execute

You can also pass parameters to a stored query and return a recordset directly off the connection object by name.
The following executes a stored query called qReadCustomer and passes two expected params (psLogonID, psPassword) and returns the resultset as an ado.recordset

cn.qReadCustomer psLogonID, psPassword, rs

theres more than one way to skin a database - LOL

have fun

Alan


0
 
LVL 2

Author Comment

by:vdhant
ID: 12123427
thanks a heep alan
ant
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now