Excel VBA - Help with Macro

S

SKS

Hi

I wrote a vba function that should return data to the excel spreadshee
after querying the Oracle database. I have installed and created
Microsoft ODBC connection for Oracle on my workstation.

If I manually open excel workbook and navigate to Data, Get Externa
Data, New Database Query, connect to the database, click on the SQ
pushbutton and paste the query, click OK and hit the Return Dat
pushbutton, the query return data to the spreadsheet without an
issues.

I recorded all the above steps in a Macro and wrote a similar vb
function for extracting data for another table. I then reopened anothe
workbook, navigated to Tools, Macro, Visual Basic Editor and pasted th
vba function that I wrote over there and executed it.

It then gives me this error "Run Time Error '1004' General ODBC Error"
When I click on the Debug, it highlights the following line of code i
yellow:
.Refresh BackgroundQuery:=False

Here is the query I am using. This query reads data from all the Ke
fields of the same table from 2 database instances (source and target
and retrieves all the rows that are missing in either of the database
[(Source - Target) + (Target - Source)]:
---------------------------------------------------------------------------
Select
'In PSP2 and not in PSE2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL

MINUS

Select
'In PSP2 and not in PSE2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL@COMPARE_PSE2

UNION ALL

Select
'In PSE2 and not in PSP2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL@COMPARE_PSE2

MINUS

Select
'In PSE2 and not in PSP2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL



Below is the Macro I wrote to execute this query. I masked the value
of UID and PWD here purposefully. Here PSP2 and PSE2 are 2 seperat
database instances. :
------------------------------------------------------------------------
Sub CompareMissingMacro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC fo
Oracle};UID=<userid>;PWD=<password>;SERVER=PSP2;", _
Destination:=Range("A1"))
.CommandText = Array( _
"Select 'In PSP2 and not in PSE1' INSTANCES, ACTION, ACTION_REASON
EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10) _
, _
"MINUS" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSP2 and not in PSE1' INSTANCES, ACTION, ACTION_REASON
EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13) & "" & Chr(10) _
, _
"UNION ALL" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSE1 and not in PSP2' INSTANCES, ACTION, ACTION_REASON
EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13) & "" & Chr(10) _
, _
"MINUS" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSE1 and not in PSP2' INSTANCES, ACTION, ACTION_REASON
EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10) _
)
.Name = "Query from ora_psp2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "ACTN_REASON_TBL"
End Sub

----------------------------------------------------------------------------

I would appreciate if anyone can look into this and shred some light a
to what is causing this error to occur. Pls let me know if you need mor
info.

Thank you
Siv
 
J

Jamie Collins

I searched the google groups Usenet archive for past posts on this
error (have you?) and it seems the line where your code errors is when
the SQL is actually executed against the connection. If either the SQL
text or connection string is invalid, this error is how you'll first
know about it.

Presumably your connection string hasn't changed, so the prime suspect
is your SQL. I cannot test it because you are using Oracle proprietary
syntax, I assume, because the MINUS keyword does not exist in the ANSI
SQL-92 standard. This is the reason for having a standard: so your
code is portable and can be understood by programmers of other SQL
compliant products (I wonder if you'd got a better response if you had
posted standard SQL?)

So, try running the SQL text on your server (or ask your DBA to do so)
and see if it works there or re-write using standard SQL (the latter
being a worthwhile exercise anyhow).

Jamie.

--
 
C

CLR

Hi Siva...........

I can't speak directly to your problem, however I do similar things between
Excel and FourthShift. At times I have also tried to record a working query
and then "re-write" it over to work in a different situation and it
fails........two reasons I have come up with........1 type0's in my
modifications, 2 access issues with the new areas in FourthShift I was
trying to get to........the latter being solvable by getting someone with
full authority to try to run the query for you.........

hth
Vaya con Dios,
Chuck, CABGx3


SKS > said:
Hi

I wrote a vba function that should return data to the excel spreadsheet
after querying the Oracle database. I have installed and created a
Microsoft ODBC connection for Oracle on my workstation.

If I manually open excel workbook and navigate to Data, Get External
Data, New Database Query, connect to the database, click on the SQL
pushbutton and paste the query, click OK and hit the Return Data
pushbutton, the query return data to the spreadsheet without any
issues.

I recorded all the above steps in a Macro and wrote a similar vba
function for extracting data for another table. I then reopened another
workbook, navigated to Tools, Macro, Visual Basic Editor and pasted the
vba function that I wrote over there and executed it.

It then gives me this error "Run Time Error '1004' General ODBC Error".
When I click on the Debug, it highlights the following line of code in
yellow:
Refresh BackgroundQuery:=False

Here is the query I am using. This query reads data from all the Key
fields of the same table from 2 database instances (source and target)
and retrieves all the rows that are missing in either of the databases
[(Source - Target) + (Target - Source)]:
-------------------------------------------------------------------------- -
Select
'In PSP2 and not in PSE2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL

MINUS

Select
'In PSP2 and not in PSE2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL@COMPARE_PSE2

UNION ALL

Select
'In PSE2 and not in PSP2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL@COMPARE_PSE2

MINUS

Select
'In PSE2 and not in PSP2' INSTANCES,
ACTION,
ACTION_REASON,
EFFDT
from PS_ACTN_REASON_TBL



Below is the Macro I wrote to execute this query. I masked the values
of UID and PWD here purposefully. Here PSP2 and PSE2 are 2 seperate
database instances. :
------------------------------------------------------------------------
Sub CompareMissingMacro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=<userid>;PWD=<password>;SERVER=PSP2;", _
Destination:=Range("A1"))
CommandText = Array( _
"Select 'In PSP2 and not in PSE1' INSTANCES, ACTION, ACTION_REASON,
EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10) _
, _
"MINUS" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSP2 and not in PSE1' INSTANCES, ACTION, ACTION_REASON,
EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13) & "" & Chr(10) _
, _
"UNION ALL" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSE1 and not in PSP2' INSTANCES, ACTION, ACTION_REASON,
EFFDT from PS_ACTN_REASON_TBL@COMPARE_PSE1" & Chr(13) & "" & Chr(10) _
, _
"MINUS" & Chr(13) & "" & Chr(10) _
, _
"Select 'In PSE1 and not in PSP2' INSTANCES, ACTION, ACTION_REASON,
EFFDT from PS_ACTN_REASON_TBL" & Chr(13) & "" & Chr(10) _
)
Name = "Query from ora_psp2"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
PreserveColumnInfo = True
Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "ACTN_REASON_TBL"
End Sub

-------------------------------------------------------------------------- --

I would appreciate if anyone can look into this and shred some light as
to what is causing this error to occur. Pls let me know if you need more
info.

Thank you
Siva
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA - Runtime Error "1004" General ODBC Error 2
a better way to query? 4
Retrieving Data from SQL Server to Excel 1
Database query. 4
ODBC Connection Not Connecting 13
Refernce to cell in SQL statemen 1
Excel VBA SQL Query Filename 5
Excel getting info from Access 1

Top