Solved

Importing Access MDB into SQL Server 2005 with Stored procedure

Posted on 2007-11-30
9
6,982 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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