February 13, 2012 at 1:02 pm
In SQL you can highlight all queries in the Summary tab and right click and choose "Execute Stored Procedure as" then choose "create to", then choose file. This will script all hightlighted sp's create code into one file that you can then dig into and use find functions etc. to pick apart/find what you need.
My question is, does Access have anything like that?
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 13, 2012 at 1:26 pm
kschwid (2/13/2012)
In SQL you can highlight all queries in the Summary tab and right click and choose "Execute Stored Procedure as" then choose "create to", then choose file. This will script all hightlighted sp's create code into one file that you can then dig into and use find functions etc. to pick apart/find what you need.My question is, does Access have anything like that?
not that I am aware of, but would like to know if there is.
its not necessarily the "access views" you need to consider....you can have VBA code attached to a form event / cmdbutton event etc...also Access has "modules" where other code resides
one idea...open a form in design view ..fire up the VBA code window and then use Edit>Find method to search for various "names" you believe may exist
another...have you looked at exg "modules" and what that code does?
.......sorry grasping at straws now
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 13, 2012 at 4:26 pm
Hi
From what I can see in the doc you attached in your earlier post, you are looking into a macro which opens a form - SET DATE PARAMETERS.
I don't use this method so I'm guessing a little, but I suspect that the "z_log_refresh" query simply provides data to the form when it opens. What you need to show us is the form (SET DATE PARAMETERS). I suspect that there will be one or more buttons on that form with OnClick events attached to them which perform the data updates. I suspect your answers will be in the VBA behind that form.
On second thoughts, if the original developer is using macros, then there will be macros behind those buttons.
Regardless, I think the next link in the chain you need to examine is the form.
Cheers
Rowan
PS I think that query is simply recording who is opening the form (and perhaps doing an update) and at what time. Something along thjose lines anyway.
February 13, 2012 at 5:02 pm
kschwid (2/13/2012)
In SQL you can highlight all queries in the Summary tab and right click and choose "Execute Stored Procedure as" then choose "create to", then choose file. This will script all hightlighted sp's create code into one file that you can then dig into and use find functions etc. to pick apart/find what you need.My question is, does Access have anything like that?
Not really, because you are looking at local tables created from SQL Server tables using queries. Was this situation created by an external company they hired to do this for them, or is it something an individual created over a long period of time? If you have decent documentation of the SQL Server database that will help somewhat, but the choices made by the previous developer of the Access front-ends as to what tables or subsets of tables can only be ascertained by looking at the queries.
We also found that in most cases you don't need to import SQL Server tables into Access to produce reports and get acceptable performance. SQL Server views will do the trick nicely and you can link to them as though they are just another table. Base on your description of the situation I'm afraid you will need to pick one or more of the Access databases apart query by query till you get an understand of what they are doing.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 14, 2012 at 6:23 am
I do believe that that file I sent is just a login and date recorder. I do think that these are utilized as parameters for the reports that run behind other buttons in the "application".
I did continue to dig and look into macros, which I hadn't done much of previously I mostly just looked individual queries and table structure, and in the macros it calls many queries in concession, I think this will help me make more sense of the small individual queries now that I can see how they are working together.
Thanks for the suggestion.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 14, 2012 at 6:31 am
The situation is created by an external part time contract employee and an internal employee (whom is quite touchy about his work, so I can't get much out of him since I am basically going to be recreating his work and then tossing it out.). What I have gotten from people is the external employee is developing an application for the company (in VB) he is creating the SQL DB as he develops. The internal employee created these, what they are calling "Reporting Applications" which are just VB apps used strictly for reporting purposes and they use SQL and VB tables. There are many of these small applications and they can also reference each others tables. The two employees didn't work together on this and the internal employee has no concept of coding rules and conventions, so nothing is called the same or makes any sense in relation to the SQL DB.
There isn't really any documentation other than what I have been working on for the last 3 weeks since I started working here.
I will be getting us out of VB for reporting and go to SSRS, but I have to recreate all the reports. What I am beginning to think I need to do is just look at the actual front end of the application, ignore the Reporting Applications and work with people to find out what they want and just start all from scratch. If they can show me in the Application I can find it in the SQL DB. Its just the reporting crap that isn't making sense.
Thanks for all the suggestions and help.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 14, 2012 at 7:21 am
You are very welcome - you have a daunting task ahead of you for sure. As a general comment, the tools in Access offer a great way of displaying and editing data with minimal development effort. Reports in Access are also pretty powerful, and can be controlled with form menus, but if users get to do their own, things can get out of hand quickly. SSRS offers an alternative that is easier to control, but is separate from the editing and display tools unless you do a .NET interface - which requires more development effort. Here's hoping that all goes well once you figure out what you have, and what they need.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 15, 2012 at 11:22 am
Have you looked for SQL Server DTS jobs that export the information to Access?
February 15, 2012 at 11:41 am
There are a couple of jobs that run, all of our backups of course and then there are a couple of Jobs that Command window says "DTS Run" but the rest doesn't make sense to me. I have attached a screenshot of one of them.
I have never looked at DTS job before so any help would be great.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 15, 2012 at 11:56 am
In the Enterprise Manager you have to go in to the Data Transformation Services node and then look in the Local Packages node. If you open up a DTS package you will probably be able to understand what it is doing. If it is an export you should see a connection and then a line to another connection. If you click on the line you will see the SQL being used.
February 15, 2012 at 12:15 pm
Ok, so I did that and unfortunately all of them are related to Shipping and Receiving from the UPS System to ours, which is not what I am looking for.
There has to be something that runs from an stored procedure during the day through the applications or something because everything is current all day long. There has to be something that is really only for functional purposes and not user purposes so its buried on the network in a separate Access Database that just gets reference by the others, because I have dug into what I have for 4 weeks now and still haven't found what I am looking for.
I think its time to just start from scratch and recreate reports on my own for SSRS rather than trying to reference/recreate the current ones that are in Access. It might be easier at this point to just sit with a user and have them point in the main application what they want on a report. I can find in the SQL Database based on that, its the Access stuff that is ridiculous.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
February 15, 2012 at 12:33 pm
You might want to start from the beginning again.
1. Open a report and check the Record Source for the report. You can see the query it references by clicking the elipses at the end of the field.
2. In that query you can see where the data is coming from. That could be an Access table, a linked SQL Server table or another query. If needed follow the queries back until you get to tables.
3. Look at those tables in the navigation pane on the left. If it is an Access table you will see a gray symbol looking like a table - it may have an arrow in from which means the table actually exists in some other Access database. SQL Server tables will have a globe (like the common internet symbol). SQL Server views will look like a SQL server table (they are treated like tables in Access). There is a good possibility that one of the Access queries and/or SQL views give aliases to the fields.
It is worth doing this to make sure where the data is coming from. Given that you say the data is always current I expect that you are actually going against SQL Server tables or views.
February 15, 2012 at 12:38 pm
kschwid (2/15/2012)
I think its time to just start from scratch and recreate reports on my own for SSRS rather than trying to reference/recreate the current ones that are in Access. It might be easier at this point to just sit with a user and have them point in the main application what they want on a report. I can find in the SQL Database based on that, its the Access stuff that is ridiculous.
going back to your original post
So here's the deal. We have a MS SQL Server 2000 database (will be upgrading to 2008 R2 in a couple of weeks), we have an "Application" written in Access 2010. The main application references the MS SQL DB, but then we have these "Mini-Applications" which are more for Reporting rather than changing of data. These "Report Applications" use Access DB, the tables are obviously populated somehow from the SQL DB but when it was written items where not named the same, table structures are not the same.
I think if I were you, I would start with a user of one of the "mini report apps"...get them to show you the report they use....open that in design, examine the report data source (click on elipses to bring up the query designer)...this should now clearly show what tables that report is using.
from there you can then start to consider how you are going to build a replica in SSRS.
just my twopenneth 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 15, 2012 at 3:47 pm
Not sure this is going to help...But I developed applications in MS Access and what you need to consider trying to track what happens and where is these reports are objects and usually certain events i.e.On Load, On Open...trigger either macros to run or processes to run.
So a lot of the reports can have data updated or refreshed when the report is run.
Check all the calls around the events and you should be able to trace what's happening.
Also, look at it as a challenge and dive down deep into it.
Good Luck.
February 16, 2012 at 10:06 am
There's also an opportunity for the "do it once, do it right" approach that non-IT people have no regard for. As I was reading all the posts, I kept thinking, "Man, it sounds like people using a hammer to install a screw because that's the only tool that they know. I'd lobby for chucking the whole Access-based garbage and do it in SSRS." I'd also bet that as you meet with the users (actual consumers of the "reports") you'll find out all kinds of things they do to a report to "make it work for me" and some that aren't used at all.
I think you're better off not wasting time with a non-sustainable process.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply