Unexpected Parameter Request

Question

To allow users with different Windows versions to run a query, I implemented a prompt that asks users if they are on Windows 10. If the user selects no, a message box pops up requesting a generic parameter value. Not sure why this is happening. Any ideas?

 'Prompt user to choose which version of Windows they have
  constringopt = MsgBox("Do you have Windows 10?", vbYesNoCancel, "IMAG Data")

  If constringopt = vbYes Then

'Import data: Windows 10 version
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DRIVER={Oracle in OraClient11g_home1};UID=XXX;PWD=XXX;SERVER=XXX;DBQ=XXX", _
  Destination:=Range("A2"), Sql:=QryStr)
    .MaintainConnection = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .Refresh
    .Delete
End With
    ElseIf constringopt = vbNo Then

'Import data: != Windows 10 version
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DRIVER={Microsoft ODBC for Oracle};UID=XXX;PWD=XXX;SERVER=XXX;", _
Destination:=Range("A2"), Sql:=QryStr)
    .MaintainConnection = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .Refresh
    .Delete
End With

Else
    GoTo Finish_Sub
End If

What parameter is it asking for? (I assume it will be something to do with the ODBC connection, but it would be nice to know what.) — YowE3K, Apr 14 '17 at 23:08
It's just a generic message box that says "Enter parameter: " Could it possibly be because the second connection string doesn't contain the DBQ: XXX reference? — ERKSMTY, Apr 14 '17 at 23:10
Your SQL (QryStr) is likely supplying the table but not the database. Are you expecting the database to default to something for non-Win10 users? — , Apr 14 '17 at 23:18
Why ask the user? Why not just use a routine that detects it? — Ron Rosenfeld, Apr 15 '17 at 00:46
What it looks like depends on how you write it. How to write it depends on exactly what you want. There are a lot of examples posted. You can access something usable with `Application.OperatingSystem`; You can get more information by accessing the `WMI`. Some information is available using the `Environ` function. There are probably Windows API calls that will give you what you need. An internet search will turn up many examples. — Ron Rosenfeld, Apr 16 '17 at 01:34

score 0 · Answer 1 · edited May 23 '17 at 12:09

0

Simplify your query with no parameters and try the connection that way. If it works, you can find your answer here: Using Parameters with an Oracle ODBC Connection

If you don't want a pop up window, you may want to use cells to pass parameters: Take a look at here: Excel: Use a cell value as a parameter for a SQL query

edited May 23 '17 at 12:09

Community

1
1

answered Apr 14 '17 at 23:48

Tehscript

2,556
2
13
23

Unexpected Parameter Request

1 Answers1