CopyFromRecordset problem - empty rs?

[gbshahaq]

Hi

i've been using ADO recordsets for loading data from Access to Excel for some time with no issues - until now.

i set up the following Function in Excel to use as a general module for importing data from like-named queries in my Access db (Access 2000)
There is definitely data in the queries, and can fetch data using ODBC/Query Wizard with no issues. However, using the code below i only get the field names and no data (no errors either...) - it's as if the recordset is empty?

Please help...getting rather annoyed right now....

Function BaseImport(SheetName As String)

Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As String, TR As Range, DSrc As String
Dim QryName As String

Set TR = Range(SheetName & "!A1")
Set TR = TR.Cells(1, 1)

DSrc = "\\server1\files\Scorecard.mdb"


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DSrc & ""
QryName = "qry_MTH_" & SheetName

strSQL = " SELECT * FROM " & QryName
'Debug.Print strSQL

Set rs = New ADODB.Recordset
rs.Open strSQL, cn


For intcolindex = 0 To rs.Fields.Count - 1
    TR.Offset(0, intcolindex).Value = rs.Fields(intcolindex).Name
Next
TR.Offset(1, 0).CopyFromRecordset rs


rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Function

 

[checoturco]

strSQL = " SELECT * FROM " & QryName
'Debug.Print strSQL

Set rs = New ADODB.Recordset
rs.Open strSQL, cn

rs.movelast
debug.print rs.recordcount


what you get with this code?

 

[gbshahaq]

hi checo

with the code I pasted, I get:
Rowset does not support fetching backward

if i open the rs with options "adOpenKeyset, adLockBatchOptimistic", I get the following msg:
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record

 

[checoturco]

try this,

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM " & QryName, cn



P.S. you can put here your query without concatnation?

 

[gbshahaq]

no same error.
the only query that works without incident from this db is one that I know returns one row - and hey presto! it returns one row...

hmmmpppphhhh

 

[checoturco]

did you saw my P.S. ?

 

[DB7]

Is QryName showing the right Name?

rs.Open QryName, cn, adOpenDynamic,adLockPessimistic

If Not rs.EOF And Not rs.bOF Then

Else
MsgBox "No current record"

 

[gbshahaq]

hi both

yes - QryName is showing the correct name - even hardcoding the query name makes no difference - it's as if the recordset is empty...

i have a feeling it has something to do with the access db itself though - i have no clue what though :-(

i can connect fine to a similar db i created a while ago - and the query data comes through just fine.
I checked through the options on the two dbs and cannot identify any differences between the setup of the two.

Hopefully someone has come across this issue before? a google provided no useful answers though....

 

[jubb]

Hi,

I had a similar problem with a recordset a couple of weeks ago, I couldn't work out why it would work in one db and not the other, after hours of checking & trying different things. What ended up working for me even though it sound illogical is making a copy of the DB that the recordset was working in, deleting all tables, queries etc then importing the info from the DB that wasn't working. After doing this I made no changes to my tables, queries and recordsets and it worked fine.

Still can't work out why though.

Maybee the same approach might work for you.

Jubb

 

[gbshahaq]

i'll try that out jubb - thanks.
these things were sent to try us....