INSERT INTO tablename using sql [dynamic table?]

Hello all!

Im trying to insert my record set (using SQL 2005 server [server 2008r2 upgrade coming soon] and office 2013) into a named table "test". (Activeworksheet - for testing purposes.)

The purpose is so I can manipulate the data (add columns- remove columns -write formulas in between the data - then hide columns) and allow the data to remain inside the same named table and upon a refresh have the data enter the same named columns even if the columns are moved from their original position.Basically a table that is dynamic and data always returns to its appropriatenamed column.

This code is cobbled together from the internet ( I new to VBA) and I currently use the connection manager and query designer to manage my workbook. However because my server names and database names change per user group I need a more "mobile" environment. [Tables do remain the same per user group]

My work book has 8 connections total and are all manged via excel's connection manager to SQL server. [workbook is about 10 tabs large]
Just trying to get this to work so I can have all connections managed via VBA.

Sorry for the rambling! Just trying to get this to work.

Please Login or Register  to view this content.

anyone? Ideas? I have tried the Insert Into "mytablename" however it always states, invalid object "tablename". I have been searching the internet for what I need to no avail. Any help much thanks.

Here is where I have tried to use the insert statement as follows:

However it gives me an error invalid object (I do have a table named "test" in the active worksheet), works without the string and placed data into my active worksheet. Im sure im missing something simple! But its kicking my butt!

I will read up on the update query and try that as well.

Thanks for the response.

Please Login or Register  to view this content.

Alan,

Ok, I almost have it. Thanks for pointing me to the update query!

This will allow me to pass sheet refrences to my sql query and allow me to place tables into my workbook, I can insert columns and place formulas into the table and upon refresh formulas will remain. However I need one more thing: I need to be able to move my table anywhere in my worksheet and update the table by table name. Not just a static cell refrence, but by table name refrence. I know it has to deal with the target line in my vba but I can not figure out how to set the correct property, or if it is even possible. Any guidance will be much help!

Please Login or Register  to view this content.

Thanks