Search a data (EmployeeName) from all the tables in the database m

[Irshad Alam]

I am looking for a code on click event of a button by which I want to search
a name based on the Textbox2 of my form. It should search as below :

Like * me.text2 * from all the exisiting table in the database.

Actually there is more than 28 table of different events and having thousand
of employee records attending the event. I want to apply a method of search
and to know that this employee has attended which all events.

The output must search the table name, its row number , so that i can figure
out the other data's of that particular row, like attended date etc.

Please advice.

Regards

Irshad

 

[Douglas J. Steele]

No offense, but having "more than 28 table of different events" certainly
sounds as though your database isn't properly normalized.

 

[John Spencer]

It seems as if you are going to need to build a work table to store the
information you retrieve from each of the 28 tables.

If you had fewer tables and the table structures were similar you might be
able to use a UNION query to combine them all for purposes of the search.

Your 28 tables should be ONE table with the needed fields plus one more field
that contains what is currently the name of each of the 28 event tables. Then
you would have one more table to link Employees to Events.

--Employees table with information about the employee
--Events table with information about the event
--EmployeeEvents table with information about the attendance of the employee
at an event.

If you cannot modify the structure and fix the data then with your current
structure there is no good way to accomplish what you want.

If I had to solve this and could not fix the structure, I would add a new
table with the needed fields and use 28 append queries to pump in the
information into the work table.

UNTESTED, INCOMPLETE Code snippet follows (good luck, you need it)

Dim strSQL as String, strInsert as String
Dim strTableName as String
Dim db as DAO.Database

Set db=CurrentDb()

'If you want to clean out the worktable, you can use the
'following
strSQL = "Delete * FROM MyWorkTable"
Db.Execute StrSQL,dbFailOnError


strInsert = "Insert into MyWorkTable (EmployeeID, EventName, EventDate)"

strTableName="BirthdayPartyEvent"
strSQL = " SELECT EmployeeID, """ & strTableName & """, EventDate" & _
" FROM [" & strTableName & "] WHERE EmployeeID = " & Me.Text2

Db.Execute StrInsert & StrSQL,dbFailOnError

strTableName="Anniversary Event"
strSQL = " SELECT EmployeeID, """ & strTableName & """, EventDate" & _
" FROM [" & strTableName & "] WHERE EmployeeID = " & Me.Text2
Db.Execute StrInsert & StrSQL,dbFailOnError

Repeat for all 28 event tables. Hopefully the 28 event tables have the same
field names. If not then you are going to have to modify each strSQL
statement to use the correct field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Irshad Alam]

Dear Sir,

Thanks for your reply.

I do agree that mdb was not managed/designed properly, the person who
created the table, chose the events in seprate table, like -
Seprate-Product-Brand-OpenHouse for a particular day, he named (designed) the
table as "MaxEngine05032001" and he input the data like EmpName, ECode etc.
in the fields as designed.

Solution will save a lot of time, please advice if it can be done via code

Regards

Irshad

 

[Douglas J. Steele]

While it might be possible to do it using code, it's a very bad idea.

I'd strongly recommend fixing the design, rather than building work-arounds.

 

[tbs]

I have this set of codes that might help you. This is in assumption that
you're looping through all the tables in the database and the field name to
search Textbox2 is the same for all the tables.

Dim iIndex As Integer
dim sTabName as string
dim rsData as recordset
dim bInAllEvents as boolean

bInAllEvents = True
For iIndex = 0 To CurrentDb.TableDefs.Count - 1
sTabName = currentdb.tabledefs(iIndex).name
set rsdata = "Select * from [" & sTabName & "] where [Field_Name]
like '*" & Textbox2 & "*'"
if rsdata.eof then
bInAllEvents = false
end if
do while not rsdata.eof
' You can read the record and do whatever needed to be done here.

rsdata.movenext
loop
Next

 

[tbs]

ops, don't know what is wrong with me today. There's a part my reply that
you'll need to change.

change
set rsdata = "Select * from [" & sTabName & "] where [Field_Name]
like '*" & Textbox2 & "*'"

to

set rsdata = currentdb.openrecordset("Select * from [" & sTabName & "] where
[Field_Name] like '*" & Textbox2 & "*'")

I have this set of codes that might help you. This is in assumption that
you're looping through all the tables in the database and the field name to
search Textbox2 is the same for all the tables.

Dim iIndex As Integer
dim sTabName as string
dim rsData as recordset
dim bInAllEvents as boolean

bInAllEvents = True
For iIndex = 0 To CurrentDb.TableDefs.Count - 1
sTabName = currentdb.tabledefs(iIndex).name
set rsdata = "Select * from [" & sTabName & "] where [Field_Name]
like '*" & Textbox2 & "*'"
if rsdata.eof then
bInAllEvents = false
end if
do while not rsdata.eof
' You can read the record and do whatever needed to be done here.

rsdata.movenext
loop
Next

I am looking for a code on click event of a button by which I want to search
a name based on the Textbox2 of my form. It should search as below :

Like * me.text2 * from all the exisiting table in the database.

Actually there is more than 28 table of different events and having thousand
of employee records attending the event. I want to apply a method of search
and to know that this employee has attended which all events.

The output must search the table name, its row number , so that i can figure
out the other data's of that particular row, like attended date etc.

Please advice.

Regards

Irshad

 

[Larry Linson]

I am looking for a code on click event of a button by which I want to
search
a name based on the Textbox2 of my form. It should search as below :

Like * me.text2 * from all the exisiting table in the database.

Actually there is more than 28 table of different events and having
thousand
of employee records attending the event. I want to apply a method of
search
and to know that this employee has attended which all events.

The output must search the table name, its row number , so that i can
figure
out the other data's of that particular row, like attended date etc.

In addition to other advice you've received, you cannot apply a Where
Condition to an entire table, but only to a specific field or fields within
the table. If you need to search more than one field in the table, you'll
need a more complex where condition. You will need a separate query for (at
least) each of the 28 tables. You can, if it is helpful, join the results of
those queries with the UNION or UNION ALL SQL operator, but UNION/UNION ALL
only applies to SQL view... you can't specify a Union Query in the Query
Builder visual interface.

From your description, it seems you also may have unstructured text fields
in those tables, and that, too, could be a complicating factor.

Did you say what you intend to do with the records you select and return
from the tables? It might help us to know how the data in the tables is
laid out and structured, what data you need, and what you are going to do
with it.

Larry Linson
Microsoft Office Access MVP