Solved

How to load specific records from Bible database

Posted on 2004-09-06
11
424 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
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

730 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