?
Solved

Importing Access MDB into SQL Server 2005 with Stored procedure

Posted on 2007-11-30
9
Medium Priority
?
7,016 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 3

Accepted Solution

by:
gemost earned 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

743 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