Pass Through or local Query (1 Viewer)

I have a split SQL/Access database and an entry from with around 100 fields on it that gets split into 8 other tables.

I currently have it setup as a 1 pass through query with multiple INSERT and UPDATE clauses, which works just fine. I'm just wondering if it would operate any faster, or slower, if i had it as a local query in Access and use multiple APPEND and UPDATE queries to accomplish it.

Not really sure on the differences in performance pushing the query across the network to work on the SQL server or having it local and moving data between linked tables.

Of course I can build the queries and test it for results, but can someone explain best usages of Pass Through vs Local queries?

thanks
Kevin

Sounds like an unnormalized procedure if you are not using a bound form and a single record causes insert into 8 tables.
Pass through queries would probably be slightly faster since there is less local overhead. You can use a pass through query whether or not the SQL Server tables are linked. Just because your inserts are done with a pass through doesn't mean that updates can't be done would a bound Access form and an access select query.

Pat Hartman said:

Sounds like an unnormalized procedure if you are not using a bound form and a single record causes insert into 8 tables.
Pass through queries would probably be slightly faster since there is less local overhead. You can use a pass through query whether or not the SQL Server tables are linked. Just because your inserts are done with a pass through doesn't mean that updates can't be done would a bound Access form and an access select query.

Click to expand...

It is 1 table with 100 columns with an auto number field to create the "Facility ID" primary key.

My data entry form is bound to that table, which is launched with "Add Record", to input all the info, i.e. Facility Name, Facility POCs, Phone Numbers, Emails, Programs, etc.

After all the info is entered, you hit the Create Facility button which then inserts the Facility ID and applicable info into each respective table, which are all tied to the Facility Names table on the Facility ID

After the entry, I open the Facilities Form, searchable by a Facility Name drop down menu, and display the info via a tabctrl where each table is bound to a tab

After all the info is entered, you hit the Create Facility button which then inserts the Facility ID and applicable info into each respective table, which are all tied to the Facility Names table on the Facility ID

Click to expand...

Why would you not break up the data entry so that each table is updated via a separate subform? You can use a tab control to separate the subforms. Having 100 fields on a single table seemed way too many and you confirmed that. I'm not sue what the purpose of doing the data entry this way is, especially since you also have a form set up the way I described using a tab control.

Adds and edits are normally bound by the same validation rules. By making separate forms for add and edit, you are requiring that the validation code appear in two places (unless you modularized it and call it from two places). This just seems like more work than it has to be, both for you and the user.

Pat Hartman said:

I'm not sue what the purpose of doing the data entry this way is, especially since you also have a form set up the way I described using a tab control.

Click to expand...

Currently the input table is populated/updated over a course of 2-3 weeks as information is made available, either manually or via Fillable PDF form imports (still working on this piece).

Once all info is available and a contract is signed, the Facility is created in the database and then my coworkers are able to work with that information.

When I designed this about 3 years ago, it was as just a shared Access Database with a Facility Names table, a Facility Information table and an "Add New Facility" button, but over the years as business grew, several new "features" have been added so I split the information piece up with a tabctrl and decided to go with a single input form to keep "New Sites" separated from "Active Sites'' as I needed to be able to do reports for both.

I now have it as a split database with all queries being local and 1 being pass-through, which is why I asked what was the better route. I am also am using Security Levels, tied to the logged on PC user, to make different parts of the database available depending on what they need to do, so I've also been contemplating a setup like you suggested.

The plan would be to Create a new facility record and since everything is related on the Facility ID, I would set a flag to identify it as a "New Facility" which could be use to prevent it from showing in any drop downs or reports, until its been finalized. Yet I could still modify the info as needed as I could set my permission to be able to see/modify ''New Facilities"

I wouldn't be using a pdf as my data entry source but whatever works for you.

Pat Hartman said:

I wouldn't be using a pdf as my data entry source but whatever works for you.

Click to expand...

Why is that?

I made the PDF and send it to our sales people, the fill it out and send it back. Currently I am hand jamming their info into the DB, but with the help of TheDBGuys code to parse out the fillable form, I am planning on using that to import the info from each field, which matches the column names and formatting of my tables.