?
Solved

Database Display

Posted on 2003-03-26
20
Medium Priority
?
151 Views
Last Modified: 2010-05-01
Hi

I have an access2000 database and am trying to display fields in vb. What i want it to do is when a certain button is clicked it will do a select sql query and then display these results in a few different labels. I have tryed doing it with ado and recordsets but cant :( So am wondering if anyone can show me how to open a database and the do different sql querys for each button pressed.


0
Comment
Question by:Corekill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 5
  • +1
20 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8213566
after open database and create a recordset object  do the following

sub populateLabels(rs as adodb.recordset)
' best if you use a control array of labels

dim i as integer
' it suppose that there are 4 fields
for i= 0 to 3
    label1(i).caption=rs.fields(i).value
next i
end sub
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 8213573
to avoid nulls:
sub populateLabels(rs as adodb.recordset)
' best if you use a control array of labels

dim i as integer
' it suppose that there are 4 fields
for i= 0 to 3
   label1(i).caption="" & rs.fields(i).value
next i
end sub
0
 

Author Comment

by:Corekill
ID: 8213620
Thanks for that but am wondering how to open up the database and make a different recordset for each different query. Maybe a step by step guide and example code if thats not to much ?

Sorry if that wasnt clear
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Corekill
ID: 8213671
Thanks for that but am wondering how to open up the database and make a different recordset for each different query. Maybe a step by step guide and example code if thats not to much ?

Sorry if that wasnt clear
0
 
LVL 1

Accepted Solution

by:
Metalhead earned 600 total points
ID: 8213677

Here's how I would do it, you can do it this way or you can modify it to your preference:

  'This creates and a connection to your DB, I am assuming you are not using User/Password

  ADOConnection.ConnectionString  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\db1.mdb";Persist Security Info=False"  
  ADOConnection.CursorLocation = adUseClient
  ADOConnection.ConnectionTimeout = 5
  ADOConnection.Open

  Set adoRS = New adodb.Recordset

  'Here's 2 methods of executing your query, I prefer the first as everything is explicitly specified

  'Method 1
  adoRS.ActiveConnection = ADOConnection
  adoRS.CursorLocation = adUseClient
  adoRS.Source = "Select Field1 From Table1 Where Field2 = '" & cmdButton1.Caption & "'"

  adoRS.CursorType = adLockOptimistic
  adoRS.LockType = adOpenDynamic
  adoRS.Open

  'Method 2
  adors.Open "Select Field1 From Table1", ADOConnection
  'Or something like this
  adors.Open "Select Field1 From Table1 Where Field2 = '" & cmdButton1.Caption & "'"

  'Now you have your recordset and you can use your recordset to populate whatever you want.

  'Validate that we DO have recordset
  If adors.recordcount > 0 then
    'Now you have a choice of either using the first record
    'to populate your label or loop in your recordset
    label1.caption = adors.fields("Field1").value
  end if


  All of the initilisation, both the adoConnection and adoRs should only be done once. This is a suggestion only. If you want to initialise these each time you do a adoRs.Open that is fine too.
  The adoRs.Open should be performed has many times as you wish.
0
 
LVL 1

Expert Comment

by:Metalhead
ID: 8213691
Forgot

Set adoConnection = new adodb.Connection
0
 

Author Comment

by:Corekill
ID: 8213764
Thanks but i get an error here :

Set adoConnection = New adodb.Connection ' Error here used type not defined
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=volcano.mdb;Persist Security Info=False"
 adoConnection.CursorLocation = adUseClient
 adoConnection.ConnectionTimeout = 5
 adoConnection.Open
 

 Set adors = New adodb.Recordset ' Error here used type not defined

Any ideas how to fix that ?
0
 

Author Comment

by:Corekill
ID: 8213780
Sorry i works fine!

Thanks very much

btw do u know how to use map path to the database ?
0
 
LVL 1

Expert Comment

by:Metalhead
ID: 8213798
 Do you have a reference to Microsoft ActiveX Data 2.x Object Library

  Go Project -> Reference

  In the list of Available References make sure that you have

  Microsoft ActiveX Data 2.x Object Library

  The x represents either 5, 6, 7 maybe even 8!

  If you do not a reference to the Microsoft ADO 2.x library or a higher version in the list (checked or unchecked) you might have to get MDAC from Microsoft

 
0
 
LVL 1

Expert Comment

by:Metalhead
ID: 8213826
 Do you have a reference to Microsoft ActiveX Data 2.x Object Library

  Go Project -> Reference

  In the list of Available References make sure that you have

  Microsoft ActiveX Data 2.x Object Library

  The x represents either 5, 6, 7 maybe even 8!

  If you do not a reference to the Microsoft ADO 2.x library or a higher version in the list (checked or unchecked) you might have to get MDAC from Microsoft

 
0
 
LVL 1

Expert Comment

by:Metalhead
ID: 8213947
Sorry no idea about the map drive to the database
0
 

Author Comment

by:Corekill
ID: 8213988
Sorry another question

How can i set it so that this code can be used by procedure :

Set adoConnection = New ADODB.Connection
 adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\volcano.mdb;Persist Security Info=False"
 adoConnection.CursorLocation = adUseClient
 adoConnection.ConnectionTimeout = 5
 adoConnection.Open
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 8214097
you could add this to a module:

Public adoConnection As ADODB.Connection

Public Sub OpenConnection(sDataBase As String)
Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sDataBase & "';Persist Security Info=False"
adoConnection.CursorLocation = adUseClient
adoConnection.ConnectionTimeout = 5
adoConnection.Open
End Sub

Public Sub CloseConnection()
If Not adoConnection Is Nothing Then
    If adoConnection.State <> adStateClosed Then adoConnection.Close
End If
End Sub



Then use it like this:


Private Sub Form_Load()
OpenConnection "C:\volcano.mdb"
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
CloseConnection
End Sub
0
 

Author Comment

by:Corekill
ID: 8214239
Thanks for that!

I dont have anymore points at the moment but when i do i will give u some.

ps. I have this bit of code

If rsAfrica.recordcount > 0 Then
    For i = 0 To rsAfrica.recordcount
        List1.AddItem (rsAfrica.Fields("volcano_name").Value)
    Next i
End If

where do i put the [i] so it adds a different name ?
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 8214321
try this:

List1.Clear
If rsAfrica.recordcount > 0 Then
    rsAfrica.MoveFirst
    Do Until rsAfrica.EOF = True
        List1.AddItem rsAfrica.Fields("volcano_name")
        rsAfrica.MoveNext
    Loop
End If
0
 

Author Comment

by:Corekill
ID: 8214350
Thanks again you have been such a great help!

Do you know where i can read up on modules ?
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 8214417
Look up ".BAS Modules" in the vbhelp.It gives a good description.
0
 

Author Comment

by:Corekill
ID: 8214459
i dont have the msdn libary installed :(

What i wanted was to have a module that had all the different SQL statements so that i could just call them up
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 8214632
you can do that.Just create Sub Routines and/or Functions in your .bas module and make them public so they can be accessed from anywhere in your project.

This example contains a zip file called ado helper.It contains example modules for ADO or DAO.

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=34627&lngWId=1
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 8218516
Corekill, I just noticed I left something out of the CloseConnection Sub. You should set it to Nothing like so:

Public Sub CloseConnection()
If Not adoConnection Is Nothing Then
   If adoConnection.State <> adStateClosed Then adoConnection.Close
    Set adoConnection = Nothing
End If
End Sub



0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

770 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