-
Feb 11th, 2020, 02:44 PM
#1
Thread Starter
Fanatic Member
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?
-
Feb 11th, 2020, 05:15 PM
#2
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.
-
Feb 11th, 2020, 06:05 PM
#3
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
-
Feb 11th, 2020, 07:19 PM
#4
Re: Database Question
Originally Posted by Darkbob
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.
-
Feb 11th, 2020, 08:01 PM
#5
Re: Old School Database question - Genius needed!
Originally Posted by Zvoni
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?
-
Feb 12th, 2020, 02:39 AM
#6
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
-
Feb 12th, 2020, 07:55 AM
#7
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
-
Feb 12th, 2020, 08:43 AM
#8
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
-
Feb 12th, 2020, 09:05 AM
#9
Re: Old School Database question - Genius needed!
Originally Posted by FunkyDexter
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
-
Feb 12th, 2020, 11:33 AM
#10
Thread Starter
Fanatic Member
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.
-
Feb 16th, 2020, 06:59 PM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|