September 14, 2017 at 3:26 pm
One our network we have an Access front end and back end. The front end contains the queries, forms and other non-table objects. The back end is where the tables are maintained.
I ran the migration utility and converted the access tables to SQl Server. Next I converted the linked tables in the front end to SQL and renamed them so they can reference the existing queries and VBA code.
My sup suggested I move the queries to the back end. Does that make sense? Of course if I did that the front end code would have to be modified. Personally I don't think it's worth the effort.
The database is relatively small - less than 250 g.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 14, 2017 at 3:44 pm
Depends if there are performance issues. If not, don't worry about it. If the Access app is slow (search screens, reports, etc), you will get much better performance converting queries to views or stored procedures - the SQL Server db does the processing, not the Access front-end
September 14, 2017 at 3:52 pm
The biggest thing is to remove all the VBA data functions that are in the database. Push all the data processing type stuff to SQL Server.
September 14, 2017 at 4:10 pm
This small application has a ton of small update queries. It is fed by a daily extract from another must more robust application. We would have been better served if they tweaked this application instead of extracting data from it and feeding it into access.
The extract is ugly because the robust application does not have a column to track when specific columns in tables have been updated. So the developer compares data from the extract with data in Access. Not a good way to run an airline. Makes me queasy.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 14, 2017 at 5:05 pm
I'm sure.
You could do the cleanup in SSIS, and then once clean, insert the cleaned data into your final table. No MS Access required. And schedule the jobs and forget out them (well, for the most part).
September 15, 2017 at 5:32 pm
fizzleme - Thursday, September 14, 2017 4:10 PMThis small application has a ton of small update queries. It is fed by a daily extract from another must more robust application. We would have been better served if they tweaked this application instead of extracting data from it and feeding it into access.
The extract is ugly because the robust application does not have a column to track when specific columns in tables have been updated. So the developer compares data from the extract with data in Access. Not a good way to run an airline. Makes me queasy.
If indeed you are running airline, I just gave up flying! Without a column in the robust(???) app that tracks the change date, trying to pick out changed records is the needle in the haystack problem. It's pretty easy to create a trigger to indicate when a record was last edited. But I suspect you aren't allowed to touch the "robust" app. If you are stuck with Access, our experience is that 90% of the queries run just fine, and you may want to create a few SQL Server views to solve specific performance issues as they are identified. Experience with more than 20 years of working with Access as the front-end to SQL Server back-end tables.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
September 20, 2017 at 4:27 pm
Let's follow up a bit on the topic of placing the queries in the newly minted SQl server back end.
Our goal is to provide an access front end and sql server back end. We also want to move the queries and other database objects to SQl Server.
Of course, this means the access front end must be able to run a saved SQL query that is stored on SQl Server outside the access application. Does Access provide a way to do this? Both Access and SQL server will be on the same network.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 20, 2017 at 4:34 pm
fizzleme - Wednesday, September 20, 2017 4:27 PMLet's follow up a bit on the topic of placing the queries in the newly minted SQl server back end.
Our goal is to provide an access front end and sql server back end. We also want to move the queries and other database objects to SQl Server.
Of course, this means the access front end must be able to run a saved SQL query that is stored on SQl Server outside the access application. Does Access provide a way to do this? Both Access and SQL server will be on the same network.
Yes, you can run pass-through queries which execute stored procedures on the server. You can also convert queries to Views, then link to the views like any ODBC table.
Can also use ADO from Access to execute stored procedures
September 21, 2017 at 3:14 pm
You folks have been incredibly helpful. Is there a URL describing how to create pass thru queries in Access using VBA?
I'm a rookie at this. I thought I ditched Access many years ago, but it has returned to bite me in the ___.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
September 22, 2017 at 1:40 pm
fizzleme - Thursday, September 21, 2017 3:14 PMYou folks have been incredibly helpful. Is there a URL describing how to create pass thru queries in Access using VBA?
I'm a rookie at this. I thought I ditched Access many years ago, but it has returned to bite me in the ___.
Check out the video at MS Access Pass Through Query - YouTube - it is a simple way to start using pass through queries. In general, you want to store the SQL Server syntax text in a VBA string, and then edit in the parameters for the specific situation, perhaps based on user input. To do that you will need to learn about "QueryDefs" - search on that and you'll find the details and how to edit the contents of a query.
But I would strongly suggest you look at views first - they work really well where you want to look at a subset of data for a given period, or some other criteria, and it is all specified in the design of the view. If you are searching million row tables for a few records, pass-through is the way to go, but often you can build a view and then use the filter properties in Access to narrow down records based on various criteria.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply