Importing Access MDB into SQL Server 2005 with Stored procedure

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)
EGormlyAsked:
Who is Participating?
 
gemostConnect With a Mentor Commented:
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
 
Shanmuga SundaramDirector of Software EngineeringCommented:
why looking for SP? Try using DTS in SQL Server to import and export. it is faster and easy.
0
 
gemostCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
EGormlyAuthor Commented:
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
 
EGormlyAuthor Commented:
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
 
EGormlyAuthor Commented:
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
 
PaultheBrokerConnect With a Mentor Commented:
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
 
gormlyCommented:
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
 
EGormlyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.