Solved

MS SQL Server Management Studio 2005 and MS Access 2003

Posted on 2011-09-13
14
217 Views
Last Modified: 2013-11-27
I am fairly new to using Servers --my problem is that I have 3 linked tables in an MS Access 2003 dbase.  I need to create 1 table in SQL Server dbase from these 3 linked tables.  The 3 linked tables are from an external Server database in which I have read only access. This 1 combined table must be updated from the 3 linked tables to my group's Server dbase. How do I go about doing this?
0
Comment
Question by:rfitzh
  • 8
  • 5
14 Comments
 
LVL 4

Expert Comment

by:grogman
ID: 36530861
I have done this before by creating a job in SQL Server Agent that queries the tables in the MS Access DB, and imports the results into a table in SQL. Depending on how the data changes (for example, if existing records are often updated/modified), you might even consider having the job drop and then recreate the SQL table before importing the query data. If the changes are only additional records, you could bypass the drop/create steps, and just import the new records through the query and add the new data to the existing table. Set the job to run on a schedule that works for you.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 36530866
You can do it from Access, by linking to the combined table and then running the appropriate append queries.

You can also do it from Management Studio with TSQL statements.  You just need to link to the read-only servers (in Management Studio these are call linked servers)  or use the OpenRowSet statement to access the source tables.
0
 

Author Comment

by:rfitzh
ID: 36531337
Thanks for your responses.  

The tables are constantly being updated and I need to capture the updates. These tables are huge. I am using Management Studio 2005.

I understand that stored procedure is the way to go. How do I go about doing this?
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36531854
There are many ways to approach the job, depending on your requirements.  One of the most critical is how current your combined table needs to be and another is how much activity you have.  

On one end of the spectrum, you could create a view that logically combines the tables (or relevant parts of the tables) on the fly.  On the other end, technologies like replication are popular because they automatically take care of some of the insideous details that may not be obvious when you first try to roll-your-own.

Another important dynamic is whether you want to pull the data from your server or push the data from the others.  Each approach has it's pros/cons.  For example, if you want to pull the data, then it's no trivial matter to recognize a new record, much less an update or delete.

If you want to push the data, then the triggers are convenient for recognizing the changes, but those must be deployed on the other servers, which may/may not be possible.

You also have to factor in the implications of primary/unique key collisions.
oversimplified illustration of a stored proc.  But, I caution you the task is likely a couple orders of magintude more complicated than this suggests:

1. Define your linked servers to the 3 data sources
2. Write a proc that runs on a regular schedule to "sync-up" with the combined table.

Create Proc Sync-up
as
Insert into TableC
   Select * from Table1 where pk not in (select pk from TableC)
   Union all select * from table2 where pk not in (select pk from TableC)
   Union all select * from table3 where pk not in (select pk from TableC)

Delete from TableC
   where PK not in
         (Select PK from Table1
   Union all select PK from table2
   Union all select PK from table3
         )

Update TableC
    from TableC inner join Table1 on TableC.PK = Table1.PK
    set TableC.fld1 = Table1.fld1
        ,TableC.fld2 = Table1.fld2
 where TableC.fld1 <> Table1.fld1
      or  TableC.fld2 <> Table1.fld2


Update TableC
    from TableC inner join Table2 on TableC.PK = Table2.PK
    set TableC.fld1 = Table2.fld1
        ,TableC.fld2 = Table2.fld2
 where TableC.fld1 <> Table2.fld1
      or  TableC.fld2 <> Table2.fld2


Update TableC
    from TableC inner join Table3  on TableC.PK = Table3.PK
    set TableC.fld1 = Table3.fld1
        ,TableC.fld2 = Table3.fld2
 where TableC.fld1 <> Table3.fld1
      or  TableC.fld2 <> Table1.fld2







 
       






 

   


0
 

Author Comment

by:rfitzh
ID: 36538308
OK, on item 2 as you stated: Write a proc that runs on a regular schedule to "sync-up" with the combined table. --That should be doable.

I have decided to import the tables from the external Server into the Server database that I have write permissions.  Below is my query used in the SQL Server Import Export Wizard which I am saving as an SSIS package:
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null

UNION ALL
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null
UNION ALL
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null;  

Once the package is saved I will try and see how to get to item 2 as you indicated.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36539879
Unioning those 3 tables is a good idea. However, I notice that you union the same table 3 times, which I do not think is right.

Also, it is not really necessary to import the three tables to union them. You can union them during the import to create a single table. I propose putting the Union statement in a view, which you can subsequently reference in your sync procedure. Something like this:

Create view CombinedDescriptionLog
as
Select ...  FROM [Server1][dbo].[IM_DescriptionLog]
union all
Select ...  FROM [Server2][dbo].[IM_DescriptionLog]
union all
Select ...  FROM [Server2][dbo].[IM_DescriptionLog]
go


Then, you can reference the view in your SSIS import or even skip the imort and reference it in the syncronization proc:

Insert into TableC
   Select * from CombinedDescriptionLog where pk not in (select pk from TableC)

Delete from TableC
   where PK not in
         (Select PK from CombinedDescriptionLog         )

Update TableC
    from TableC inner join CombinedDescriptionLog CL on TableC.PK = CL.PK
       

    set TableC.fld1 = CL.fld1
        ,TableC.fld2 = CL.fld2
 where TableC.fld1 <> CL.fld1
      or  TableC.fld2 <> CL.fld2










0
 
LVL 42

Expert Comment

by:dqmq
ID: 36539917
Also, it occurs to me that if you are not changing the data locally, then really you may not need to import or sync anything at all.  Just reference the view as if it were a local table.  It will draw data from the remote tables and merge the results into a local view.  Up-to-date at all times with no synchronization effort on your part.

The drawbacks:
  You do not really have a local copy, so if one of the remote db's is down, your application won't work.
  Access to the data will be slower because it is over the network

The advantages:
   As simple as it gets
   No sync required
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rfitzh
ID: 36542358
I like the view idea because it is simple.  I will try that first.  --Not able to work on it today, but all options you presented seems viable.  --Will work on and once complete will award the points.

Thanks dgmg
0
 

Author Comment

by:rfitzh
ID: 36542517
One further note. --the 3 tables are from different regions and they all have the same name and fields.  The databases all have the same name except the initials of the regions at the end of it.  So it is correct as stated in the Import/Export query option.

Thanks,
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36544396
My concern was about the SQL you provided for the SSIS package. Unless I'm missing something, it doesn't identify the region!  So, whatever region it defaults to, you will pull that table 3 times.  

Your FROM clause needs to look something like:

 FROM [servername].[dbo].[IM_DescriptionLog]


[servername] is what distinquishes the region.
0
 

Author Comment

by:rfitzh
ID: 36557592
Yes, you are correct.  The 3 tables are from 3 different databases.  The only difference is the Region code initials attached to the name of each dbase. The Import/Export Wizard does not allow for the name of the dbases in the query (the dbase selection is already made on the previous menu).  Again you are correct I need to include the name of the dbase in my query if I am including all 3 tables.

It seems that I will need 3 SSIS packages and then combine into 1 table after that point. I am not sure how that is done but I will be working on it first thing tomorrow a.m (Eastern Standard Time) --Will update on status.
0
 

Author Comment

by:rfitzh
ID: 36709313
Sorry, for late response working on a number of projects.

Ok, I have now created the SSIS package via SQL Server Business Intelligence Developement Studio. I used one query to pull the data from each region's database and saved as 1 SSIS package. Now, I need to complete by setting up the job to run this package daily.  This is where I need some help.  Do I need to use the SQL Server Agent to do this or can this be done without the Server Agent?  How do I go about doing this?

Thanks
0
 

Assisted Solution

by:rfitzh
rfitzh earned 0 total points
ID: 36967731
Resolved as follows:
1)  Created SSIS package via SQL Server Business Intelligence Developement Studio. I used same query (3 times - 1 time each region) to pull the data from each region's database and saved as 1 SSIS package;

2)  Set up job via 'Scheduled Task'  to run overnight from my machine

Will award points now - the above solution from dgmg is acceptable if using SQL BI Dev Studio and the SQL Server Agent option.

Thanks,
0
 

Author Closing Comment

by:rfitzh
ID: 36991214
Solution was partially correct as the SQL Server Agent option is not available on my department's Server; also the problem involved 3 different databases for retrieving the data.  This necessitated using SQL Server Business Intelligence Development to set up the SSIS Package and Scheduled Tasks to set up the job to run overnight.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now