Import SQL Data to MS Access 2007 Database using Access Forms

Question

I'm working on a mini-project at work:

I need to Import data from SQL server 2008 to Access 2007 so that an end user can access the data/run reports etc.

Things I am not allowed to do:

I am not allowed to link the data [don't want the end user running queries on the SQL server database which is live]

I am not allowed to create a SQL task/SQL job/SSIS package to do this nor am I allowed to write a little application in C#/VB.NET etc.

Things I can do:

I'm only allowed to create an Access form which copies data from the SQL server database to a local Access DB on a button click.

I must store the connection string in an inaccessible place so that the end user cannot see it/edit it

Things I've tried

I've tried to use the "External Data Import" manually and that works just fine but creates a new table every time I run it [Something like MyTable1, MyTable2].

I can't seem to find any sources that concisely describe how I could automate this [save for the button click].

I would like for someone to point me to resources or help me figure this out. I am not looking for someone to write code for me. I just don't know where to start.

When I said I can't link it, I meant I cannot use linked tables to the SQL server. I can only copy the data to local tables. — GVashist
– GVashist, Commented Apr 30, 2015 at 19:57
Sorry, I misunderstood about the form. Since import external data does what you want, you can accomplish the same thing with DoCmd.TransferDatabase. Delete the old copy of the table first in order to avoid that naming issue. — HansUp
– HansUp, Commented Apr 30, 2015 at 20:18
Thank you. Would DoCmd.TransferDatabase work if I need to copy only a subset of tables? Also, how is this different from DoCmd.TransferSQLDatabase — GVashist
– GVashist, Commented Apr 30, 2015 at 20:25
What you mean by copy only a subset of tables? Is that something you were able to accomplish with the import external data option in the Access UI? — HansUp
– HansUp, Commented Apr 30, 2015 at 20:28
Pick one table. Use DoCmd.TransferDatabase to import it into Access. Repeat that process for each of your other tables. — HansUp
– HansUp, Commented Apr 30, 2015 at 20:39

Brian Pressler · Accepted Answer · 2015-04-30 20:28:02Z

2

You might look into using the "TransferDatabase" method. It allows you to import and export data from outside data sources. You could get to a sql server using ODBC. See:

https://msdn.microsoft.com/en-us/library/office/ff196455.aspx

Also, if you don't want users to get easy access to your connection string, you can publish it as an MDE file so they can not see the code. But the connection string isn't encrypted... so it's not unhackable.

answered Apr 30, 2015 at 20:28

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import SQL Data to MS Access 2007 Database using Access Forms

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related