|
MS Query "Connections"
Once you build the query, then yes, you just use the refresh property after
you change the string. The query is automatically dropped.. What you are talking in the names collection are not connections. -- Regards, Tom Ogilvy "Johnslg" wrote in message ... hmmm....I'm calling QueryTable inside a For loop in a single VB macro. The loop will run for as many cells of data the user adds to a column in the spread sheet. I'm doing a Activesheet.QueryTable.Add..... I'm not sure how to "refresh". Can I do ...QueryTable.Refresh.... instead of Add? Is there no "drop connection" command or method? THANKS! "Tom Ogilvy" wrote: http://nickhodge.co.uk/gui/datamenu/...taexamples.htm http://www.dicks-clicks.com/excel/Ex...htm#Parameters once the query is built, change the parameter and refresh the query (don't rebuild it). Make the the Backgroundquery property is set to false. -- Regards, Tom Ogilvy "Johnslg" wrote: I have a sheet that lets the user enter a chart of accounts in a column & then a macro runs against each account (using QueryTable), hits an SQL database, and returns data. My problem is that it adds a new connection each time the mcro runs. I just deleted about 400 connections. Is there a way to reuse the same connection (it's all going against the same server/database) or is there a way to automatically delete connetions? Thanks. |
MS Query "Connections"
Yesterday after debugging the macro, I opend up my "connections" button and
there were like a hundred and eighty connections, connection1 - connection189. Those are the one's I'm concerned about. I can delete them but I can't always be certain the user will remember to delete them. I don't know how many connections Excel will create before it crashes. All I did was record a macro of connecting to SQL Server and downloading some data. Excel built the macro actually using QueryList....I changed it to QueryTable....now it seems I should be using ADO. Sigh..... Thanks! "Tom Ogilvy" wrote: Once you build the query, then yes, you just use the refresh property after you change the string. The query is automatically dropped.. What you are talking in the names collection are not connections. -- Regards, Tom Ogilvy "Johnslg" wrote in message ... hmmm....I'm calling QueryTable inside a For loop in a single VB macro. The loop will run for as many cells of data the user adds to a column in the spread sheet. I'm doing a Activesheet.QueryTable.Add..... I'm not sure how to "refresh". Can I do ...QueryTable.Refresh.... instead of Add? Is there no "drop connection" command or method? THANKS! "Tom Ogilvy" wrote: http://nickhodge.co.uk/gui/datamenu/...taexamples.htm http://www.dicks-clicks.com/excel/Ex...htm#Parameters once the query is built, change the parameter and refresh the query (don't rebuild it). Make the the Backgroundquery property is set to false. -- Regards, Tom Ogilvy "Johnslg" wrote: I have a sheet that lets the user enter a chart of accounts in a column & then a macro runs against each account (using QueryTable), hits an SQL database, and returns data. My problem is that it adds a new connection each time the mcro runs. I just deleted about 400 connections. Is there a way to reuse the same connection (it's all going against the same server/database) or is there a way to automatically delete connetions? Thanks. |
MS Query "Connections"
Ok, I'll bite. Where is this "connections" button.
-- Regards, Tom Ogilvy "Johnslg" wrote: Yesterday after debugging the macro, I opend up my "connections" button and there were like a hundred and eighty connections, connection1 - connection189. Those are the one's I'm concerned about. I can delete them but I can't always be certain the user will remember to delete them. I don't know how many connections Excel will create before it crashes. All I did was record a macro of connecting to SQL Server and downloading some data. Excel built the macro actually using QueryList....I changed it to QueryTable....now it seems I should be using ADO. Sigh..... Thanks! "Tom Ogilvy" wrote: Once you build the query, then yes, you just use the refresh property after you change the string. The query is automatically dropped.. What you are talking in the names collection are not connections. -- Regards, Tom Ogilvy "Johnslg" wrote in message ... hmmm....I'm calling QueryTable inside a For loop in a single VB macro. The loop will run for as many cells of data the user adds to a column in the spread sheet. I'm doing a Activesheet.QueryTable.Add..... I'm not sure how to "refresh". Can I do ...QueryTable.Refresh.... instead of Add? Is there no "drop connection" command or method? THANKS! "Tom Ogilvy" wrote: http://nickhodge.co.uk/gui/datamenu/...taexamples.htm http://www.dicks-clicks.com/excel/Ex...htm#Parameters once the query is built, change the parameter and refresh the query (don't rebuild it). Make the the Backgroundquery property is set to false. -- Regards, Tom Ogilvy "Johnslg" wrote: I have a sheet that lets the user enter a chart of accounts in a column & then a macro runs against each account (using QueryTable), hits an SQL database, and returns data. My problem is that it adds a new connection each time the mcro runs. I just deleted about 400 connections. Is there a way to reuse the same connection (it's all going against the same server/database) or is there a way to automatically delete connetions? Thanks. |
MS Query "Connections"
In the Data ribbon (xl2007 - query table is kind of a POA...it has to be
refreshed and every time it refreshes it creates a new connection) .....BUT....I switched my code to ADO and (almost) everything is going fine....thanks to you and Mr. Carey. I just have one last problem. I want to pop a window and ask the user to input UID/PWD to connect : cnnConnect.Open "Provider=SQLOLEDB;SERVER=" & SvrName & ";DATABASE=" & dbName & ";UID=" & user & ";PWD=" & pass & " " This fails. If I replace UID/PWD with "trusted_Connection=yes" it logs in. We use windows authentication, no user accounts in SQL server. Is this connection string trying to find a SQL Server account???? "Tom Ogilvy" wrote: Ok, I'll bite. Where is this "connections" button. -- Regards, Tom Ogilvy "Johnslg" wrote: Yesterday after debugging the macro, I opend up my "connections" button and there were like a hundred and eighty connections, connection1 - connection189. Those are the one's I'm concerned about. I can delete them but I can't always be certain the user will remember to delete them. I don't know how many connections Excel will create before it crashes. All I did was record a macro of connecting to SQL Server and downloading some data. Excel built the macro actually using QueryList....I changed it to QueryTable....now it seems I should be using ADO. Sigh..... Thanks! "Tom Ogilvy" wrote: Once you build the query, then yes, you just use the refresh property after you change the string. The query is automatically dropped.. What you are talking in the names collection are not connections. -- Regards, Tom Ogilvy "Johnslg" wrote in message ... hmmm....I'm calling QueryTable inside a For loop in a single VB macro. The loop will run for as many cells of data the user adds to a column in the spread sheet. I'm doing a Activesheet.QueryTable.Add..... I'm not sure how to "refresh". Can I do ...QueryTable.Refresh.... instead of Add? Is there no "drop connection" command or method? THANKS! "Tom Ogilvy" wrote: http://nickhodge.co.uk/gui/datamenu/...taexamples.htm http://www.dicks-clicks.com/excel/Ex...htm#Parameters once the query is built, change the parameter and refresh the query (don't rebuild it). Make the the Backgroundquery property is set to false. -- Regards, Tom Ogilvy "Johnslg" wrote: I have a sheet that lets the user enter a chart of accounts in a column & then a macro runs against each account (using QueryTable), hits an SQL database, and returns data. My problem is that it adds a new connection each time the mcro runs. I just deleted about 400 connections. Is there a way to reuse the same connection (it's all going against the same server/database) or is there a way to automatically delete connetions? Thanks. |
| All times are GMT +1. The time now is 04:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com