item cannot be found in collection corresponding to the requested name or ordinal

[dsal3]

I am seeking help to try and fix an error that I am getting inside of my excel vba code. I am trying to import the column names of a sql table into an excel worksheet. When I run this code:

[For intCount = 0 To rsmyf.Fields.Count
Range("A2").Offset(intCount, 0).Value = rsmyf(intCount).Name]

I get this error: Run-time error '3265'- Item cannot be found in the collection corresponding to the requested name or ordinal. The error appears here - ".Name"

Can someone tell me what I need to do to fix this?

[Rabbit]

rsmyf(intCount) This attempts to access the value of the field at the current record.

What you're probably trying to do is access the field itself. rsmyf.Fields(intCount)

[dsal3]

I made the change to rsmyf.Fields(intCount)and now I get the following error:

Run-time error '1004': Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

[dsal3]

I changed my code back to this:

[For intCount = 0 To rsmyf.Fields.Count
Range("A2").Offset(intCount, 0).Value = rsmyf(intCount).Name]

It gave me the column names of the sql table that I am referencing in the correct location in my excel spreadsheet, but it still errors and will not continue with the rest of my code.

[Rabbit]

What's the definition/sql of the recordset?

[dsal3]

I made some changes to the code that I originally posted. The connection to the sql db and table is defined above the code below:

[strSQL = "SET NOCOUNT ON"
strSQL = strSQL & " SELECT * FROM Testtbl where 1=2 "

Set rsmyf = New ADODB.Recordset
rsmyf.Open strSQL, connmyf, adOpenStatic, adLockOptimistic

For intCount = 0 To rsmyf.Fields.Count - 1
Range("A2").Offset(intCount, 0).Value = rsmyf.Fields(intCount)
Next]

[Rabbit]

Your query returns no records. That's why it can't access the "current record" because there are none.

[dsal3]

Ok, how can I fix it do that it only returns the column names?

[dsal3]

sorry, how can I fix it so that the query only returns the column names in the table?

[Rabbit]

You can either return a record, or you can query the system table.

[dsal3]

Can you tell me what the syntax should be for both?

[dsal3]

I changed my syntax to query the system table.

strSQL = "Select column_name from information_schema.columns where table_name='Testtbl'"

This returns all of the column names, however, I am still getting this error: item cannot be found in collection corresponding to the requested name or ordinal. Can someone please help me fix this error? The code is erroring out on this ' = rsmyf.Fields(intCount)'


[For intCount = 0 To rsmyf.Fields.Count
Range("A2").Offset(intCount, 0).Value = rsmyf.Fields(intCount)
Next]

[Rabbit]

If you're querying the system table, you no longer want to return the fields of the recordset. Instead, you have to iterate through the rows of the recordset.

[dsal3]

Can you tell me how that can be done in my code?

[Rabbit]

I don't know VB 4/5/6 so I can only give you pseudocode. You'll have to port it appropriately.

recordset.MoveFirst
Do While Not recordset.EOF
  something = recordset(1)
  recordset.MoveNext
Loop