Moving data from Access to SQL server

Question

Post reply

Moving data from Access to SQL server

Krasavita

SSC-Insane

Points: 23138
More actions
April 7, 2010 at 9:35 am

#143915

I have a Access database, that I would like to write a store procedure to insert records from Acees tables to sql tables. I usually do it this process with wizard,but I wanted to know how I can create a store procedure to automate this process and schedule job. Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

You want this procedure created in SQL or Access?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

April 7, 2010 at 9:40 am

#1147472

Preferably in sql

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 3

A linked server could work.

http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/

or you could use SSIS. Run the import wizard from management studio, configure and save your package, schedule your package.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

Not that I am prompting Access...:-D

I would be prone to actually do this from within Access, unless you are wanting to automate it. If you want it automated then follow Emily's advice.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Tim Weintz SSCommitted Points: 1622 More actions · Answer 5

Several options here:

1. SSIS

2. Linked Server (Personally, I would shy away from this.)

3. OpenRowset

Note: Last I checked there was no 64 bit Jet driver available, which can add a bit of complexity if you are running a 64 bit SQL instance. You might want to consider this before making a decision.