SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MS SQL Server 2000 & Access 2010


MS SQL Server 2000 & Access 2010

Author
Message
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5509 Visits: 35456
hmmm....without having your db in front of me, it is very difficult to help you further.

one idea....is there a "start up / splash" form that is called when the Access db is opened?....does this have any VBA code behind it...look for form events on "on load" and "on open"

possibly other forms may similar events.

sorry,,,but am running out of ideas.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

kschwid
kschwid
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 264
I will try to look into those things. Thank you for all of your help.

It's a weird situation and I wish I had someone here at my work that could explain what they coded! Not sure why they did what they did anyway. If you can read from SQL why put it in Access, since there is a limit in access anyway.

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5509 Visits: 35456
kschwid (2/13/2012)
I will try to look into those things. Thank you for all of your help.

It's a weird situation and I wish I had someone here at my work that could explain what they coded! Not sure why they did what they did anyway. If you can read from SQL why put it in Access, since there is a limit in access anyway.



I wish you well....picking up someone else's code is never easy.....especially when there is no documentation / user help etc.

one comment on "If you can read from SQL why put it in Access"...dependent upon the app, you may well find better peformance from local tables than with direct SQL reads....but of course that requires the local tables to be updated ;-)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

WendellB
WendellB
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1760
INSERT INTO DATABASE_LOGIN_FILE ( [USER], [TIME], [DATE], [DATABASE], COMPUTER, NOTES )
SELECT Environ("USERNAME") AS [USER], Time() AS [TIME], Date() AS [DATE], "Refresh HD-Stat" AS [DATABASE], Environ("COMPUTERNAME") AS COMPUTER, "LOGIN" AS NOTES;

That query appears to be saving the details of the user, computer, in a table called 'Database_Login_File" which may or may not be a linked SQL Server table. As such it should appear as an Append Query in the Navigation Pane. If you want to see what queries are being used to append SQL data into local tables, sort the queries by type in the Nav Pane, and then look through queries that append to local tables - you will have to open each and check the properties - from linked SQL Server tables.

I agree with J Livingston SQL that it's quite unusual to see an apparently sophisticated database using macros that way, although the macro capability was enhanced significantly in Access 2010. Is it possible that a portion of the data is being upsized to a web or SharePoint app? The fact that there are many databases that apparently connecting to the same database for reporting purposes also is a concern. We typically put all the reports into a single database and then distribute the same front-end to all users, limiting who can see what reports with the menu and security. Otherwise you have a change control nightmare as soon as tables are modified or added.

Sorting through such a situation is essentially a long and tedious process - I've done it a number of times in the past 20 years, and it just takes time and research.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
kschwid
kschwid
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 264
The "Database_Login_File" is an Access table not a SQL table. It is from a different Access DB and just referenced in the one I was currently trying to pick apart.
I was hoping to find a way to get the information I needed without looking at every query by hand in all the Access environments(At Least 20 environments and probably 50-100 queries per environment.).

I just don't get why there isn't a simple way to show how Access and SQL relate to each other.

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
kschwid
kschwid
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 264
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 Schwid
Software Developer/Data Analyst
Ozaukee, WI
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5509 Visits: 35456
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

ProofOfLife
ProofOfLife
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 9474
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.
WendellB
WendellB
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 1760
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!
kschwid
kschwid
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 264
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 Schwid
Software Developer/Data Analyst
Ozaukee, WI
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search