Hello,

I have one mdb file ("res.mdb") containing the m records x n fields
employee table (tbRes). With the sub RetrieveData (asking for mdb
filename, SQL command and data-destination range), I get the employee
data in a dynamic range of the "employee" worksheet.

My scenario is: the calling sub activates the worksheet and calls the
sub RetrieveData passing the asked parameters

The sub performs correctly.

My problem is this:
I have added one table (tbPassword) in res.mdb, containing only one
field and only one record storing a password the user can modify. In
this case, however, the function fails to retrieve the password value in
the employee worksheet range "M2".

Debugging the code I've seen the recordset.recordcount property equals
-1. Surely something is wrong but I'm not able to understand where and
why.
I've also tried to use the cn and rs.Cursorlocation = 3, but the result
is the same.

SNIPPET OF THE CALLING SUB:
*...

'retrieving the tbres table data
Sheets("employee").activate
mdbfilename = ThisWorkbook.Path & "\res.mdb"

SQLstr = "SELECT tbRes.RepUtil, tbRes.ImpUtil, tbRes.ID,
tbRes.Prof, tbRes.Lastname, " _
& "tbRes.Name FROM tbRes WHERE (((tbRes.Ceased)=False)) ORDER BY
tbRes.Lastname, tbRes.Name"
Call RetrieveData(mdbfilename, SQLstr, "A2")
' the range "A2" populates correctly

'retrieve tbPassword data
SQLstr = "SELECT tbPassword.PSWRD FROM tbTassword;"
Call RetrieveData(mdbfilename, SQLstr, "M2")
....
*


*Public Sub RetrieveData(mdbfilename As String, SQLcmd As String,
destrange As String)
Dim cn As Object
Dim rs As Object
Dim Sh As Worksheet
On Error GoTo err_hnd

Set cn = CreateObject("ADODB.connection")
Set rs = CreateObject("ADODB.recordset")
Set Sh = ActiveSheet

With Sh

'open the connection...
With cn
..CursorLocation = 1
..Provider = "Microsoft.jet.OLEDB.4.0"
..Properties("Data Source") = mdbfilename
..Properties("Jet OLEDB:Database Password") = PWORD
..Open
End With

'populate the recordset
rs.CursorLocation = 1
rs.Open SQLcmd, cn, 1, 3, adCmdText

'popolate sh with the rs records
..Range(destrange).CopyFromRecordset rs

End With

'
Set Sh = Nothing
Set rs = Nothing
Set cn = Nothing
Exit Sub
err_hnd:
MsgBox Err.Description & "/" & Err.Number & " Sub
RecuperaDatiEsterni"
Resume Next
End Sub*


Thanks,


--
eggpap

Emiliano
Excel 2003 on Vista HP System - can use VBA
------------------------------------------------------------------------
eggpap's Profile: http://www.thecodecage.com/forumz/member.php?userid=90
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94944