Creating a Script to schedule a data dump from SQL Server 2005 to Access

Posted on 2009-04-15
Last Modified: 2012-05-06
Hi there,
I have a db in SQL Server 2005 and would like to dump all the data from one table on to an Access db on the same computer. This job would be running twice, @ 8 AM/PM. What would be the best procedure to do this? Create a Job? Package in SSIS? I'm not that familiar with SSIS...
Also, the destination table has different field names than the source table, so mapping would probably come into play.
I'm a little comfortable with the insert statement, it's the scripting from SQL Server to Access is what I'm having trouble with...

Thanks for your input,
Question by:Classic1
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
  • 2
  • 2
LVL 31

Accepted Solution

RiteshShah earned 500 total points
ID: 24149486
you can create one linked server and create one job which will dump your data from SQL Server to linked access table. here is one example to create linked server with Access

Author Comment

ID: 24150557
Hi there,
Thanks for the link, the sp_addlinkedserver worked well! I was able to execute a select statement within SQL...

Now is there a chance I can do an insert statement to the linked Access db? I tried doing this, but I got this error: (This was executed in the actual db, not in the master environment.

Msg 215, Level 16, State 1, Line 1
Parameters supplied for object 'AutoSolLink...AuditTrail' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

I've attached the code as well..

Much appreciated,
Insert into [AutoSolLink]...AuditTrail ('Data1', 'Data2')
Select ADR.Reg_000, ADR.Reg_001
from MercuryAuditDRecords ADR

Open in new window

LVL 31

Expert Comment

ID: 24150652
run this

Insert into [AutoSolLink]...AuditTrail (Data1, Data2)
Select ADR.Reg_000, ADR.Reg_001
from MercuryAuditDRecords ADR

Open in new window


Author Closing Comment

ID: 31570480
Thank you very much! I was able to insert the data successfully...much appreciated,

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

623 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