Solved

ADO Recordset.. Binding Access2000 Database to Fields?

Posted on 2001-08-19
14
335 Views
Last Modified: 2013-11-25
I am writing a project use ADO to An Access2000 Database
---
I am connecting to the database using ADO.
---
Connecting to the ado source, checking data etc ....
??????????????????????????????
Am i doing it the RIGHT way???
====================================
??1. Opening.....
Global rs As ADODB.Recordset
Global cnn As New ADODB.Connection
rs.Open "wol", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.Index = ("TITLE")
    rs.MoveFirst
    Slider1.Max = rs.RecordCount - 1
===
??2. Binding Text Input Fields..
  Set TextTitle.DataSource = rs
    TextTitle.DataField = "Title"
    Set TextQuantity.DataSource = rs
    TextQuantity.DataField = "Quantity"
    QuantityInput = ""
    QuantityInput.SetFocus
===
I have tried the properties, does not seem to recognise.
??3. Checking that input is Numeric.
Private Sub QuantityInput_Change()
    If IsNumeric(QuantityInput) Then
    Else
        Beep
        QuantityInput.Text = ""
        QuantityInput.Refresh
    End If
End Sub
====
??4. Checking if Input Is +  or -
Private Sub Quantityinput_LostFocus()
    Dim TestMinus As String
    Dim TestLen As Integer

    TestLen = Len(QuantityInput)
    TestMinus = Mid(QuantityInput, TestLen, 1)
    If TestMinus <> "-" Then
        rs.Fields("quantity") = rs.Fields("quantity") + Val(QuantityInput)
    Else
        rs.Fields("quantity") = rs.Fields("quantity") - Val(QuantityInput)
    End If

    TextQuantity = rs.Fields("quantity")
    TextQuantity.Refresh
End Sub

=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

All the above is working, but it seems a little complicated.

I am relatively new to VB, please excuse it i am off the track a bit!


Would appreciate some advice as to my code, and also
any other better ways of doing it!!!
0
Comment
Question by:turnerrob
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6404910
I can see a number of problems.  But if you could explain what you are trying to achieve it may be for the best.

1. Does the RecordCount property actually return a value (other than -1)  I suspect you may have to do one of two things to get it to give you a valid answer
a. rs.CursorLocation = adClient 'before opening the recordset
b. rs.MoveLast  'before checking the value of RecordCount

2. I have a personal aversion to bound controls so don't expect any positive comments here.  In any non-trivail app, they usually end up being more trouble than they are worth.

3 & 4 For the most part, as you have discovered, is a royal PITA.  The IsNumeric does not always work and the LostFocus event does not always fire.  You are better off, checking for the correct format when you update the record.

Hope this helps,
Anthony
0
 

Author Comment

by:turnerrob
ID: 6404923
I have an Access2000 Database, with a large number of song titles.
I need the user to enter progressively the quantity of useage of each title.

===========================================

Am i best to us a Bound Control.???
I avoided this , due to comments in the forum, that ADO is better.


If i use Bound.. Can i lose the path of where the file is loaded upon installation. (The user may install to another directory)

Please remember, i am only just starting in VB.!!!


Appreciate any help
0
 
LVL 1

Expert Comment

by:sanjeevjain1973
ID: 6404931
To get the path u can use App.Path function. it will return path where ever application running.

Sanjeev
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6404961
Some clarification is in order here.

The 2 object models that you can use with Access are DAO and ADO.  Both have their own Data Controls that allow you to bind other controls at design time.  You can still bind controls without using the Data Controls, but you have to write code to do that.

I am suggesting you do NOT bind your controls, either with a Data Control or otherwise.  Learn to read the data from a recordset and write it back to it.

Binding controls has nothing to do with where the data is located.  It is a separate issue.

If you think it would help, post your table structure and I will see if I can come up with some example that may help you.

Anthony
0
 

Author Comment

by:turnerrob
ID: 6404981
Database Name    Accesswol.mdb...   Table=  Wol

My Access2000 Database File structure is.. (Wol)

Code       Text
Title      Text
Quantity   Number  (ie Accum Quantity)
----------------------------------
There are nearly 100,000 records
I have an Index on Code  and Title
----------------------------------

The program, basically, consists of.

Form1.
A series of lookups on the title
ie.
a. After 3 characters are keyed in, a list box is filled
with all titles with these 3 characters in the first 3 positions.
b. A random lookup on the database for the characters in the title field, results displayed in list box.
----
The user clicks on the item in the list box, and to the right-->
Shows.. Title    The song title...........
        Extra Quantity                     Accum Quantity

The user enters  say 111 to Extra Quantity, this is added to Accum Quantity
-----------
The user then looks up next title  etc...

The program is only very simple.

Would appreciate advice as to the best way to handle Access2000 Tables etc in VB.
Ado seems hard work??

Hope i have given enough info.


Regards  Turnerrob

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6405005
Let me take a look at it and will try and get back to you tomorrow.

Anthony
0
 

Author Comment

by:turnerrob
ID: 6405067
Thanks, for your interest, much appeciated.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Expert Comment

by:harsh008_k
ID: 6405137
U can instead try writing the code

1)make standard module(i.e .bas file)
2)write
''conn is connection Object used
''for conecting to the database

Public con As ADODB.Connection
3)go to odbc in control panel,choose userdsn,then choose add,choose access driver,say finish,then choose database,give a dsn name.
4)In ur MDIform(if u have one or sdi form) write

Set con = New ADODB.Connection
con.Open "dsn=dsnname;uid=;pwd=;"

or
Set con = New ADODB.Connection
con.open app.path &"\" &"db.mdb"

use
voila  its connected.

5)
write this code in ur form

Private Sub openrec(sql As String, rs As ADODB.Recordset, con As Connection, rstype As String, mode As String)
Set rs = New ADODB.Recordset
rs.Open sql, con, rstype, mode
End Sub

''this will generate a recordset of ur choice


6)write a sql to open recordset

dim sql as string
sql="select * from tablename"
call openrec(sql,rsname,con,adopenkeyset,adlockoptimistic)

ur rsname is ready to use

7)If u would like to show the records in textbox
then

text1.text=rsname.fields(0)
or
text1.text=rsname.fields("field name"))

u can do it for other textbox and other controls aswell


8)to restrict user to enter numeric values
in the keypress event of text box write
 If  (KeyAscii >= 48 And KeyAscii <= 57)  
 Or KeyAscii = 8  Then
 Else
 KeyAscii = 0
 End If
 End If


to track the quantity use static variable

static intquantity as integer

intquantity=intquantity+txtquantity

while saving

rsname.fields("quantity")=intquantity

******if u have any doubts ,plz gimme feedback*****
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 75 total points
ID: 6408344
Here is the code I promised you:

First the instructions:
1. Create a new Project
2. Add a Text Box (txtTitles)
3. Add a Command button (cmdLookup)
4. Add a fairly wide List Box (lstTitles)
5. Add another Text Box (txtQuantity)
6. Add another Command button (cmdUpdate)

Then paste this code:

Option Explicit
Private cn As ADODB.Connection

Private Sub Form_Load()

Initialize

End Sub

Private Sub cmdLookup_Click()

Lookup

End Sub

Private Sub cmdUpdate_Click()

UpdateQuantity

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

cn.Close
Set cn = Nothing

End Sub

Private Sub Initialize()

' Set the connection
Set cn = New ADODB.Connection
With cn
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\Accesswol.mdb"
   .Open
End With

' Set the tabstops for the list box so the fields appear in justified columns
ReDim TabStops(0) As Long
TabStops(0) = -200      ' The negative amount right justifies.  It may need to be adjusted
SetTabStops lstTitles.hWnd, TabStops()
 Erase TabStops
 
 ' Intialize the quantity to be added
 txtQuantity.Text = "0"

End Sub
Private Sub Lookup()
Dim Search As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
   'Only return the first 1000 (change if not appropriate)
   .Source = "Select Top 1000 Title, Quantity From Wol"
   Search = Trim(txtTitle.Text)
   If Len(Search) Then
      .Source = .Source & " Where Title Like '" & Search & "%'"
   End If
   .Source = .Source & " Order By Title"
   .ActiveConnection = cn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open Options:=adCmdText
   lstTitles.Clear
   Do While Not .EOF
      lstTitles.AddItem !Title & vbTab & CStr(!Quantity)
      .MoveNext
   Loop
   If lstTitles.ListCount Then
      lstTitles.ListIndex = 0
      txtQuantity.Enabled = True
      cmdUpdate.Enabled = True
   Else
      txtQuantity.Enabled = False
      cmdUpdate.Enabled = False
   End If
   .Close
End With
Set rs = Nothing

End Sub

Sub UpdateQuantity()
Dim AddQty As Long
Dim Pos As Integer
Dim ListItem As String
Dim Title As String
Dim Qty As Long

On Error GoTo NumErrHandler
AddQty = CLng(txtQuantity.Text)

On Error GoTo ErrHandler

ListItem = lstTitles.Text
Pos = InStr(ListItem, vbTab)
Title = Left$(ListItem, Pos - 1)
Qty = Mid$(ListItem, Pos + 1)
cn.Execute "Update wol Set Quantity = Quantity + " & AddQty & " Where Title = '" & Title & "'", , adExecuteNoRecords
lstTitles.List(lstTitles.ListIndex) = Title & vbTab & CStr(Qty + AddQty)

Exit Sub
NumErrHandler:
MsgBox "Invalid Quantity:" & vbCr & txtQuantity.Text

Exit Sub
ErrHandler:
MsgBox Err.Description

End Sub


Make the necessary changes to the connection string and run.

This does not fulfill all your requirements, nor does it have all the error checking it should, but should get you on the right track.  

Let me know if you have any problems.
Anthony
0
 

Author Comment

by:turnerrob
ID: 6408523
Thanks for all the trouble in the sample.,much appreciated.

I have all the form and code loaded etc.

I get an error, sub or function not defined.

SetTabStops Lsttitles.hWnd, TabStops()
***********
It is probably a Reference or component missing in my project???? Have tried a few !!


Appreciate your advice.

Regards  Turnerrob
0
 

Author Comment

by:turnerrob
ID: 6408585
To acperkins

I have worked out the missing Module for SetTabstops.

Thankyou very much for the code, i will study it carefully and apply to my project.

Once again, much appreciated.

Thanks also for the other comments posted.


Regards  Turnerob
0
 

Author Comment

by:turnerrob
ID: 6408915
.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6409677
Sorry about that.  Here is the missing code:

Private Declare Function SendMessageLong Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Private Sub SetTabStops(hWnd As Long, TabStops() As Long)
Const LB_SETTABSTOPS = &H192

'Clear any existing tabs
SendMessageLong hWnd, LB_SETTABSTOPS, 0&, 0&
SendMessageArray hWnd, LB_SETTABSTOPS, CLng(UBound(TabStops) - LBound(TabStops) + 1), TabStops(LBound(TabStops))

End Sub
0
 

Author Comment

by:turnerrob
ID: 6411257
Thanks, much appreciated.
Turnerrob
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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

18 Experts available now in Live!

Get 1:1 Help Now