By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Expand|Select|Wrap|Line Numbers
  1.    Dim DSETID As String       

- further down the code THIS takes the recordset ID
Expand|Select|Wrap|Line Numbers
  1.     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)
Expand|Select|Wrap|Line Numbers
  1.    Sheets("Data Warehouse").Select
  2.     Application.CutCopyMode = False
  3.     With Selection.QueryTable
  4.         .Connection = Array(Array( _
  5.         "ODBC;DRIVER={Oracle in OraHome92};SERVER=INETDEV.AUDIT-COMMISSION.GOV.UK;UID=acquire;;DBQ=INETDEV.AUDIT-COMMISSION.GOV.UK;DBA=W;APA=" _
  6.         ), Array( _
  7.         "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" _
  8.         ), Array("=F;FBS=60000;TLO=O;"))
  9.         .CommandText = Array( _
  10.         "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" _
  11.         , _
  12.         "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" _
  13.         , _
  14.         "_ID AND DATAVALUE.DATASET_ID = DATASET.DATASET_ID AND DATASET.DATAGROUP_ID = DATAGROUP.DATAGROUP_ID AND ((DATASET.DATAGROUP_ID= & DSETID))" _
  15.         )
  16.         .Refresh BackgroundQuery:=False
  17.     End With
  18.     Sheets("Data Template").Select
  19.     Range("G6:G12").Select
  20.     Selection.Copy
  21.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  22.         :=False, Transpose:=False
  23.     ActiveWindow.ScrollColumn = 2
  24.     ActiveWindow.ScrollColumn = 1
  25. End 
- Help!!
Nov 29 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.