ADO / Excel / XBROWSE / FWAdoStruct() / Edit()

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Sun Aug 13, 2023 04:01 AM

hi,

i "read" *.XLSx using ADO to display in XBROWSE
that "sems" to work ... so far

but LEN of ALL FIELDs are "maximum" LEN :shock:

---

does Function FWAdoStruct(objRS) work only on "active" Record :?:

using FWAdoStruct(objRS, .T.) i got e.g.

{CHINAART, C, 255, 0, 202, .T., 255, 0, 255, 255}

8th Element Type "C" is 0 while in DBF FIELD is EMPTY() in 1st Record

---

any Idea what i can do ... :idea:

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Sun Aug 13, 2023 10:32 AM

That is the issue with ADO with Excel.

Whatever length you specify while creating the table, that is ignored.

ADO RecordSet field object always shows oField:DefinedSize as 255.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Sun Aug 13, 2023 10:36 AM

does Function FWAdoStruct(objRS) work only on "active" Record :?:

It should work on both existing and blank records, but let me check again.

Please post a DBF Structure here so that we both work on the same structure

Regards

G. N. Rao.

Hyderabad, India

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 03:33 AM

hi,

nageswaragunupudi wrote:That is the issue with ADO with Excel.

ok, understand

greeting,

Jimmy

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 03:49 AM

nageswaragunupudi wrote:
does Function FWAdoStruct(objRS) work only on "active" Record :?:
Please post a DBF Structure here so that we both work on the same structure

Code (fw): Select all Collapse

Local aStruct := {                        ;
   { "TEST_C"      ,"C" ,        10,         0 } ,;
   { "TEST_N"      ,"N" ,        10,         2 } ,;
   { "TEST_D"      ,"D" ,         8,         0 } ,; 
   { "TEST_L"      ,"L" ,         1,         0 }   } 
 
DbCreate( "TESTTYPE.DBF", aStruct, "FOXCDX" )

---

Code (fw): Select all Collapse

cQuery = "CREATE TABLE testtype ( TEST_C TEXT ( 10) , TEST_N DOUBLE , TEST_D DATE , TEST_L BIT  )"

var2char( aStruct ) =
"{{TEST_C, C, 255, 0, 202, .T., 255, 12, 255, 255},
{TEST_N, N, 17, 2, 5, .T., 8, 8, 15, 255},
{TEST_D, D, 8, 0, 7, .T., 8, 8, 255, 255},
{TEST_L, L, 1, 0, 11, .T., 2, 2, 255, 255}}"

Code (fw): Select all Collapse

cQuery = "CREATE TABLE testtype ( TEST_C TEXT ( 10) , TEST_N NUMBER , TEST_D DATE , TEST_L LOGICAL  )"

var2char( aStruct ) =
"{{TEST_C, C, 255, 0, 202, .T., 255, 12, 255, 255},
{TEST_N, N, 17, 2, 5, .T., 8, 8, 15, 255},
{TEST_D, D, 8, 0, 7, .T., 8, 8, 255, 255},
{TEST_L, L, 1, 0, 11, .T., 2, 2, 255, 255}}"

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 04:44 AM

FWAdoStruct() gets the information from each field object of the RecordSet.
The issue with ADO for Excel is, whatever length of char we specify it always returns 255.
oField:DefinedSize.

Please try this:

Code (fw): Select all Collapse

   n := 0
   aFields := {}
   do while n < oRs:Fields:Count()
      WITH OBJECT oRs:Fields( n )
         AAdd( aFields, { :Name, :Value, :Type, :DefinedSize, :ActualSize, :Precision, :NumericScale } )
      END
   enddo
   XBROWSER aFields

Whatever FWAdoStruct() shows is what ADO informs us.

Regards

G. N. Rao.

Hyderabad, India

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 04:53 AM

hi,

i have found this

You need ADOX to do it.

This is how you would create the excel file

Code (fw): Select all Collapse

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog

cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="
& myfile & ";Extended Properties=Excel 8.0"
Set tbl = New ADOX.Table
tbl.Name = "Sample"

'do this for each column in the table
Set col = New ADOX.Column
With col
.Name = "myTipe"
.Type = adVarWChar
.DefinedSize = 80
End With
tbl.Columns.Append col

....
You can then open the file with ADO to write your info

other Sample

Code (fw): Select all Collapse

Sub Main()
On Error GoTo CreateAutoIncrColumnError

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & "C:\Test.XLS" & ";Extended Properties=Excel 8.0" Set cat.ActiveConnection = cnn

With tbl
.Name = "MyContacts"
Set .ParentCatalog = cat
' Create fields and append them to the new Table object.
.Columns.Append "ContactId", adInteger
' Make the ContactId column and auto incrementing column
.Columns("ContactId").Properties("AutoIncrement") = True
.Columns.Append "CustomerID", adVarWChar
.Columns.Append "FirstName", adVarWChar
.Columns.Append "LastName", adVarWChar
.Columns.Append "Phone", adVarWChar, 20
.Columns.Append "Notes", adLongVarWChar
End With

cat.Tables.Append tbl


cnn.Close
Set cat = Nothing
Set tbl = Nothing
Set cnn = Nothing
Exit Sub

CreateAutoIncrColumnError:

Set cat = Nothing
Set tbl = Nothing

If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close
End If
Set cnn = Nothing

If Err 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If

End Sub

100% working. (just need a correct file/path.) +
Add Ref : (Menu Tools - Réf)
Microsoft ADO +
Microsoft ADO Ext

it is for Excel 8 so i need to change "Provider" String which is no Problem

but how to handle "Set cat = New ..." :?:

greeting,

Jimmy

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 07:17 AM

hi,

i have ask ChatGPT and got this

Code (fw): Select all Collapse

FUNCTION Main()
    LOCAL cat, tbl, col
    cat := AdoxCreateObject("ADOX.Catalog")
    tbl := AdoxCreateObject("ADOX.Table")
    col := AdoxCreateObject("ADOX.Column")
    
    cat:ActiveConnection := "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + myfile + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"
    
    tbl:Name := "Sample"
    
    col:Name := "Header1"
    col:Type := adVarWChar
    col:DefinedSize := 255
    tbl:Columns:Append(col)
    
    col:Name := "Header2"
    col:Type := adDouble
    tbl:Columns:Append(col)
    
    col:Name := "Header3"
    col:Type := adDate
    tbl:Columns:Append(col)
    
    col:Name := "Header4"
    col:Type := adBoolean
    tbl:Columns:Append(col)
    
    cat:Tables:Append(tbl)
    
    LOCAL conn, rs
    conn := HbCreateObject("ADODB.Connection")
    rs := HbCreateObject("ADODB.Recordset")
    
    conn:Open(cat:ActiveConnection)
    rs:Open("SELECT * FROM [Sample]", conn, 1, 3)
    
    rs:AddNew()
    rs:Fields(1):Value := "Text Data"
    rs:Fields(2):Value := 123.45
    rs:Fields(3):Value := DATE(2023, 8, 14)
    rs:Fields(4):Value := .T.
    rs:Update()
    
    rs:Close()
    conn:Close()
    
    cat:Tables:Refresh()
    cat:Tables("Sample"):Columns.Refresh()
    
    cat:Save(cat:ActiveConnection)
    
RETURN NIL

PROCEDURE AdoxCreateObject(cObjectName)
    LOCAL oADOX
    oADOX := HbCreateObject(cObjectName)
RETURN oADOX

this CODE is not "perfect" but give me a Idea what to do :idea:

---

Question : if i use adVarWChar instead of adVarChar is the same FIELD LEN :?:

greeting,

Jimmy

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO / Excel / XBROWSE / FWAdoStruct() / Edit()
Posted: Mon Aug 14, 2023 07:17 AM

Yes, we can use ADOX also.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion