Search multiple MS Access database files via asp.net web application

[willington79]

Hello all,

We have a payroll application that runs with MS Access. It is comprised
of several different database files and one ms access mdb file for each
client. This means there can be from 10-1000 different MDB files. We
have a web application that we designed to do various things with this
payroll application.
One of the problems is I want to be able to find what databse a
specific piece of information belongs too. For example...We have a last
name, or a social security number but we do not know which client it
belongs to. So I want to be able to type in that info and find out
which client that name or SSN belongs to.
I do not have the ability to change any settings of these
databases. I can only READ the data. I am thinking of making an "Index"
of sorts that runs every night that compiles the various information
(employee names, social Securty numbers, etc...) into XML files (one
xml file for field) that contains the client database name and the
field value. So that when a search is performed it is rather quick.
Then it can re-index every night to get the latest information. When a
match is found it displays the name of the client that it belongs too.

Does this make any sence? I am stuck at this point. I'm not sure if
this is the best way, or if I should just search each database
everytime, one at a time (zzzz)? PLEASE HELP!!!

 

[Guest]

(e-mail address removed) wrote in @f14g2000cwb.googlegroups.com:

I do not have the ability to change any settings of these
databases. I can only READ the data. I am thinking of making an "Index"
of sorts that runs every night that compiles the various information
(employee names, social Securty numbers, etc...) into XML files (one
xml file for field) that contains the client database name and the
field value. So that when a search is performed it is rather quick.
Then it can re-index every night to get the latest information. When a
match is found it displays the name of the client that it belongs too.

I would import all the data into a SQL Server database... and search SQL
server instead.

Much faster... and much more reliable.

XML files are OK, 10,000 files is still too many to manage.

 

[Willington79]

That was another option I had thought about. Place the "indexed" data
into a SQLExpress (MSDE) database for faster searching. There is
nothing I can do about the Access databases. Those must be used.

 

[David W. Fenton]

(e-mail address removed) wrote in

We have a payroll application that runs with MS Access. It is
comprised of several different database files and one ms access
mdb file for each client. This means there can be from 10-1000
different MDB files. We have a web application that we designed to
do various things with this payroll application.
One of the problems is I want to be able to find what
databse a
specific piece of information belongs too. For example...We have a
last name, or a social security number but we do not know which
client it belongs to. So I want to be able to type in that info
and find out which client that name or SSN belongs to.
I do not have the ability to change any settings of these
databases. I can only READ the data. I am thinking of making an
"Index" of sorts that runs every night that compiles the various
information (employee names, social Securty numbers, etc...) into
XML files (one xml file for field) that contains the client
database name and the field value. So that when a search is
performed it is rather quick. Then it can re-index every night to
get the latest information. When a match is found it displays the
name of the client that it belongs too.

Full-text indexing sounds like the answer to me. It wouldn't require
importing into SQL Server, and the resulting problems that keeping
that data up-to-date would entail. It would only require keeping the
index updated.

I have no recommendations for which software to use. I'd choose
something whatever is best supported along with ASP.NET.

Sounds like a relatively simple solution to *me*, but I've never
developed in ASP or ASP.NET and I've never worked with full-text
indexes.

 

[Willington79]

I think I will write a full-text indexing tool that stores everything
in various XML files for quick searching via the asp.net page.

 

[David W. Fenton]

I think I will write a full-text indexing tool that stores
everything in various XML files for quick searching via the
asp.net page.

Why wouldn't Index Server just do the job without having to custom
write anything at all?

 

[Willington79]

What is Index Server?