Hi, I am not a computer programmer. I have been learning VBA along the way of making this script. I know Access and SQL.
I have a very large database with many tables and queries. My goal is to write a script or function that will search the entire database for a certain string (actually two strings).
I have tried using a series of queries and unioning them together but this is a very scrappy way of doing this. Perhaps there is a method of automatically searching every field in every table for a string? I'm sure this has been done before but I have been searching the internet for about a week and have come up with nothing, so here I am, asking for help.
Any input is GREATLY appreciated; I am getting flustered due to my lack of knowledge with VBA!
Thanks!
Ian
9 16377 ADezii 8,834
Recognized Expert Expert
Just subscribing, when I get a chance I'll see what I can come up with.
Delerna 1,134
Recognized Expert Top Contributor
Hi, this is an extract from some code I had in an Access form
It should get you started -
Private Sub cmdSearchTheTables_Click()
-
Dim DB As dao.Database
-
Dim tbl As dao.TableDef
-
Dim fld As dao.Field
-
-
Set DB = CurrentDb
-
For Each tbl In DB.TableDefs
-
For Each fld In tbl.Fields
-
If fld.Value = "Search string" Then MsgBox "Found it"
-
Next
-
Next
-
Set tbl = Nothing
-
Set fld = Nothing
-
Set DB = Nothing
-
End Sub
-
you will of course need a reference to "Microsoft DAO 3.6 object library"
Ah! The code makes sense to me, and I have DAO 3.6 installed and enabled.
I have attached the code to a button called cmdSearchTheTab les.
However, it says the IF statement - If fld.Value Like "*MK 5*" Then
is an invalid operation.
I tried a few different syntax forms for the line, but it still returned the same error.
Am I missing something? Do I need to assign a text field to output the result into?
OK. I have added much to the Sub. My code now reads as: -
-
Public Sub GenerateFieldSizeInfo()
-
Dim cdb As database
-
Dim oTable As TableDef
-
Dim oField As field
-
Dim rsTable As Recordset
-
Dim strSQLTable As String
-
Dim iMaxSize As Integer
-
Dim iHeadroom As Integer
-
Dim iFileNumber As Integer
-
Dim strOutputFile As String
-
-
Dim ddb As database
-
Dim dtbl As TableDef
-
'Dim dfld As field
-
-
'Create the header file. (If the file already exists, its contents are overwritten.)
-
iFileNumber = 1
-
strOutputFile = "C:\etc,etc."
-
-
Open strOutputFile For Output As #iFileNumber
-
'Open the NWSS database.
-
Set cdb = CurrentDb
-
-
Print #iFileNumber, " Table"; Tab(40); "Field"; Tab(70); "Relevant Text"; Tab(101); "Size"; Tab(111); "Max"; Tab(117); "Headroom"
-
Print #iFileNumber, String(125, "-")
-
For Each oTable In cdb.TableDefs
-
'If (StrComp("tbl", Left$(oTable.Name, 3)) = 0) Then
-
For Each oField In oTable.Fields
-
If (oField.Type = 10) Then
-
Print #iFileNumber, " "; oTable.Name;
-
Print #iFileNumber, Tab(40); oField.Name; Tab(104 - Len(Str(oField.Size))); oField.Size;
-
strSQLTable = "SELECT " + "[" + oField.Name + "]"
-
strSQLTable = strSQLTable + " FROM " + "[" + oTable.Name + "]"
-
strSQLTable = strSQLTable + " ORDER BY " + "[" + oField.Name + "]"
-
Set rsTable = cdb.OpenRecordset(strSQLTable, dbOpenDynaset)
-
If Not rsTable.EOF Then
-
rsTable.MoveFirst
-
End If
-
Do Until rsTable.EOF
-
If oField.Value Like "*Search text*" Then
-
Print #iFileNumber, Tab(70 - Len(Str(oField))); oField
-
End If
-
rsTable.MoveNext
-
Loop
-
iMaxSize = 0
-
Do Until rsTable.EOF
-
If (Len(rsTable.Fields(0).Value) > iMaxSize) Then
-
iMaxSize = Len(rsTable.Fields(0).Value)
-
End If
-
rsTable.MoveNext
-
Loop
-
iHeadroom = oField.Size - iMaxSize
-
Print #iFileNumber, Tab(114 - Len(Str(iMaxSize))); iMaxSize;
-
Print #iFileNumber, Tab(124 - Len(Str(iHeadroom))); iHeadroom
-
rsTable.Close
-
End If
-
Next
-
Print #iFileNumber, ""
-
'End If
-
Next
-
Close #iFileNumber
-
End Sub
-
I am still getting the "run-time error '3219', invalid operation" message when I try to run the script. It pertains to the IF statement in line 40. Anyone have any suggestions? I am puzzled because this should work!
Just out of curiosity's sake, in what real-world situation would you need to search every field of every table for a given search string?
Welcome to Bytes!
Linq ;0)>
Just out of curiosity's sake, in what real-world situation would you need to search every field of every table for a given search string?
Welcome to Bytes!
Linq ;0)>
This is a task given to me for my job. It is to find data sensitive to a certain location. The script will be able to be used on any database made in the future; henceforth, creating a script to search every field is the most efficient way to go about this.
After the script is fully functional on VBA, I plan to translate it into VB and have it read a given database which is inputted by the user.
Delerna 1,134
Recognized Expert Top Contributor
hmmmm
the function LIKE compares two strings.
Maybe its having a problem with one of them being a field object.
Try -
Dim str as string
-
str=fld.value
-
if str like "*MK 5*" Then
-
ADezii 8,834
Recognized Expert Expert
I've written an Algorithm for you that will search every Field in every Table for a Search String that you specify. System Tables, those prefaced with 'MSys' or 'USys', are ignored. If a match(es) are found, the Record Number, Table Name, Field Name, and the actual Field Value containing the String are written to the Immediate Window. The Function will return True if successful, otherwise False. The code is very flexible in that the results can easily be saved to a Table, written to a List Box, printed to a Text File, etc. with little modification. You can simply call the Function within an Expression passing it the required Search String, then test for success. I did my testing on all the Tables of the Northwind Database, looking for the String 'Seafood'. For testing purposes, I added this String to several Fields, in multiple Tables. The Function Call and subsequent results are posted below: - Public Function fSearchForString(strSearchString) As Boolean
-
On Error GoTo Err_fSearchForString
-
-
Dim tdf As DAO.TableDef
-
Dim MyDB As Database
-
Dim MyRS As Recordset
-
Dim intNumOfFields As Integer
-
Dim intCounter As Integer
-
-
'Let's not get ridiculous, need at least 4 Characters
-
If Len(strSearchString) <= 3 Then Exit Function
-
-
Set MyDB = CurrentDb
-
-
DoCmd.Hourglass True
-
-
Debug.Print "The String [" & strSearchString & "] has been found in the following locations:"
-
Debug.Print "******************************************************************************************"
-
Debug.Print
-
-
For Each tdf In CurrentDb.TableDefs
-
If Not tdf.Name Like "MSys*" Or Not tdf.Name Like "MSys*" Then 'ignore System Tables
-
Set MyRS = MyDB.OpenRecordset(tdf.Name, dbOpenDynaset)
-
intNumOfFields = MyRS.Fields.Count
-
For intCounter = 0 To intNumOfFields - 1
-
Do While Not MyRS.EOF
-
If InStr(MyRS.Fields(intCounter).Value, strSearchString) > 0 Then
-
Debug.Print "Rec Num: " & MyRS.AbsolutePosition + 1 & " | " & _
-
"Table: " & tdf.Name & " | " & "Field: " & _
-
MyRS.Fields(intCounter).Name & " | Value: " & _
-
MyRS.Fields(intCounter).Value
-
End If
-
MyRS.MoveNext
-
Loop
-
MyRS.MoveFirst
-
Next
-
End If
-
Next
-
-
DoCmd.Hourglass False
-
-
MyRS.Close
-
Set MyRS = Nothing
-
-
fSearchForString = True
-
-
Exit_fSearchForString:
-
Exit Function
-
-
Err_fSearchForString:
-
fSearchForString = False
-
MsgBox Err.Description, vbExclamation, "Error in fSearchForString()"
-
DoCmd.Hourglass False
-
If Not MyRS Is Nothing Then
-
MyRS.Close
-
Set MyRS = Nothing
-
End If
-
Resume Exit_fSearchForString
-
End Function
Call to Function: - If fSearchForString("Seafood") Then
-
MsgBox "Search for String a success!", vbExclamation, "Success"
-
Else
-
MsgBox "What in the world have you done, ADezii!", vbCritical, "Failure"
-
End If
Sample OUTPUT: - The String [Seafood] has been found in the following locations:
-
******************************************************************************************
-
-
Rec Num: 8 | Table: Categories | Field: CategoryName | Value: Seafood
-
Rec Num: 12 | Table: Employees | Field: LastName | Value: Seafood
-
Rec Num: 5 | Table: Employees | Field: Title | Value: Sales Seafood Manager
-
Rec Num: 1 | Table: Products | Field: ProductName | Value: Chai Seafood
-
Rec Num: 23 | Table: Products | Field: ProductName | Value: Tunnbröd Seafood
-
Rec Num: 37 | Table: Products | Field: ProductName | Value: Seafood Gravad lax
-
Rec Num: 4 | Table: Shippers | Field: CompanyName | Value: Seafood Express
-
Rec Num: 19 | Table: Suppliers | Field: CompanyName | Value: New England Seafood Cannery
Perfect! Thank you very much. I played around with the code for a bit and I ended up combining my script with yours, and it works perfectly!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Michi |
last post by:
I was wondering what the best solution is for making large numbers of
TEXT (or BLOB?) fields searchable. For example, if I have a forum,
what is the best way to be able to search for specific words? How
about exact phrases?
I saw a solution where all words are preindexed in a "dictionary" like
table and then another table stores the word matches. That seems
really fast, but it has two major problems: 1) it can't do exact
matches, and 2)...
|
by: Paul H |
last post by:
I have a text file that contains the following:
********************
__StartCustomerID_41
Name: Fred Smith
Address: 57 Pew Road
Croydon
|
by: junky_fellow |
last post by:
Can anyone suggest some efficient way to search a substring in
a text file. For eg. suppose i want to search the pattern "abc"
in a text file, then which algorithm should i use.
I just want some hints, not the complete program.
thanx in advance.....
|
by: Geoff Jones |
last post by:
Hiya
I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:
I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.
|
by: Jon Beverley |
last post by:
I am looking for some assistance with searching a third part LDAP database.
I have managed to read from it and write to it using
Dim objAD As ActiveDs.IADs
Dim objDS As ActiveDs.IADsOpenDSObject
Dim objContainer As ActiveDs.IADsContainer
Dim strUser As String
| |
by: thehumantrashcan |
last post by:
Hi,
This is the first database I have ever created, so please bear with me.
I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).
I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)
SELECT
word
|
by: Hunk |
last post by:
Hi
I have a binary file which contains records sorted by Identifiers
which are strings. The Identifiers are stored in ascending order. I
would have to write a routine to give the record given the Identifier.
The logical way would be to read the record once and put it in an STL
container such as vector and then use lower_bound to search for a
given identifier. But for some strange reason i'm asked to not use a
container but instead...
|
by: neilio2j |
last post by:
hi, can anyone tell me how to search a given directory using C++?
so far i have been successful in searching a file named, for tags"h:\\tags.cpp",
whereas i would like to search for all files in the entire h: directory.
this is the code i have used for searching the file named, "h:\\tags.cpp",
/*char filename;
cout<<"Please enter the filename";
|
by: Kassimu |
last post by:
Hi guys out there,
There is this database Iam creating, I have a table with 40 fields among which there are Date/time, Text, Number, Memo and Yes/No fields and I have created the form bound to that tabe. I have also created Unbound form (for searching purpose) which is similar in layout with the bound one; in this search form the user will fill-in the keywords in as many text boxes as he wish to narrow down the search. The search results are...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |