I wish to import a Oracle table via a macro and have done it via the wizzard and recorded that. when i run it i get a 1004 error at the refresh. I then scripted it myself and it fails at the connect line. Can you help me please. I got the driver from a record macro script...
My script:
Dim oConn As Object, oRS As Object
Password = InputBox("input password", "enter password")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={Oracle ODBC Driver};" & _
"Dbq=RPTCCB;" & _
"Uid=JSHAPER;" & _
"Pwd=" & Password
Set oRS = CreateObject("ADODB.RecordSet")
oRS.Open "SELECT SC_ACCESS_CNTL.USR_GRP_ID, SC_ACCESS_CNTL.APP_SVC_ID, SC_ACCESS_CNTL.ACCESS_MODE, SC_USR_GRP_PROF.EXPIRATION_DT, SC_ACCESS_CNTL.VERSION, SC_ACCESS_CNTL.OWNER_FLG FROM CRYSTAL.SC_ACCESS_CNTL SC_ACCESS_CNTL, PRDCCB01.SC_USR_GRP_PROF SC_USR_GRP_PROF WHERE SC_ACCESS_CNTL.APP_SVC_ID = SC_USR_GRP_PROF.APP_SVC_ID AND SC_ACCESS_CNTL.USR_GRP_ID = SC_USR_GRP_PROF.USR_GRP_ID", oConn
Range("A1").CopyFromRecordset oRS
Thank you in advance to anyone that spends any time helping me figure this out...
My script:
Dim oConn As Object, oRS As Object
Password = InputBox("input password", "enter password")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={Oracle ODBC Driver};" & _
"Dbq=RPTCCB;" & _
"Uid=JSHAPER;" & _
"Pwd=" & Password
Set oRS = CreateObject("ADODB.RecordSet")
oRS.Open "SELECT SC_ACCESS_CNTL.USR_GRP_ID, SC_ACCESS_CNTL.APP_SVC_ID, SC_ACCESS_CNTL.ACCESS_MODE, SC_USR_GRP_PROF.EXPIRATION_DT, SC_ACCESS_CNTL.VERSION, SC_ACCESS_CNTL.OWNER_FLG FROM CRYSTAL.SC_ACCESS_CNTL SC_ACCESS_CNTL, PRDCCB01.SC_USR_GRP_PROF SC_USR_GRP_PROF WHERE SC_ACCESS_CNTL.APP_SVC_ID = SC_USR_GRP_PROF.APP_SVC_ID AND SC_ACCESS_CNTL.USR_GRP_ID = SC_USR_GRP_PROF.USR_GRP_ID", oConn
Range("A1").CopyFromRecordset oRS
Thank you in advance to anyone that spends any time helping me figure this out...
Last edited:
