How to use an sql variable in a connect string to ORACLE

[mikej]

Hi,

I automated a process (using a recordable MACRO) in excell to open msaccess query and retrieve data (from an Oracle database) - said data then to be compared via VLOOKUP with data in a second worksheet

I then hoped to tinker with the recordable MACRO, so that the second time the query is made to the ORACLE database, a prompt asks for the unique key of the record to be retrieved. Hopefully, I'd like to amend the code further so that various connects are made to Oracle, the user queried for a record ID, the record returned and the data copy/paste specialed into excell.

The problem is I'm having trouble passing the variable across into ORACLE.

Perhaps you can help me?



- this defines the variable

   Dim DSETID As String       

- further down the code THIS takes the recordset ID

    DSETID = InputBox("Please input Dataset ID", "DATASET POPULATOR") 

- Connection with ORACLE is made. Unfortunately the DSETID doesn't appear to get passed across (see several lines down)

   Sheets("Data Warehouse").Select
    Application.CutCopyMode = False
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DRIVER={Oracle in OraHome92};SERVER=INETDEV.AUDIT-COMMISSION.GOV.UK;UID=acquire;;DBQ=INETDEV.AUDIT-COMMISSION.GOV.UK;DBA=W;APA=" _
        ), Array( _
        "T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC" _
        ), Array("=F;FBS=60000;TLO=O;"))
        .CommandText = Array( _
        "SELECT AUTHORITY.AUTHORITY_ID, AUTHORITY.SHORTNAME, DATASET.DESCRIPTION1, DATAGROUP.PERIOD, DATAGROUP.DESCRIPTION1, DATAVALUE.DATAVALUE" & Chr(13) & "" & Chr(10) & "FROM PROTO.AUTHORITY AUTHORITY, PROTO.AUTHORITYGROUP AUTHORITYG" _
        , _
        "ROUP, PROTO.DATAGROUP DATAGROUP, PROTO.DATASET DATASET, PROTO.DATAVALUE DATAVALUE" & Chr(13) & "" & Chr(10) & "WHERE AUTHORITYGROUP.AUTHORITYGROUP_ID = AUTHORITY.AUTHORITYGROUP_ID AND AUTHORITY.AUTHORITY_ID = DATAVALUE.AUTHORITY" _
        , _
        "_ID AND DATAVALUE.DATASET_ID = DATASET.DATASET_ID AND DATASET.DATAGROUP_ID = DATAGROUP.DATAGROUP_ID AND ((DATASET.DATAGROUP_ID= & DSETID))" _
        )
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Data Template").Select
    Range("G6:G12").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
End 

- Help!!