?
Solved

Search Form Across multiple Access and SQL databases

Posted on 2012-03-18
4
Medium Priority
?
323 Views
Last Modified: 2012-03-19
Hello,


In this question I'm looking for some conceptual guidance rather than specific help.

I am creating an Access front end to make it easier for users to view or write information from a couple of different databases.  Some of them are Access based on a network drive and a couple are on a SQL Server.  

I am hoping to start the users out on a SEARCH FORM where they can get to the RESULTS FORM by typing in any number of limiters.  I wrote a quick loop through the text boxes to create the sql statement which works fine on a single database.  Since I have multiple databases would it be fair to do the same for each database and add the matching records to a recordset and then display that recordset in the unbound RESULTS FORM? The user would choose by clicking on the row of which address they want to look and be sent over to the DETAILS FORM that uses subforms for each database based on the street address.


The nearest thing to a common key among the databases is a street address of the property.  Since the addresses may be keyed differently across db's it may be difficult.

Or is there some other way people have found to consolidate several db's onto a single tabbed form with subforms?

I inlcuded a picture of the basic layout so far.
Search.jpg
0
Comment
Question by:zipnotic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 1600 total points
ID: 37735476
My suggestion is to create a local table using a make table query against the first database you are searching.  Then run an append query to the same table against the subsequent databases that you are searching.   After all queries are completed the local table then contains the collective "matches".   The results form can be bound to the local table.

The local table would probably need to include a column or two that identifies the source system/database/table where the row originated.
0
 

Author Comment

by:zipnotic
ID: 37735550
Is there an advantage to creating a local temp table and appending to it rather than creating a recordset in memory and appending to that then displaying the RESULTS FORM ?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37738786
Two, I can think of:

1. It's a lot simpler.   For example, the append to a table is one set-oriented statement.  The append to a recordset is a procedural loop on the order of 10 statements minimum.  The unbound RESULTS form is similarly an order of magnitude more complex than one that is simply bound to a temp table.

2.  Using persistent storage means the search results survive an Access shutdown--allow you to resume where you left off, so to speak.
0
 

Author Closing Comment

by:zipnotic
ID: 37739067
Much appreciated.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question