Write to Data Source Name (DSN)

[jlcca]

OS: Windows 7 Build 7601 Service Pack 1
Visual FoxPro 09.00.0000.5815 for Windows

I have an Excel spreadsheet, bra456.xls, which has one sheet named bra456

Here is how I access the bra456.xls from VisualFoxPro 9;

SET SAFETY OFF
OPEN DATABASE bradford
CREATE CONNECTION cnBradford DATASOURCE bra456
CREATE SQL VIEW vwBradford CONNECTION cnBradford AS SELECT * FROM [bra456$]
USE vwBradford
EDIT FIELDS cdescripti
DISPLAY FIELDS cdescripti
CLOSE DATABASES

Note that the bradford database has been already created.

Here is the DSN Configuration;

In the above code, I edit a field, save it, and the change is DISPLAYed.

Running the code again shows that the change made was not written to the bra456.xls file.

Opening the bra456.xls file in Excel 2000 also shows that the change was not saved.

Note that in the DSN Configuration I do NOT have the Read Only option selected.

How do I save changes to the bra456.xls file?

Regards,

Joe

 

[Olaf Doschke]

A view created this way is readonly. The view cursor is readwriteable, but what you write is not written to XLS.

To have an updateable view, some additional view propertties have to be set, either by code (see:

https://msdn.microsoft.com/library/k625abw5(v=vs.80).aspx)

or in the visual view editor.
VFP needs to know: Table, Keyfield, Updatename list, Updatable field list, and THAT you want to send updates at all.

Bye, Olaf.

 

[jlcca]

I have taken a different route, and this works;

LOCAL XLS AS ADODB.CONNECTION
LOCAL rst AS ADODB.Recordset

XLS = CREATEOBJECT("ADODB.Connection")
XLS.ConnectionString = "DSN=bra456;"
XLS.OPEN

rst = CREATEOBJECT("ADODB.Recordset")
rst.CursorLocation = 3  && adUseClient
rst.CursorType = 3  && adOpenStatic
rst.LockType = 3  && adLockOptimistic
rst.OPEN("SELECT * FROM [bra456$]", XLS)

DO WHILE .NOT. rst.EOF
  orgValue = rst.FIELDS('cdescripti').VALUE
  rst.FIELDS('cdescripti').VALUE = orgValue + " 123"
  rst.MoveNext
ENDDO

rst.CLOSE
XLS.CLOSE

I did some digging, and found a book from 2002 I have that has a chapter (Working with Remote Data) that I will have to read through a few times, to augment the info that Olaf has provided.

Regards,

Joe

 

[Koen Piller]

Hi,
I advise first to upgrade to SP2 with Hotfix.
After that you may make use of the fine classes developped by Vilhelm to import Excel into your VFP.dbf
You can explore his blog to find the procedure to your needs:

http://praisachion.blogspot.nl/2017/08/importfromxlsx-40.html

Regards,

Koen