Results 1 to 11 of 11

Thread: Old School Database question - Genius needed!

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    Old School Database question - Genius needed!

    Hey guys. I've got an old school Access database that I'm searching with VB6. SQL Server is not available at the moment and I'd like to avoid it if possible.

    I'm using just a good old ADO connection but I can add indexes to the database as needed.

    Can this be done with just .index and .Seek then a While loop to loop through the results?

    I've got 5 different fields. Each field can hold one of say 20 different text values but I'll simplify below to show just 3 per field. There are about 850,000 records.

    I need to search for any combination of values and get results reasonably quickly.

    So for example here's the 5 fields with the possible values they could contain.

    Field 1 - Cat, Dog, Mouse
    Field 2 - Frog, Banana, House
    Field 3 - Brown, Red, Green
    Field 4 - Rock, Paper, Scissors
    Field 5 - Mango, Pepper, Razor

    How would I search for all records where:

    Field 1 contains both Cat & Dog
    Field 2 Contains Frog OR House
    Field 3 contains Red
    Field 4 contains Rock and Scissors
    Field 5 contains Mango and Pepper.

    Any suggestions or is this just impossible?

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Database Question

    So are you saying that field one can contain both cat and dog in the same record? You start by saying the field can hold one of 20 different values but then indicate you want to find two values the same field within the same record. That seems a bit odd.

    In any case you would want indexes on the key fields and you could simply use a SQL statement to query the db using where clauses with and/or clauses as needed

    The cases where you seem to be expecting two values in the same field could be an issue, especially if there is not a definite pattern to them. For example cat and Dog Dog and Cat Cat, Dog Cat & Dog on so on. So to create the query one would need to know what the data in the field looks like but you gave no example of what a record would look like that meets your search criteria.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Old School Database question - Genius needed!

    Err? How is Field 1 supposed to contain BOTH values Cat + Dog (at the same time)?
    As for your criteria: Only OR makes sense
    Field1 contains Cat OR Dog
    Field2 contains Frog OR House
    Field3 contains Red
    Field4 contains Rock OR Scissors
    Field5 contains Mango OR Pepper

    Next: Why in Blazes ADO? Why not just SQL the whole thing?

    Next: Are the criteria interconnected?
    As in: Field1=Cat OR Dog AND Field2=Frog OR House AND etc.....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Database Question

    Quote Originally Posted by Darkbob View Post
    I posted this in Database Development but it's actually more of a VB question because I'm using VB.
    Then you ought to have asked the mods to move the original thread. Posting the same question in multiple forums is frowned upon.

  5. #5
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Old School Database question - Genius needed!

    Quote Originally Posted by Zvoni View Post
    Next: Why in Blazes ADO? Why not just SQL the whole thing?
    If he is using VB6 then he probably uses ADO, what's the problem with that? Or do you mean to say he needs to just retrieve the records using a criteria in sql instead of bringing all the records and identifying the matching records?
    Regards,

    â„¢

    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Old School Database question - Genius needed!

    dee-u,
    ok, i misread something.
    Yes, he needs ADO to access that thing from VB6, but in his post he mentiones the .index and .seek methods of the Recordset-Object.
    My Statement, why not just SQL that thing, was regarding using those methods
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Database Question

    Not only that, but now you'll get two sets of answers... as well as two separate discussions that are not cognizant of the other...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Old School Database question - Genius needed!

    Thankfully we have a rather natty merge function.

    I've merged you thread from the database section into this one since that seems to be your preference but I'd have thought this is more of a Database question. Particularly because you seem to be suggesting that you pull the entire datatable back and then process it in the client - that would be the wrong way to go about this. You'd be far better constructing an appropriate Where clause and only bringing back the records you need.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Old School Database question - Genius needed!

    Quote Originally Posted by FunkyDexter View Post
    You'd be far better constructing an appropriate Where clause and only bringing back the records you need.
    ....what i said....SQL that thing....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2005
    Posts
    558

    Re: Old School Database question - Genius needed!

    First, thanks very much for all of your replies.

    I really simplified my initial question but the real problem is so much worse. The database is such an unstructured mess it's impossible to describe it. There are 850,000 records. Each record has 19 fields with essentially random english text. Inside each of those fields there MIGHT be one of 40 key words from two different lists. Or there might not. And let's not forget the three memo fields. Plus the customer wants to correlate his search across three different tables. Arrrgh.

    Asking VB to find any random combination of a series of random colored needles in a field of gigantic hay stacks is simply unrealistic.

    What I decided to do was write a program go go through the data (17.8 million searches) and extract only the individual key words. Then I can pop those key words into a separate table with a link back to the initial record. Essentially pre-indexing and organizing things a bit. It makes the project a lot more manageable.

    Now I can use a SQL statement employing the use of LIKE to search through a handful of fields for a handful of key words.

    As an example, the customer wants to find anybody taking a Statin or a Nitrate for either Hypertension or Stroke or Pulmonary Fibrosis.

    I load the drugs up into a record with ITEM1 containing the drugs they are on and ITEM2 with a field containing the conditions they might have.

    I've checked that the keys are unique and aren't found inside each other (ie AF is a key for "Atrial Fibrillation" but the letters AF could easily be found inside another key).

    In the end the search looks something like this:

    Select * from MyTable WHERE (ITEM1 like '*STATIN*' or ITEM1 like '*NITRATE*') AND (ITEM2 like '*HTN*' or ITEM2 like '*PULMONARY FIBROSIS*' or ITEM2 like '*STROKE*')

    I'm sure I've got a lot of work left to do before this thing does what I want but at least I'm off to a better start now.

    I feel I've learned a LOT so thanks for your help.
    Last edited by Darkbob; Feb 12th, 2020 at 11:49 AM.

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Old School Database question - Genius needed!

    You might want to make an attempt with converting your MDB to an SQLite-DB,
    then making use of the built-in Full-Text-Search (FTS4 or FTS5)...

    The feature allows superfast-text-scans (in a "Google-like" AND/OR/NOT fashion) for either:
    - the content of the whole FTS-table (on all fields)
    - or only on specified field-list within that table

    The beauty is, that you can perform "singular inserts, updates or deletes" on
    such an FTS table (like on any normal one), without having to "manually update secondary search-tables".

    HTH

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width