Solved

Importing Access MDB into SQL Server 2005 with Stored procedure

Posted on 2007-11-30
9
6,996 Views
Last Modified: 2010-04-21
Pretty straightforward, I am looking for the code to import a MS Access MDB database into SQL Server 2005 with a stored procedure.

I have seen examples of this with excel or csv files, but I have not seen and cannot figure out how to do it with a mdb and in a stored procedure.  I need to do the import 'hands off', and the data will change completely three times a day, that is why it needs to be a stored procedure.

The SQL server already has a table with matching specifications so there is no need to create or drop tabels.  I just want to import the data into an empty but existing table.

(I'll be deleting the data in the SQL Table beforehand, I already figured that part out)
0
Comment
Question by:EGormly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20382878
why looking for SP? Try using DTS in SQL Server to import and export. it is faster and easy.
0
 
LVL 3

Expert Comment

by:gemost
ID: 20382901
Is the Access data from one table or are there many. If there are many how many columns are in each?
If there is just one table you can set up a DTS on the server to import the data from access. Once the DTS is created you can schedule it  to run at certain times of the day.

gemost
0
 

Author Comment

by:EGormly
ID: 20383173
shasunder:
why am I looking for SP? because I am a newb and I have no idea what to do to acmplish my goal, hence.. a question on EE.

I do not know what DTS is
Can it be scripted to do this? if so how?


gemost:
Yes, one single table. (There may be many at a later time)

I do not know what a DTS is or how to schedule one.
I have imported access data before via the Import data wizard

>> Right Click database in SQL Server management Stuido,  Click Tasks, Click Import Data
Thats as far as I got.

I am a SQL newbie, I need a bit of hand holding  :<
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 3

Accepted Solution

by:
gemost earned 250 total points
ID: 20384994
Ok,
1. On the Data Transport Services  Import/Export wizard click next
2. on the next page you need to select the database or other object you will be importing the data from.
     - First select the data source that you are getting the data from. There is a choice for Microsoft  
           Access
     - Next click on the browse button next to the right of  the File name text box and navigate to the database file you are migrating from. Once done the path will display in the file name text box.
     - Next if there are password requirements for the user name & password
     - Now click next
 3  On this next page select the server that your SQL database is on from the dropdown
     - Next select the  select the specific database from the database combobox
     - click next
4  Make sure the option titled  Copy tables or Views... is selected then click next
5  One this screen put a check in the box of the table you want to import. To the right of the table name make sure the destination table name is the same as the one in SQL then click next.
6 On the next page make sure  that run  immediately is checked. and then check Save DTS package to save the package on the server. Then hit next
7 Enter the package name and hit next. now the package should run.
  Once you have done this you will be to run the package from the server as long as you do not make any changes to  either of the tables.

HTH
gemost
0
 

Author Comment

by:EGormly
ID: 20385154
1. On the Data Transport Services  Import/Export wizard click next

where is that?

I do not see anything related to "Data Transport Services"  anywhere in the management Studio.
Am I looking in the wrong place?

0
 

Author Comment

by:EGormly
ID: 20385175
Do you mean the "Save SSIS package" option in the "SQL Server Import and Export Wizard"?

If so, I have done that.. how do I then "run" the saved packed on a scheduled basis?

0
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 250 total points
ID: 20388151
SSIS is the new name  (2005) for DTS (2000), so yes you are on the right lines.  Look at this:

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

about half way down is this: (followed by a step by step guide)
"Scheduling Package Execution
You can schedule the execution of SSIS packages by using the SQL Server Agent. The SQL Server Agent is the built-in job scheduling tool that is provided with SQL Server 2005. Like the SSIS Server, the SQL Server Agent is implemented as a Windows service and that service must be running in order to support job scheduling. Also, like the SSIS Server, the SQL Server Agent service is managed using the SQL Computer Manager thats a part of the Computer Management MMC console."
0
 
LVL 1

Expert Comment

by:gormly
ID: 20395237
PaultheBroker, Now that is a complete and absolute answer.
Wow, thanks. I could not find that info anywhere.
You get the points and if I could raise them..,. I most certainly would.

PaultheBroker, is there a way to fire that with an outside script or can I only use the Built in Scheduling?
(should I ask this a another question?)

0
 

Author Closing Comment

by:EGormly
ID: 31411929
Awesome guys, thanks.


gemost you got the main part, and Paul sealed the deal.
I am sorry I couldn't give you both the 500.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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