ODBC To OLE DB

[PaulHInstincticve]

I think I may have got a little left behind here. I have been trying to implement my app on some new web sites recently and I think it is not working because VFP ODBC drivers are no longer supported in the latest operating systems in favour of OLE DB technology. Can anyone confirm that my thoughts, after some quick reading, are correct and if so where can I check syntax for my replacement connection string and what error message would I get if the drivers are not yet installed on the ISPs server?

I was previously using VFP ODBC drivers using a connection string

con.OPEN("DRIVER=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\data\myapp\;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE")

This seemed to work for ISPs running Windows 2000 servers but ISPs running Windows 2003 had to be asked each time to reinstall their VFP ODBC drivers before it would work. The usual give away was the error message

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Driver does not support this function

Asking the ISP to reinstall ODBC drivers until now has worked but upon further reading it seems that OLE DB is the technology that has replaced ODBC since VFP 6 and really I should be using that now instead of ODBC - is this correct? If it is correct, apart from the connection string is everything else pretty much compatible with what I was doing with ODBC (simple SQL select, delete and update that's all)?

If I convert to OLE DB, can I assume that that will already be installed on Windows server 2003 and above?

I have tried the following connection string as per an example I found on the web

con.OPEN("Provider=vfpoledb.1;Source=c:\data\myapp;Collating Sequence=general")

This generates the error

Microsoft OLE DB Provider for Visual FoxPro error '80004005'

Feature is not available.

Is this error a connection string syntax error or might the driver not be installed? Can anyone link me to syntax for other connection string options, how do I for example turn deleted records off as I did with the ODBC connection string? Many thanks

Paul

 

[bborissov]

You must use CursorAdapters with ADO connection. All SPT functions are ODBC based and can't be used with OleDB.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[PaulHInstincticve]

Borislav,

What are cursoradapters and SPT? Sorry I should have clarified that I am connecting to my VFP tables from ASP. Do your comments still apply and if so can you briefly outline what this means in terms of syntax for a simple code example such as

con.OPEN("DRIVER=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\data\myapp\;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE")

Set categoryrs = Server.CreateObject( "ADODB.Recordset" )
categoryrs.ActiveConnection = Con
sqlString = "SELECT catdesc from bkitcategory"
categoryrs.Open sqlString

WHILE NOT categoryrs.EOF
response.write(categoryrs("desc"))
categoryrs.MoveNext
wend

con.close
categoryrs.close
set con=nothing
set categoryrs=nothing

Thanks

Paul

 

[bborissov]

No, it is only valid if you work with VFP
This works:

oConn.Open("Provider=vfpoledb.1;Data Source=D:\ALL_ZAPL_OLD\b_data;Collating Sequence=machine")

Change Source to Data Source in your connection string and it must works

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[PaulHInstincticve]

Borislav,

Thanks that's great it works. The sample string I picked up had Source instead of Data Source in it. I asked earlier about other parameters to OLE DB connection strings for example to replace

Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE

Do you know of a link where I can find other available parameter syntax? Thanks

Paul

 

[bborissov]

I don't know. Sorry.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

I was able to open connection with following string:
Provider=vfpoledb.1;Data Source=D:\ALL_ZAPL_OLD\b_data;Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;Deleted=yes


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[PaulHInstincticve]

Thanks for that. One final question hopefully - when I have been using ODBC drivers for VFP with ASP I have had a problem inserting more than 254 characters at a time into memo fields. A workaround has been to add a new record into a buffer, to add contents to the memo field in the buffer 254 characters at a time until they have all been added and then to flush the buffer. Is this messy workaround still required with OLE DB or is the 254 character limit in the Insert SQL command lifted? Thanks

 

[bborissov]

Don't know. Never used VFP tables with ODBC or OLE DB. You could try to pass a string bigger that 254b as parameter.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP