asked on Search Form Across multiple Access and SQL databases
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
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
Microsoft AccessMicrosoft SQL Server
Last Comment
zipnotic
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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 ?
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.
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.
ASKER
Much appreciated.