Accessing Data Natively

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