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.

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.

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.

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.