Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

table variables in a Microsoft Query (Excel) and parameters

Hello,

I have a MS SQL query like so

declare @qq table
( ... )

insert into @qq ( ... )
select ( ... )
from mytable
where myparameter1 = ?

I want to be able to execute this in MS Excel (2007 if ure wondering) as a Microsoft Query. Unfortunately, I cannot use any parameters in this query because it cannot be graphically represented. My actual query is way more complicated than that and has multiple table variables. Any suggestion or is this an impossibility?

Thanks in advance.
0
CodeWrangler
Asked:
CodeWrangler
  • 2
  • 2
2 Solutions
 
Mark WillsTopic AdvisorCommented:
Ummmm... interesting problem. The (memory) table will only exist for that session, so, kinda interesting what you are doing with it.

If there is something that needs to be updated in the Server, then normally link to the spreadsheet from the server and update via the linked server.
0
 
CodeWranglerAuthor Commented:
Well, I havent even got that far yet. Excel will execute the above code, so I know the tables are being written to and read from correctly, it's just that I cannot pass any parameters to it...
0
 
Mark WillsTopic AdvisorCommented:
Well, I have seen methods interactively when using DSN - but that is more for stored procedures...

What it looks like is a query being built more so than a parameter per se.

Normally resort to VBA code and create a little macro / routine. You can then pass your variable to the routine, or, have the routine read information from the excel page.

Have a look at : http://www.ozgrid.com/forum/showthread.php?t=41672&page=1 for a pretty good example with error checking et al... Also note it uses ADO...

0
 
MWGainesJRCommented:
Would this not work?


     

Sub updateSQLTable() 
Dim conn As ADODB.Connection
Dim constr As String
Dim cmd As ADODB.Command
Dim sql As String
Dim tbl As String 
Set conn = New ADODB.Connection
constr = "Your Connection String"
tbl = InputBox("Enter Table Name.")
sql = "insert into " & tbl & " select from mytable where myparameter1 = ?" 
With conn
   .Open constr
   .Execute (sql)
End With
End Sub

Open in new window

0
 
CodeWranglerAuthor Commented:
VBA was the way to do this. Used ADO for the connections. Thanks guys for the guidance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now