Solved

How to load specific records from Bible database

Posted on 2004-09-06
11
438 Views
Last Modified: 2013-12-25
I have the entire Bible stored in an access database.  The name of the Bible books are stored in text fields.  The chapter numbers of the bible books are stored in integer fields.

My question is how can I load and display specific books/chapters using queries?

Example: How can I load only verses in Genesis, Chapter 5, verse 2  ?

I was attempting to use the data control but could not figure out a solution.
0
Comment
Question by:mcdonald_g
[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
  • 6
  • 4
11 Comments
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11989927
You will have to provide informaion about database definition (tables, field names) if you want somebody to help you with query
0
 

Author Comment

by:mcdonald_g
ID: 11990015
The database structure is as follows:
Table = Bible
Field 1 = Books (Text format) 'This is the name of each book of the Bible
Field 2 = Chapters (Integer format) 'This is the Chapter numbers
Field 3 = Verse (Integer format) ' This is the verse numbers
Field 4 =  Text (Memo format) 'This stored the text of each verse in the Bible
0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 11990065
You'll need an SQL query something like:

select field4 from bible
where field3 = 2
and field2 = 5
and field1 = "genesis"
0
Independent Software Vendors: 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!

 
LVL 8

Expert Comment

by:mladenovicz
ID: 11990071
SELECT * FROM Bible WHERE Books='Genesis' AND Chapters=5 AND Verse=2
0
 

Author Comment

by:mcdonald_g
ID: 11990165
mladenovicz, how do I use that query in code?  Is it useful with the data control?
0
 
LVL 8

Accepted Solution

by:
mladenovicz earned 375 total points
ID: 11990219
Something like this:

Public Function Test()

Dim adoRS   As ADODB.Recordset
Dim adoConn As ADODB.Connection
Dim sDNS    As String
   
    Set adoRS = New ADODB.Recordset
    adoRS.CursorLocation = adUseClient
       
    sDNS = YourConnectionString '--> set according your settings

    Qry = "SELECT * FROM Bible WHERE Books='Genesis' AND Chapters=5 AND Verse=2"

    Set adoConn = New ADODB.Connection
    adoConn.Open sDSN
    adoRS.Open Qry, adoConn, adOpenStatic, adLockBatchOptimistic

    If Not (adoRS.BOF And adoRS.EOF) Then Msgbox adoRS("Text")
       
    ' clean up
    adoRS.Close
    Set adoRS = Nothing
    adoConn.Close
    Set adoConn = Nothing
   
End Function

You will need reference to the Microsoft ADO.

You can use this with data control. You should set RecordSource to this query string. Before that, you should fill Databse property
0
 

Author Comment

by:mcdonald_g
ID: 11990465
mladenovicz, can you help me out one more time. I'm going to give you the points.
I tried the query in the data control and it didn't work.

what should the sDNS connnection string be?  The name of my database is "KJV.mdb", and the table is "Bible"
0
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11990507
What is the full path of KJV.mdb file (e.g. "C:\My Documents\KJV.mdb")?
0
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11990564
Try sthg like this (put Data control on form and put textbox)

Private Sub Form_Load()
    Data1.DatabaseName = "C:\db1.mdb"
    Data1.RecordsetType = 1
    Data1.RecordSource = "SELECT * FROM Bible"
    Data1.RecordSource = "SELECT * FROM Bible WHERE Books='Genesis' AND Chapters=5 AND Verse=2"

    Set Text1.DataSource = Data1.Recordset
    Text1.DataField = "Text"
End Sub
0
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11990568
You have to replace "C:\db1.mdb" with your dataabse path
0
 

Author Comment

by:mcdonald_g
ID: 12000670
Hey, I think I may have given too many points for this question.   I thought the solution would me much harder than this.  Congrats mladenovicz!  Thanks for your help as I'm almost done with my Bible project.
0

Featured Post

Independent Software Vendors: 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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…

728 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