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

[Ian Mac]

All,

Not really an Access question, BUT thought someone may have an idea as it's related to querying other data sources.

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, Not the expected 5000 (the amount of records in the original table.)

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)), or to another table (i.e. Access).

Sorry it's a bit vague (I haven't really got a clue what I'm on about at the minute), any further information needed just ask. Or any directions someone could nudge me in.

Thanks,

 

[RichMorrison]

Ian,

This sounds sort of familiar.

I think RecordCount is not correct until you do something with the recordset.

Try MoveFirst before testing RecordCount.
If that does not work, try MoveLast.

HTH,
RichM

 

[Ian Mac]

Ian,

This sounds sort of familiar.

I think RecordCount is not correct until you do something with the recordset.

Try MoveFirst before testing RecordCount.
If that does not work, try MoveLast.

HTH,
RichM

Your quite right (highlighted below), you pointed me in the right direction.

For anyone interested here's 2 ways to do it:

1)

Private Sub ODBCDirectTest()
Dim wrkEG As Workspace
Dim cnnEG As Connection
Dim qdfEG As QueryDef
Dim Results As Recordset
Dim Ret As Boolean
Dim sqlEG As String
Dim i, iCols As Integer

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



sqlEG = "SELECT ACTIVEEMPS.EMPLOYEEID, ACTIVEEMPS.FIRSTNAME, ACTIVEEMPS.MIDDLEINITIAL, ACTIVEEMPS.LASTNAME, ACTIVEEMPS.FULLNAME, ACTIVEEMPS.EMPLOYEENUMBER, ACTIVEEMPS.HIREDATE, ACTIVEEMPS.WAGER" & _
"ATE, ACTIVEEMPS.ACCRUALPROFILE, ACTIVEEMPS.SITE, ACTIVEEMPS.DEPT, ACTIVEEMPS.JOBCODE, ACTIVEEMPS.ACTIVITYCODE, ACTIVEEMPS.LINEMANAGER, ACTIVEEMPS.EMPLOYEETYPE, ACTIVEEMPS.TEMP" & Chr(13) & "" & Chr(10) & "FROM TK" & _
"CSOWNER.ACTIVEEMPS ACTIVEEMPS"
Set qdfEG = cnnEG.CreateQueryDef("EGtemp", sqlEG)

qdfEG.CacheSize = 100

Set Results = qdfEG.OpenRecordset

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").Cells(2, 1).Activate

While Not Results.EOF
For i = 0 To 15
If IsNull(Results.Fields(i).Value) Then
ActiveCell.Value = ""
Else
ActiveCell.Value = CStr(Results.Fields(i).Value)
End If
ActiveCell.Offset(0, 1).Activate
Next i
ActiveCell.Offset(1, -16).Activate
Results.MoveNext
Wend

cnnEG.Close
wrkEG.Close
End Sub

2)

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

The second version runs MUCH faster than the first AND MUCH
MUCH fatser than MSquery.

Thanks