December 11, 2015 at 2:07 pm
Hello.
I am in the process of migrating Ms Access tables and queries to Sql Sever. I am keeping the front end still in Access, which has lot of Forms and reports
I have successfully moved all my tables and linked the tables to front end Ms access app.
I have a main form, which has a drop down control. The whole application is driven on this combo box.
when a user selects an item from this drop down, the whole tabs in the form details section is displayed with data.
Today the record source of this Main form is a query in Ms access, to which the Combo box section is passed as a parameter (Where section of query).
I want to now get rid of this query, as I have more options after moving to Sql Server. (use a stored procedure or something)
What do you think is the best idea to redo such a form record source query.
Please suggest your valuable thoughts.
Note: Once the data is loaded on the form, users do make edits.
December 11, 2015 at 3:17 pm
It depends on the complexity of the query as to how you should proceed. In general, I would leave the query as the data source if the query is only using a single table that you are planning to update, and you are applying criteria to restrict it to a few records, or a single record. If you are joining more than two tables, then I would definitely look at using a SQL Server view as the data source. Two table joins in the query are sort of a toss up, but in any event you have to be sure that the query or the view is updateable, and views tend to be less restrictive for updates.
The bottom line is that single table queries that are constrained to a few records are actually converted in the ODBC process to SQL Server queries and only the required records are passed back to Access, rather than pulling in the full table and then executing the query in Access. We have successfully use this with mulit-million row tables and gotten excellent response time for the user.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 11, 2015 at 4:50 pm
Thank you for your valuable inputs..
The record source query has more than 10 table to join but returns single record, the number of columns are around 100+ which are fed to various controls on form tabs. All these controls are editable for the user.
This is the scenario, I have 100 users who will be working on this application modifying the data.. Hope I am more clear now.
Please input your thoughs
December 12, 2015 at 4:56 pm
Unfortunately, Access queries that join more than 2 or 3 tables will want to bring in each table and run the query in Access. I would suggest creating a view that combines the main tables, and then link to that view. Views in SQL Server are treated as tables in Access when you link to them. You do want to make sure Access thinks the view has a primary key, or the result will not be updateable. You might however want to consider not including lookup tables where you have them, and use a combo box to display the text value contained in the lookup table. Access is pretty efficient when it comes to using combo boxes. That might reduce the number of fields in your view.
You may also find it useful to consider some sort of deployment process, as applications like this almost always need changes over time, and you don't want to try running all 100 users out of the same front-end database - we tried it many years ago and concluded you need to put the front-end on each individual workstation. Another issue to think about is capturing who makes what changes - that can often be useful in identifying training issues with users. Post back if you have further questions or concerns.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 12, 2015 at 7:25 pm
To add to what Wendell said, there are several things you need to be aware of when using Access as a front end to SQL Server. To take advantage of the SQL Server database engine, you should move all your queries to SQL Server where possible (convert them to views and stored procedures). index all foreign keys in your tables. then convert as many of your Access queries to stored procedures or views. If you use an Access function in a query in the front end, you could force Access to download a LOT of data and running the query in the front end. End result - painfully slow database.
December 15, 2015 at 10:31 am
Thanks for the inputs..
I have Master form with lot of controls, the record source for this form is a query which returns single record and this data is fed to all the control on the forms. This query is driven by two form combo box controls.
Query is shown below, How can I convert this to sql server??? Please note that when the form is loaded with the data, the controls must be editable means the data for the record loaded must be editable..
Select * from
FROM (((PMProjects INNER JOIN tblPMProjectsIntake ON PMProjects.ProjectNumber = tblPMProjectsIntake.ProjectNumber) INNER JOIN tblPMProjectsDates ON PMProjects.ProjectNumber = tblPMProjectsDates.ProjectNumber) INNER JOIN tblRequestLinks ON PMProjects.ProjectNumber = tblRequestLinks.ProjectNumber) INNER JOIN tblRoles ON PMProjects.ProjectNumber = tblRoles.ProjectNumber
WHERE (((PMProjects.ProjectNumber)=[forms]![frmMasterPMProjects].[Combo15])) OR (((PMProjects.ReqTitle)=[Forms]![frmMasterPMProjects]![Combo19]));
Thank you very much..
December 15, 2015 at 10:53 am
If you stripped off the WHERE clause, it would be a valid query in SQL Server. I would convert the parameters to SQL Server, though.
--put in the real field list...
CREATE PROCEDURE GetMyData @ReqTitle INT, @ProjectNumber INT
AS
Select * from
FROM (((PMProjects INNER JOIN tblPMProjectsIntake ON PMProjects.ProjectNumber = tblPMProjectsIntake.ProjectNumber) INNER JOIN tblPMProjectsDates ON PMProjects.ProjectNumber = tblPMProjectsDates.ProjectNumber) INNER JOIN tblRequestLinks ON PMProjects.ProjectNumber = tblRequestLinks.ProjectNumber) INNER JOIN tblRoles ON PMProjects.ProjectNumber = tblRoles.ProjectNumber
WHERE (((PMProjects.ProjectNumber)=@ProjectNumber)) OR (((PMProjects.ReqTitle)=@ReqTitle));
December 15, 2015 at 11:22 am
How can this be associated with Ms Access form record source?
December 15, 2015 at 2:31 pm
You cannot easily make a SQL Server query the data source for an Access form. The simple approach is to bind the form to the SQL Server view, and then use the combo box values as filters on the form. That will give you good performance. Unfortunately the results of a stored procedure are not generally updateable as far as Access is concerned. There are some complex procedures that require extensive use of pass-through queries or complex VBA code to manage an unbound form, but we've managed some rather large projects using the approach outlined, and they've been running well for over ten years.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 16, 2015 at 7:50 am
Wendell's comments are spot on.
I would add that if you have 100 users, then make sure that each user has their own copy of the Access file.
EDIT: oops, he already said that. but it bears repeating.
December 16, 2015 at 7:56 am
Thank you Wendell and William for valuable inputs..
Even before conversion I made the application split, each of our users have Accde file on thier desktops.
I will first convert the master query to a view in SQL and have that linked with MS Access.
Thanks again, I will reply back on the results..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply