Click here to monitor SSC
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
kschwid
kschwid
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 264
I just started at a new company, so I am trying to find my way around there database and applications. I am having to do it on my own as they don't have the staff to sit with me, and the staff doesn't really to know what I need to find out anyway.

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.

What I need to accomplish. I am supposed to be basically recreating their reporting in SQL Server RS 2008 (will get installed in a couple weeks with SQL 2008 R2), but I am not that savvy in Access (used it a few times 6-7 years ago but not to this extent.). I cannot figure out how to find what is populating the Access tables from the SQL tables.

I have tried:
1. Utilizing the MsysObjects and MsysQueries tables in Access I am trying to find anything referencing the tables in question, but am not quite finding what I am looking for.

2. I can go in design view of one of the reports and see the form that is being called, then I open the form and see what qry is being called, but that query doesn't tell me what populates the tables that are used in the query to create the report.

There are many different Access DBs set up for the many different Access Reporting Applications, so some of the reports/dbs actually reference others, so the Create/Insert queries/procedures could be anywhere. I have tried to go through all of them with the Msys tables, but still nothing.

If anyone can help me out or at least give me another option to try it would be much appreciated.

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041

What I need to accomplish. I am supposed to be basically recreating their reporting in SQL Server RS 2008 (will get installed in a couple weeks with SQL 2008 R2), but I am not that savvy in Access (used it a few times 6-7 years ago but not to this extent.). I cannot figure out how to find what is populating the Access tables from the SQL tables.


Access will either be looking at
"local" tables...these are tables that have been populated with data from SQL or
"linked tables"....the tables are linked thro an ODBC DSN connection.

where does the data in SQL come from?...is this a production (eg OLTP) SQL db ..or just a "reporting" db, in which case...how does the data get into SQL?

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

WendellB
WendellB
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 1634
I would expect they used the linked approach to the SQL Server tables using ODBC. If you look at the tables in the Navigation Pane, you should see a globe symbol for each SQL Server table. That assumes of course that the Access database(s) are in the .mdb or .accdb format. If they are in the .adp format then you have a direct connection to the SQL Server tables. Hope that helps get you stated. We do that sort of thing all the time.

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

Group: General Forum Members
Points: 154 Visits: 264
The SQL DB is populated through the Main Application-front end in Access but connected to the SQL DB.

The Access Reporting/Applications use:
--SQL Tables
--Access tables that are from other Access DB's (used within other access reporting application)
--Local access tables initially populated from the SQL DB (this is where I am stuck and trying to figure out how these are populated.)

I need to find the connection mapping SQL table/column to Access table/column or find the Queries used to populate the data from SQL to Access.

------------------------------------------------------------------------------
I can see which tables are directly connect to the SQL DB but most of the "Reports" use the Access tables that I am not sure how are initially populated.
I can see which tables are from other Access DBs
I can see which are local Access tables

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
kschwid (2/13/2012)

--Local access tables initially populated from the SQL DB (this is where I am stuck and trying to figure out how these are populated.)

I need to find the connection mapping SQL table/column to Access table/column or find the Queries used to populate the data from SQL to Access.

------------------------------------------------------------------------------
I can see which tables are directly connect to the SQL DB but most of the "Reports" use the Access tables that I am not sure how are initially populated.
I can see which tables are from other Access DBs
I can see which are local Access tables


somewhere, some code is being run to update your "local " Access tables.....we cant see what you can see...but are there any "update" buttons , modules, on start up procs that are being run?

________________________________________________________________
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 (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 264
One of the Report Applications (in Access) that I am looking to recreate some of the reports from, does have a refresh button. I look at the buttons properties and on the event tab it says "on Click" - calls Refresh_Main I click the ... button so it takes me there and I get Figure 1 in the attachment.

I then go to look at that query and its in relation to the user/time, date and database logging. It doesn't call a query to populate tables.

---------------
I am unsure how to find out if it has a module or procedures at start-up.
That's almost what I have been trying to figure out how to see. Because that is my assumption as well is its something at start up.

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
kschwid (2/13/2012)
One of the Report Applications (in Access) that I am looking to recreate some of the reports from, does have a refresh button. I look at the buttons properties and on the event tab it says "on Click" - calls Refresh_Main I click the ... button so it takes me there and I get Figure 1 in the attachment.


no attachment ??

________________________________________________________________
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 (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 264
So sorry...

Respectfully,


Kate Schwid
Software Developer/Data Analyst
Ozaukee, WI
Attachments
Figure 1.docx (37 views, 49.00 KB)
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33041
ok...what does query "z_log_refresh" do? the "open query" at the bottom of attachment.

am getting a bit OOMD on this...my Access "skills" were based on VBA not the wonderful world of embedded macros et al :-)

________________________________________________________________
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 (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 264
Here is the code in z_log_refresh query

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;

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