Accessing data within objects

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm building a recordset based on a table created by a transfer of an Excel
spread sheet . After the table has been created (and populated), I do the
following:

Dim con As Object
Dim rsImportedData As Object
Set con = Application.CurrentProject.Connection
Set rsImportedData = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM tblImportedData"
rsImportedData.Open strSQL, con, 1
idxi = rsImportedData.RecordCount
idxj = rsImportedData.Fields.Count

This places the record count in idxi and the field count in idxj (which turn
out to be 1114 and 9, repectively)

Now I want to get the field names and I try:
strFieldName =rsImportedData.fields.Item_1.Name
or
strFieldName =rsImportedData.fields.[Item 1].Name

which doesn't work. errormessage:
<Object doesn't support this property or method>

What is the correct syntax here?
And is there a better way of capturing table field names?
Thanks,
Jael
 
Jael said:
I'm building a recordset based on a table created by a transfer of an
Excel spread sheet . After the table has been created (and
populated), I do the following:

Dim con As Object
Dim rsImportedData As Object
Set con = Application.CurrentProject.Connection
Set rsImportedData = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM tblImportedData"
rsImportedData.Open strSQL, con, 1
idxi = rsImportedData.RecordCount
idxj = rsImportedData.Fields.Count

This places the record count in idxi and the field count in idxj
(which turn out to be 1114 and 9, repectively)

Now I want to get the field names and I try:
strFieldName =rsImportedData.fields.Item_1.Name
or
strFieldName =rsImportedData.fields.[Item 1].Name

which doesn't work. errormessage:
<Object doesn't support this property or method>

What is the correct syntax here?
And is there a better way of capturing table field names?
Thanks,
Jael
Click to expand...

Where is "Item_1" coming from? Just use...

strFieldName =rsImportedData.fields(0).Name

....for the first field and then 1, 2, etc..
 
Thanks Rick - didn't expect such a fast reply.
"Item 1" comes from setting a watch point on rsImportedData and expanding
the object in the watch window to:
rsImportedData
|_ Fields
|_ count 9
|_ Item 1
|_ActualSize 6
|_ ...
|_Name "Prefix"
tried your suggestion:
strFieldName =rsImportedData.fields(0).Name
worked fine.

I now realize that Item 1, etc. is essentially a pointer/index value

Thanks for helping out a very old assembly language programmer.,
Jael

Rick Brandt said:
Jael said:
I'm building a recordset based on a table created by a transfer of an
Excel spread sheet . After the table has been created (and
populated), I do the following:

Dim con As Object
Dim rsImportedData As Object
Set con = Application.CurrentProject.Connection
Set rsImportedData = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM tblImportedData"
rsImportedData.Open strSQL, con, 1
idxi = rsImportedData.RecordCount
idxj = rsImportedData.Fields.Count

This places the record count in idxi and the field count in idxj
(which turn out to be 1114 and 9, repectively)

Now I want to get the field names and I try:
strFieldName =rsImportedData.fields.Item_1.Name
or
strFieldName =rsImportedData.fields.[Item 1].Name

which doesn't work. errormessage:
<Object doesn't support this property or method>

What is the correct syntax here?
And is there a better way of capturing table field names?
Thanks,
Jael
Click to expand...

Where is "Item_1" coming from? Just use...

strFieldName =rsImportedData.fields(0).Name

....for the first field and then 1, 2, etc..
Click to expand...
 
Here is some BAS module code that either prints table names in the immediate
window, or creates a table with all current table names. You might get some
insight by playing with this code.


Option Compare Database
Sub ImmediatePrintTableNames()

Debug.Print "TABLES"
AllTables

End Sub
Sub AllTables()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
' Print name of obj.
Debug.Print obj.NAME
Next obj
End Sub


Sub BuildTableOfAccessTableNames()
'Creates table and populates the table with table names

Dim DocTable As String, rs As DAO.Recordset, x As Integer, Y As Integer
Dim t As String

DoCmd.DeleteObject acTable, "TableOfAccessTableNames"

DoCmd.Hourglass True

DocTable = "TableOfAccessTableNames"
'test if exist
On Error Resume Next
DocTable = CurrentDb.TableDefs(DocTable).NAME
If Err > 0 Then
CurrentDb.Execute ("CREATE TABLE TableOfAccessTableNames (TableName
TEXT(30))")
End If

Set rs = CurrentDb.OpenRecordset(DocTable)
For x = 0 To CurrentDb.TableDefs.Count - 1
t = CurrentDb.TableDefs(x).NAME
If InStr(t, "MSys") = 0 And t <> "TableOfAccessTableNames" Then
With rs
.AddNew
![TableName] = t
.Update
End With
End If 'if not MSys table
Next

rs.Close
DoCmd.Hourglass False
End Sub
 
Thanks George - I always enjoy playing with different ways of manipulating
information
Jael

George Walsh said:
Here is some BAS module code that either prints table names in the immediate
window, or creates a table with all current table names. You might get some
insight by playing with this code.


Option Compare Database
Sub ImmediatePrintTableNames()

Debug.Print "TABLES"
AllTables

End Sub
Sub AllTables()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
' Print name of obj.
Debug.Print obj.NAME
Next obj
End Sub


Sub BuildTableOfAccessTableNames()
'Creates table and populates the table with table names

Dim DocTable As String, rs As DAO.Recordset, x As Integer, Y As Integer
Dim t As String

DoCmd.DeleteObject acTable, "TableOfAccessTableNames"

DoCmd.Hourglass True

DocTable = "TableOfAccessTableNames"
'test if exist
On Error Resume Next
DocTable = CurrentDb.TableDefs(DocTable).NAME
If Err > 0 Then
CurrentDb.Execute ("CREATE TABLE TableOfAccessTableNames (TableName
TEXT(30))")
End If

Set rs = CurrentDb.OpenRecordset(DocTable)
For x = 0 To CurrentDb.TableDefs.Count - 1
t = CurrentDb.TableDefs(x).NAME
If InStr(t, "MSys") = 0 And t <> "TableOfAccessTableNames" Then
With rs
.AddNew
![TableName] = t
.Update
End With
End If 'if not MSys table
Next

rs.Close
DoCmd.Hourglass False
End Sub





Jael said:
I'm building a recordset based on a table created by a transfer of an
Excel
spread sheet . After the table has been created (and populated), I do the
following:

Dim con As Object
Dim rsImportedData As Object
Set con = Application.CurrentProject.Connection
Set rsImportedData = CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM tblImportedData"
rsImportedData.Open strSQL, con, 1
idxi = rsImportedData.RecordCount
idxj = rsImportedData.Fields.Count

This places the record count in idxi and the field count in idxj (which
turn
out to be 1114 and 9, repectively)

Now I want to get the field names and I try:
strFieldName =rsImportedData.fields.Item_1.Name
or
strFieldName =rsImportedData.fields.[Item 1].Name

which doesn't work. errormessage:
<Object doesn't support this property or method>

What is the correct syntax here?
And is there a better way of capturing table field names?
Thanks,
Jael
Click to expand...
Click to expand...
 
Back
Top