Automation of data migration from SQL Server 2012 to Access 2013 DB

Question

Automation of data migration from SQL Server 2012 to Access 2013 DB

Maverick_Dev

Valued Member

Points: 59
More actions
October 3, 2018 at 9:51 am

#363598

Hello,
I am currently copying data from few tables in SQL Server 2012 to an Access database Via ODBC Connection. This has to be done every week and I am currently doing it manually. I have found solution(s) from Excel to SQL server, Access to SQL Server, but NOT the other way around.
I am trying to find a solution to automate this process of copying data from SQL Server database to Access database which resides in a specific location on a shared drive.
Regards,
Dev

Viewing 8 posts - 1 through 7 (of 7 total)

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

mark.humphreys SSC Eights! Points: 867 More actions · Answer 1

Hi Dev,
Could you please give more details.

Is this a copy of the SQL data tables? Could you not just link to the SQL tables in your Access DB. Why does the data need to be in the Access DB?

Thanks
Mark

Maverick_Dev Valued Member Points: 59 More actions · Answer 2

Hi Mark,
Unfortunately, the end users prefer Access DB. I surely can link tables as well, but the data to be copied is not the exact data from each table. I am applying filters and joins over multiple tables and copying the result records set into access db.

Regards,
Dev

Joe Torre SSChampion Points: 10248 More actions · Answer 3

PowerShell or VB script and a Windows Task Scheduler job would be the easiest to automate.

Maverick_Dev Valued Member Points: 59 More actions · Answer 4

Hello Joe,

Thanks for the reply. I looked up for powershell scripts, but haven't really found an article that would suffice my need. Can you please point me to any online resource(s) available that you are aware of and that would help me learn this process of automating the task.

Regards,
Dev

Joe Torre SSChampion Points: 10248 More actions · Answer 5

https://blogs.technet.microsoft.com/heyscriptingguy/2009/08/13/hey-scripting-guy-can-i-query-a-microsoft-access-database-with-a-windows-powershell-script/
https://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell

Maverick_Dev Valued Member Points: 59 More actions · Answer 6

Maverick_Dev

Valued Member

Points: 59

October 3, 2018 at 12:36 pm

#2007928

Thank You, Joe.

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Maverick_Dev - Wednesday, October 3, 2018 10:34 AM
Hi Mark,
Unfortunately, the end users prefer Access DB. I surely can link tables as well, but the data to be copied is not the exact data from each table. I am applying filters and joins over multiple tables and copying the result records set into access db.
Regards,
Dev

Why not just create the necessary views and link the views?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)