ODBC Connectivity Error Invalid Key Word Spec 2

[TimRelton]

Hi,

I'm trying to update via ODBC using a cursor as per

http://www.tek-tips.com/viewthread.cfm?qid=812206

but when the curor moves off the current record to update I get Connectivity Error Invalid Keyword Specefication.

The current code I am using looks like...

CLOSE ALL
CLEAR ALL
CLEAR

OPEN DATABASE ('C:\Program Files\Opera II\data\comp_z')

CREATE CONNECTION "Sage Connector" DATASOURCE "SageLine50v11" USERID "MANAGER" PASSWORD ""

CLEAR
DISPLAY CONNECTIONS && Displays named connections in the database

STORE SQLCONNECT('Sage Connector', 'MANAGER', '') TO gnConnHandle
IF gnConnHandle <= 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
= MESSAGEBOX('Connection made', 48, 'SQL Connect Message')

SQLSETPROP(gnConnHandle, 'asynchronous', .F.)
SQLEXEC(gnConnHandle, 'SELECT * FROM NOMINAL_LEDGER', 'Sage_Viewer')
t1= CURSORSETPROP("AllowSimultaneousFetch", .t., "Sage_Viewer")
t2= CURSORSETPROP("Tables","NOMINAL_LEDGER", "Sage_Viewer")
t3= CURSORSETPROP("KeyFieldList","Account_ref", "Sage_Viewer")
t4= CURSORSETPROP("UpdatableFieldList","Account_ref, Name", "Sage_Viewer")
t5= CURSORSETPROP("UpdateNameList","Account_ref NOMINAL_LEDGER.ACCOUNT_REF, Name NOMINAL_LEDGER.NAME", "Sage_Viewer")
t6= CURSORSETPROP("SendUpdates", .t., "Sage_Viewer")

BROWSE

= SQLDISCONNECT(gnConnHandle)
ENDIF

DELETE CONNECTION "Sage Connector" && Removes the connection just created



Basically I'm trying to read Sage Line 50 files into VFP, amend them through the browse window and send back the changed data. All the CURSORSETPROP calls return .t.

To be honest it's driving me mad !

Any one with better experience got any ideas ??

Many Thanks

Tim

 

[Mike Lewis]

Tim,

I can't off-hand see the reason for this error. But you can make the problem easier to solve by simplifying the code.

I suggest you remove all the stuff about creating the connection object. Just create the connection manually in the DBC (using the connection designer). You only need to do that once. Then call SQLCONNECT, and then SQLEXEC. You don't need to disconnect each time ... just leave the connection open ... and you don't need to delete the connection object from the DBC.

Next, try just reading the data without updating it. Then maybe just update one field at a time.

The point of all this is that it will remove some of the variables, which might make it easier to spot where the error is occurring.

There are a couple of other things you could do. First, scrap the whole thing and use a remote view instead. Or, read the data into a cursor as you are doing, but don't make the cursor updateable. Instead, send an UPDATE command when you want to update the data.

See how you get along with the above, and report back.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[TimRelton]

Thanks for that Mike,

Sadly I tried all of that to begin with which is why I went down the route of doing it the hard way. It seems to be getting hung up on the Key Index fields for some reason.

Could it be something to do with the ODBC drivers ?

 

[Mike Lewis]

Tim,

Could it be something to do with the ODBC drivers ?

Which driver are you using? What is the underlying back end database?

... which is why I went down the route of doing it the hard way.

Hmmm. Are you saying that you did it the hard way because the easy way failed? That can't be right, surely.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[TimRelton]

Mike,

Well yes odd though it may sound, I figured that if the wizards didn't work then at least I could code it and try to work out what was going wrong, which at least has enlightened me about ODBC a bit but hasn;t worked any better.

The ODBC driver I am using is the Sage Line 50 v11 one supplied by Sage with their accounts packages. I'm looking into a bolt on for a potential client and since I'm more used to coding in Fox for Pegasus Opera it's a new area at the moment.

The database at the backend is a Sage .dta structure. I can happily read it and change the data in the remote view on both my code and also the View Designed one but get the error when the data is trying to be sent back upstream to synchronise.

If it helps I was playing around with a few things yesterday and noticed that by displaying warnings on the view designer I get two messages, 1. The timeout cannot be set and 2. The sync/async mode cannot be set. Then the connection reports successful. This appears quite odd as the code reported all the variables as .t. when I checked them.

Thanks for your time so far :O)

Tim

 

[alvechurchdata]

Sage Line 50

I had problems trying to talk to a Sage package some years ago. It was so very much harder than Opera that I gave in and we bought a DLL from a Sage specialist.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[TimRelton]

Thanks both of you, after further rummaging I think I may have the answer here...

http://www.accessmonster.com/Uwe/Forum.aspx/access-externaldata/1473/ODBC-SAGE-LINK

Looks like it may well be to do with a read only ODBC being shipped with the standard product or something similar.

Mike: Thanks for your help
Geoff: Any idea where you got the DLL from ??

Many Thanks

Tim Relton
Tektonic Ltd

http://www.tektonic.co.uk

 

[alvechurchdata]

Geoff: Any idea where you got the DLL from ??

I've lost their phone number but the copyright on the DLL is to Triplentry Solutiions Ltd. I seem to remember they were London-based but then moved to Australia.

It wasn't an ODBC solution, the DLL exposed methods that let me log in to Sage and create invoices as though I were a user. It worked well once we'd got the paths, IDs, and passwords sorted out.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Tim,

From what Geoff said, it sounds like the DLL route could be the way to go. I can only add that I have one good client who sells a commercial package, written in VFP, which interfaces to Sage. However, I believe that they also had difficulties in going via ODBC, and ended using CSV files to import and export instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[TimRelton]

Thanks Guys,

I'll probably look into the DLL option. The CSV one would work but it ins't really the seamless idea I had in mind.

If it's of any future use I have recently written an Opera to Sage conversion program which uses the csv method of Sage import.

Tim

 

[Mike Lewis]

Tim,

I have recently written an Opera to Sage conversion program which uses the csv method of Sage import

That's good to know. I haven't come across anyone who is migrating from Opera to Sage, but I do have a client who regularly imports data into Opera. I'm not sure exactly how they do it, but I suspect it's a good old CSV.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[TimRelton]

Mike,

Opera bespoke is my specialist area... I was involved in the Pre Opera Sequel programme and have been developing with the product since then. If I can be of help let me know.

Tim

 

[Mike Lewis]

Tim,

It would be more appropriate to continue this discussion outside the forum. Perhaps you could contact me via my web site (see below).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-consult.com)

 

[TimRelton]

No problem. Sorry for taking the thread off topic.