ODBCDirect..............RecordCount problem

[Ian Mac]

All,

I'm trying to return data using the ODBCDirect method from an Oracle Database,

So far I have:

Private Sub ODBCDirectTest()
Dim wrkEG As Workspace
Dim cnnEG As Connection
Dim qdfEG As QueryDef
Dim Results As Recordset
Dim Ret As Boolean
Dim Record
Dim iCols As Integer

Set wrkEG = CreateWorkspace("myEG", "admin", "", dbUseODBC)
Set cnnEG = wrkEG.OpenConnection("", dbDriverNoPrompt, False, "ODBC;dsn=EG;uid=EGuser;pwd=EGpassword;")


Ret = wrkEG.Connections.Count

If Ret = False Then
MsgBox ("There was an Error connecting to the database"), vbCritical, "Error"
End
End If

Set qdfEG = cnnEG.CreateQueryDef("EGtemp")

qdfEG.Sql = "select * from EGselect"
qdfEG.CacheSize = 500

Set Results = qdfEG.OpenRecordset.GetRows

Worksheets("Data").Activate

Worksheets("Data").Range("A1").CurrentRegion.Clear
For iCols = 0 To Results.Fields.Count - 1
Worksheets("Data").Cells(1, iCols + 1).Value = Results.Fields(iCols).Name
Next

----------------------------------------
Dim x

x = Results.RecordCount

MsgBox x

cnnEG.Close
wrkEG.Close
End Sub

Up to the line the code inserts the field headings from the Database.
When in gets to:

x = Results.RecordCount

MsgBox x

the msgbox result is -1,

I have used Results.RecordCount before using ODBC and Jet but not ODBCdirect, anyone have any idea how to pass the records back to the worksheet (i.e. place them into an array and return them to "A2:?? (?? = Cells(Results.Fields.Count, Results.RecordCount - 1)).

Sorry it's a bit vague (I have really got a clue what I'm on about at the minute), any further information needed just ask.#

Thanks,

 

[Mark W.]

Have you considered using the Data | Get External Data menu command?

 

[Ian Mac]

On 2002-08-27 10:53, Mark W. wrote:
Have you considered using the Data | Get External Data menu command?

Mark,

in short, yes. But the brief is to use ODBCDirect method as not all PC's have MSQuery on and/or are too old to run it effectively.

Also, using this method I hope to utilise temporary space on the BIG FAT Oracle server for the query while it is running and not on the Client machine (this is information new to me and I may be wrong).

As mentioned, ODBC and Jet doesn't have a problem by-passing MSQuery and has allowed those users to use the Workbook effectively.

Have you experiance of this? (I haven't)

 

[Mark W.]

No experience here. I use MS Query.

 

[Ian Mac]

On 2002-08-27 11:05, Mark W. wrote:
No experience here. I use MS Query.

Me to, and you know how my VBA is

Cheers anyway.

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-08-27 11:08

 

[Ian Mac]

Hate to do this...............Anyone!!!!

 

[Ian Mac]

I have the answer,
I thought I might share it as the code work a lot faster than MSQuery:

Private Sub Workbook_Open()
Dim wrkEG As Workspace
Dim cnnEG As Connection
Dim qdfEG As QueryDef
Dim Results As Recordset
Dim Ret As Boolean
Dim Record

Set wrkEG = CreateWorkspace("myEGdb", "admin", "", dbUseODBC)
Set cnnEG = wrkEG.OpenConnection("", dbDriverNoPrompt, False, "ODBC;dsn=EG;uid=EGuser;pwd=EGpass;")

Ret = wrkEG.Connections.Count

If Ret = False Then
MsgBox ("There was an Error connecting to the database"), vbCritical, "Error"
End
End If

Set qdfEG = cnnEG.CreateQueryDef("EGtemp")

qdfEG.SQL = "select * from vwactiveemps"
qdfEG.CacheSize = 500

Set Results = qdfEG.OpenRecordset(dbOpenDynaset)

Worksheets("Data").Activate

Worksheets("Data").Range("A1").CurrentRegion.Clear
For iCols = 0 To Results.Fields.Count - 1
Worksheets("Data").Cells(1, iCols + 1).Value = Results.Fields(iCols).Name
Next

Worksheets("Data").Range(Worksheets("Data").Cells(1, 1), _
Worksheets("Data").Cells(1, Results.Fields.Count)).Font.Bold = True

Dim x As Long
Dim array1 As Variant

With Results
.MoveLast
x = .RecordCount
.MoveFirst
End With

MsgBox x

array1 = Results.GetRows(x)

MsgBox UBound(array1, 2) + 1
MsgBox Results.Fields.Count

Application.ScreenUpdating = False

For r = UBound(array1, 2) + 2 To 2 Step -1
For c = Results.Fields.Count To 1 Step -1
If IsNull(array1(c - 1, r - 2)) Then
Worksheets("Data").Cells(r, c).Value = ""
Else
Worksheets("Data").Cells(r, c).Value = CStr(array1(c - 1, r - 2))
End If
Next
Next

Application.ScreenUpdating = True

cnnEG.Close
wrkEG.Close

End Sub

CHEERS!!