Solved

Importing Access MDB into SQL Server 2005 with Stored procedure

Posted on 2007-11-30
9
6,957 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
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

777 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