MSSQL Remote View updating

[jonblack]

I have just started using MSSQL 2005 as a r/w data source for my client. This is new territory for me as I have always used local VFP databases and read only views in the past.

What I want to know is if I can use a remote view to BROWSE data and then use the BROWSE grid to make updates?

The client has several large datasets that we have created multiple custom MSSQL VIEWS of (accessed by a VFP remote view). I am trying to get around the effort of coming up with a form to edit each view.

The primary need is to quickly access the view, locate a record and make an edit. Secondary need is to APPEND/DELETE records if possible.

(If there is a simple (i.e. cheap - no budget for this project) third party tool I would be interested in suggestions.)

Jon Black

 

[TamarGranor]

Once you USE a remote view, it's just a VFP cursor and you can do almost anything with it that you can do with a table. Changes you make in the cursor get saved to the remote data source when you issue TableUpdate().

Tamar

 

[jonblack]

Ok, If I follow, this would work??

USE Remote_view
BROWSE
... make changes to records
TABLEUPDATE()

 

[Mike Lewis]

Jon,

Yes, your code will work ... but only if you explicitly make the view updateable.

Assuming you created the view in the view designer, you need to go to the Update Criteria page. Tick the box under the key icon to indicate the primary key field; then click under the pencil icon to specify which fields should be updateable (probably all of them), and finally tick the "Send SQL Updates" box, which is a sort of master switch.

Once you've saved the view, any code that updates the local cursor (including a Browse) will also update the back end.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

Something to consider:

While I have used Updateable Remote Views into MS SQL Server backends for a while now. They are easy to setup and use.

But I have found that there are times when the backend update does not occur as reliably as I wish - especially when executed on large data tables despite SQL Command parameters being included.

Perhaps, despite adjusted Connection timeout settings within the VFP Database Connection settings, the SQL Server times out during the TABLEUPDATE() or something.

I have begun transitioning to SQL Passthru and have found that it seems to work more reliably for me. If you are just getting started working with MS SQL Server as a backend, you might want to consider this.

Maybe others here have their own opinion on the most reliable approach to use.

Good Luck,
JRB-Bldr