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

[Jimmy]

hi,

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

but LEN of ALL FIELDs are "maximum" LEN

---

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 ...

[nageswaragunupudi]

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.

[nageswaragunupudi]

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

[Jimmy]

hi,

That is the issue with ADO with Excel.

ok, understand

[Jimmy]

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: Select all  Expand view

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: Select all  Expand view

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: Select all  Expand view

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}}"

[nageswaragunupudi]

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: Select all  Expand view

  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.

[Jimmy]

hi,

i have found this

You need ADOX to do it.

This is how you would create the excel file

Code: Select all  Expand view

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: Select all  Expand view

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 ..."

[Jimmy]

hi,

i have ask ChatGPT and got this

Code: Select all  Expand view

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

---

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

[nageswaragunupudi]

Yes, we can use ADOX also.