How to delete unused parameter?

[zovche]

I'm using external connections and SQL. At first I had four parameters (in SQL I have like table1.field1 = ?), then I needed only three parameters so I deleted one from SQL. However it still asks me to enter value or reference for that deleted Parameter. It won't recognize that there's only 3 parameters now. Does anyone know how to delete parameters completely?

 

[xenou]

That's the correct way. You must be referring to the parameter somewhere else as well.

 

[zovche]

Well only way I'm defining parameters are through SQL using sign ?. So the same way I delete them. But when I want to define all parameters it lists all four parameters and I can't leave blank for that one. Does it store somewhere else where I should go and remove it? In my SQL definition it's only three ? characters as parameters.

 

[xenou]

But when I want to define all parameters it lists all four parameters and I can't leave blank for that one.

What do you mean by define here? I thought you said that you only define them in the SQL query command text and you are only defining three.

In my SQL definition it's only three ? characters as parameters.

Where is the SQL defined? Locally in Access? In a pass through query? In code? In a stored procedure? Using ODBC?


As I said, if your query has only three parameters but somewhere else you see four, there is probably a something else that is involved.

 

[zovche]

What do you mean by define here? I thought you said that you only define them in the SQL query command text and you are only defining three.


Where is the SQL defined? Locally in Access? In a pass through query? In code? In a stored procedure? Using ODBC?


As I said, if your query has only three parameters but somewhere else you see four, there is probably a something else that is involved.

Ok, so I connect using ODBC and I get SQL line, so this is where I do it, under WHERE condition I set '?' and it automatically creates parameters. I had 4 of them, but now I need just 3, so I deleted one line I don't need anymore, but on the list of parameters it still stands 4, it didn't remove one the same way it creates, and asking me to define reference for it even it doesn't exist. And every time I refresh tables it says that this parameter has no valid input.

 

[xenou]

I'm not really sure what you mean when you say "on the list of parameters it still stands 4". What list are you looking at? Can you provide a screenshot? In general however, it sounds like something has been set by the original definition and you have to find that something and fix it. It might be simplest to just delete and recreate this table or query.

 

[zovche]

I'm not really sure what you mean when you say "on the list of parameters it still stands 4". What list are you looking at? Can you provide a screenshot? In general however, it sounds like something has been set by the original definition and you have to find that something and fix it. It might be simplest to just delete and recreate this table or query.

Yes sure, thanks. And let me know if links with print screens are working.
So this is the SQL line. I market important part where Parameters are expected. Just in between those 3 lines it was fourth as well, which I wanted to delete as found not necessary anymore. So I deleted by deleting that line with ‘?’, as that’s how Parameter is created at first place, but seems that’s not enough.

https://www.dropbox.com/s/i8qt83bga2h6rra/Untitled.jpg?dl=0

When I hit “Parameters…” it still shows list of 4 parameters and asking for reference. If I leave it blank it will return error, so I have to put something. But I’m wondering how to remove it completely, as not sure this way if it will at some point this cause issues.

https://www.dropbox.com/s/5vxpgmkzx5ke5s0/Untitled1.jpg?dl=0

I would like to avoid to recreate table from start as it has references to multiple other tables and it would take a lot of time, so rather try to investigate if this could be solved any easier.

Thank you.

 

[xenou]

I can't recreate the issue. It might be necessary for you to say what version of excel you are using, and what kind of data connection you are using (ODBC or OLE) (if you don't know then explain exactly how you created the connection originally, from which menu items).

It probably would have helped if you mentioned you were using excel in the first place This is an Access forum.

Some links that seem to be relevant:
how to pass parameters to query in SQL (Excel) - Stack Overflow
SQL Server Parameterized queries in Microsoft Excel | Programming Notes!

These represent my experience - i.e., that Excel OLE doesn't work well with parameters anymore! I can't do it with just putting question marks in the query text (i'd go through MSQuery instead, I think, if I were trying to do this, or as in the first post use some code to edit the query text before refreshing to imitate a parameter).

 

[dbig]

I have the same problem. Excel creates a parameter for each "?" if finds in an SQL extract query used to populate an Excel table from Oracle. My query's WHERE clause initially contained six different conditions (e.g. where ENAME = ? and EDEPT = ? ...). Now I want to streamline the query and reduce the number of parameter-driven conditions from six down to two, but the parameters interface (the Parameters button at the bottom of the table's External Data Properties, Connection Properties, Definition tab) still thinks I have six parameters and will not let me leave without pointing each one at a cell.

As best I can tell, parameters are assigned sequentially, top-to-bottom for each ? found in the query script, and any extras are just ignored, but I'm not sure and would love to remove them. How is that done or is do I have to build a new workbook to do it?

 

[dbig]

I have the same problem. Excel creates a parameter for each "?" if finds in an SQL extract query used to populate an Excel table from Oracle. My query's WHERE clause initially contained six different conditions (e.g. where ENAME = ? and EDEPT = ? ...). Now I want to streamline the query and reduce the number of parameter-driven conditions from six down to two, but the parameters interface (the Parameters button at the bottom of the table's External Data Properties, Connection Properties, Definition tab) still thinks I have six parameters and will not let me leave without pointing each one at a cell.

As best I can tell, parameters are assigned sequentially, top-to-bottom for each ? found in the query script, and any extras are just ignored, but I'm not sure and would love to remove them. How is that done or is do I have to build a new workbook to do it?