Run Access Macro then Import Acess table to SQL database

[smatthews]

Hello all

I am looking for a little guidance as to the best way to handle this job. I have an Access 2010 db. I run a macro that creates a table in Access. I need to import the table into a SQL 2005 db. So my steps are:

Run Access Macro
Import the table that is created into a SQL database
Automate this process so it runs every night

Thanks
Smatthews

 

[gmmastros]

I don't know a whole lot about Access, but I think you can create a linked table. With a linked table, the data would actually be stored in SQL Server, but would also appear as a regular table within the Access database. Therefore, your Access macro should still work, but you could bypass the the import process because the data would already be in SQL Server.

I don't know if this will work, but I encourage you to spend a couple minutes researching this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Thanks gmmastros!

I never thought of this approach... it seems like a good way to go.

Smatthews

 

[gk53]

Are you still using Access? If not may be it will be easy to convert your macro into stored procedure in SQL server and run that on schedule...

 

[smatthews]

Thanks gk53

That is a great suggestion. I am looking for a quick fix so linking the sql table in Access and then updating it through the Access macro seems to be the best answer for now. Eventually I want to move it over entirely to sql.

Is there some way to easily move a macro into a stored procedure or would I just be moving Access query by query into the sp?

Regards
smatthews

 

[gmmastros]

Depending on the complexity of the query... sometimes you don't need to change it at all, and sometimes you need to rewrite. And then there are some times when you don't need to rewrite it, but should rewrite it to make it perform better.

There are so many factors involved that this question does not have a straight forward answer.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smatthews]

Thanks again gmmastros.

You are so right. The macro runs 8 different queries, some make tables, some update tables. I think your third solution is probably appropriate for this instance; it doesn't need to be but should be re-written. However, your suggestion to link and then update the sql table is a great solution for now.

Regards,
smatthews