Distribute data from monster SQL 2000 table into small, variable, and dynamic Access databases.

Question

Distribute data from monster SQL 2000 table into small, variable, and dynamic Access databases.

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

Lynn Pettis (1/26/2009)
Very carefully.
Using SSIS, the process would be encapsulated in a FOR EACH container (not exactly sure how i would configure) and within that I'd have the necessary "code" to connect to each Access database, truncate the existing data (if needed), select the correct data from the source table and push the data to the Access database table.
That is a 30,000 foot overview of what I'd do. the details, I have to leave that to you at the moment. You need to handle the various errors that can occur, such as the laptop isn't there, or your process doesn't have permissions to access the laptop, etc.

I will add just a few notes to this methodology. This is what we call a push methodology - which requires that the destination is accessible (connected to the network) and valid authentication is setup so the process can access each system.

You would be better off with a pull methodology - which is initiated from the users laptop. However, this would require installing the code on the users laptop, or providing code in the Access database that could perform the process. You could always set it up so that you are using a linked table in Access to pull the data into a local Access table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Henny-997017 Valued Member Points: 70 More actions · Answer 2

I can understand that...however, there is a push and pull involved.

The Access databases are pushed to a shared folder and then the user's pull them from that folder.

In all seriousness though, no more time should be spent discussing the merits of the method or whether or not there is a better method.

The original question still remains - break out a large SQL server table into smaller Access databases based off values in the SQL server table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Jeffrey Williams (1/26/2009)
Lynn Pettis (1/26/2009)
Very carefully.
Using SSIS, the process would be encapsulated in a FOR EACH container (not exactly sure how i would configure) and within that I'd have the necessary "code" to connect to each Access database, truncate the existing data (if needed), select the correct data from the source table and push the data to the Access database table.
That is a 30,000 foot overview of what I'd do. the details, I have to leave that to you at the moment. You need to handle the various errors that can occur, such as the laptop isn't there, or your process doesn't have permissions to access the laptop, etc.
I will add just a few notes to this methodology. This is what we call a push methodology - which requires that the destination is accessible (connected to the network) and valid authentication is setup so the process can access each system.
You would be better off with a pull methodology - which is initiated from the users laptop. However, this would require installing the code on the users laptop, or providing code in the Access database that could perform the process. You could always set it up so that you are using a linked table in Access to pull the data into a local Access table.

And to provide additional information, both methods have their Pros and Cons. If you are using a pull methodology remember that if there are any code changes, you need to "touch" each laptop with the necessary updates to the system.

Again, what all this comes down to is solid systems design. Identify the full requirements for what you are trying to accomplish, what the various methods of accomplishing the task are, Access using a pull methodology, Access using a push methodology, Replication, etc.

Design a high level view of each system, the pros and cons of each, (and I'm sure others will add to this list) and then make a decision as to which is the best way to go for this project.

Given more time to think about this, I'm sure I could also come up with more, but I think this is a good start.

Henny-997017 Valued Member Points: 70 More actions · Answer 4

The best way to go is to break out a large SQL server table into smaller Access databases based off values in the SQL server table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Henny (1/26/2009)
The best way to go is to break out a large SQL server table into smaller Access databases based off values in the SQL server table.

It depends. What is your definition of "best way"? What are your requirements for this project? How are you going about creating and populating the Access databases? Will these databases have additional functionality coded into them?

There are so many different ways of doing things there is "one best way". What may be best for you may not be best for someone else.

We have tried to give you some ideas on how, but you have to do the hard work and decide what needs to be done and how based on your requirements. That, it so happens, to be a big part of the picture that is currently missing from the picture you have painted.