Solved

How to load specific records from Bible database

Posted on 2004-09-06
11
405 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

808 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