Solved

How to load specific records from Bible database

Posted on 2004-09-06
11
383 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

708 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

14 Experts available now in Live!

Get 1:1 Help Now