INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Visual Basic (Microsoft) Versions 5/6 FAQ

Database

Accessing Data Natively by jlgdeveloper
Posted: 30 Jun 02 (Edited 28 May 03)

Updated: 5/2003
How do you get to your data without using the data environment and the data centric vb controls?

Once you free yourself from having no choice but to use the designers and controls provided in vb and access, you will find that you can shape an app to your own ideals. Here is a handy bit of code I wrote which I use to access my data.

Notes:
1/> I use a DSN (Data Source Name).
2/> I use a dll I built, code at bottom, which required the MDAC 2.5 + be installed. The advantage of the dll is that I can have as many different recordsets open at one time as I like, even in a web page. It also takes very few lines to get at your data. Email for the oracle version.
3/> Web page use of the dll differs slightly.
4/> You can have my dll, visit www.iqzero.net
5/> If you do not use the dll, you can copy and paste the code into your page (modify how you call it), or add it as a class, and use it in an identical way.

Code in your app to natively access data
(vb using dll/class):
******************************************
'*** set a reference in your project to j_rs.dll first

Dim myRs As j_rs.c_jRS
Dim s As String
Dim rs As Object
    
On Error GoTo errorhandler
Set myRs = New j_rs.c_jRS
s = "select * from customer where State = '" & myState & "'"
Set rs = myRs.openRS(read_only, s, "myDSNnameDB")
'*** here we are opening a read only cursor(fast).
if not(rs.bof and rs.eof) then
    do until rs.eof
       '*** do something with your data
        myCustomerID = rs("fkCustomerID") '*** get your data
        rs.movenext '*** move the recordset forward
    loop    
else
    '*** code when no record exists
end if
Set myRs = Nothing
Set rs = Nothing
Exit Sub

errorhandler:
    Set myRs = Nothing
    Set rs = Nothing
**************************************************
( to open a second recordset, simply use the following within the code above:

'*** assume you have dimmed rs2 as object
[you may like to use an array: example rs(3)("pkCustomerID") = x]

s = "select pkOrderID from Orders where (fkCustomerID = '" & rs("pkCustomerID") & "')"
Set rs2 = myRs.openRS(read_write, s, "myDSNnameDB")
'*** here we are opening a read - write cursor.
'*** use the rs. properties the same as ado, depending on the cursor ie rs.update

***********************************************
Code modifications needed for use in a web page
***********************************************
1>using create object, reference the same jrs class of the dll.
2>use the openRsWeb function, not openRS.
3>for a read cursor, use the word "read", for write, use "write".

ie:

set myrs = server.CreateObject("j_rs.jrs")
s = "SELECT COUNT(*) AS cnt, fkStatusID FROM job GROUP BY fkStatusID"
set rs = myrs.openRSWeb("read",s,"helpDesk")
opn = 0
oh = 0
clsd = 0
do until rs.eof
    select case rs("fkStatusID")
        case 1: opn = rs("cnt")
        case 2: oh = rs("cnt")
        case 3: clsd = rs("cnt")
    end select
    rs.movenext
loop
set rs = nothing
set myrs = nothing

********************************************************
Code inside the dll I use:

*********************************
VB and web, one class, called jrs
*********************************
Option Explicit
Dim rs As ADODB.Recordset
Public Enum en_rw
     read_only '0
     read_write '1
     read_static '2
End Enum

Public Function openRS(read_write As en_rw, source As String, dsn As String) As Object
    Set rs = New ADODB.Recordset
    
    On Error GoTo errorhandler
    rs.ActiveConnection = "DSN=" & dsn & ";"
    Select Case read_write
        Case 0
           rs.Open source, , adOpenForwardOnly, adLockReadOnly
        Case 1
           rs.Open source, , adOpenDynamic, adLockOptimistic
        Case 2
            rs.Open source, , adOpenStatic, adLockReadOnly
    End Select
    Set openRS = rs
    Exit Function
errorhandler:
    App.LogEvent vbCrLf & source & vbCrLf & Err.Description & vbCrLf & Err.Number & vbCrLf & App.EXEName & " jRS Dll: openRS" _
        & vbCrLf & source
    Err.Clear
End Function

Public Function openRSWeb(ByVal read_write As String, ByVal source As String, ByVal dsn As String, Optional isStatic As Boolean) As Object
    Set rs = New ADODB.Recordset
    
    On Error GoTo errorhandler
    rs.ActiveConnection = "DSN=" & CStr(dsn) & ";"
    source = CStr(source)
    read_write = CStr(read_write)
    If isStatic Then
        rs.Open source, , adOpenStatic, adLockReadOnly
    Else
        If read_write = "read" Then
           rs.Open source, , adOpenForwardOnly, adLockReadOnly
        Else
           rs.Open source, , adOpenDynamic, adLockOptimistic
        End If
    End If
    Set openRSWeb = rs
    Exit Function
errorhandler:
    App.LogEvent vbCrLf & Err.Description & vbCrLf & Err.Number & vbCrLf & App.EXEName & " jRS Dll: openRSweb" _
        & vbCrLf & source
    Err.Clear
End Function

Private Sub Class_Terminate()
    Set rs = Nothing
End Sub

*****
notes
*****
Errors are written to the application event log. Look there first (event viewer) when troubleshooting.

**********************************************************
Download the dll at www.iqzero.net
**********************************************************

Rate this article below.
       |
       |
       |
       V




Back to Visual Basic (Microsoft) Versions 5/6 FAQ Index
Back to Visual Basic (Microsoft) Versions 5/6 Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close