foxpro dbc connection always readonly???!! 2

[rorki]

Well, I've tried four or more different ways of connecting to a foxpro database (DBC not DBF) - (using the connection wizard and/or programming code) - the connection always tests okay, I have three tables available, I can loop the field values out to text boxes, or assign them implicitly.
BUT CAN I UPDATE THEM???!!! (That would be no)
I've searched everywhere on the web over the last few weeks, hence the differing methods at connecting.
I'm using VB6(SP5) on WinXP Pro(SP1), I've tried this on a Win98 PC too with the same results.
Is this a problem with the database?
The error messages are either 3027: object or database is readonly, or Operation was cancelled, or SQL statement too long... dependent on which method used.
Does anyone have a connection method (prefferably in code) which includes userID and Password (required) to connect to the DBC file and update fields within the tables from VB text boxes.

This is REALLY frustrating - I'm still learning but I feel so close.

Rorki

 

[vbkris]

check the properties of ur foxpro file, if it is read only change it.

Known is handfull, Unknown is worldfull

 

[rorki]

vbkris,

thanks, but no, all files are definitely not read-only.
If it helps, these files are part of a Telemagic database created around FoxPro. I've tried the same methods to connect to sample dbf/dbc files from the MicroSha(o)ft website and there's no difference.
I'm attempting to write a small/fast viewer/update program in order to dump large amounts of text into memo fields for each record. Doing this with the send keys method is slow and problematic - specifically Tabbing between fields in the Telemagic application is difficult since it refuses to recognise the TAB command, even tried using virtual key-codes with the KEYEVENT commands, all works fine with an Access database, or even notepad, but not Telemagic.
Otherwise I could use the clipboard...

Any ideas?

Rorki

If you can't beat them, hire someone who can, who wears gloves...

 

[vbkris]

do u use a connection string to connect to the database?

Known is handfull, Unknown is worldfull

 

[rorki]

Okay, here's the whole thing, the form itself consists of 22 text boxes (group array), an edit, update, forward and backward button. Also a standard Data control.
In the example that follows, I don't have an option for username/password - I should have, but it allows me to access the fields anyway. Is there a way to include them?
Obviously the database isn't there, but you could substitute a sample one quite easily.

This seems such a simple exercise, why won't it work...

Option Explicit

Dim n As Integer 'field loop

Private Sub Form_Load()

Dim FoxDataDB As Database
Dim FoxDataRS As Recordset
Dim dy As Recordset
Set FoxDataDB = Workspaces(0).OpenDatabase( _
"T:\tm4\Data\CVMagic\contacts.DBC", _
False, False, "FoxPro DBC;&quot
Set FoxDataRS = FoxDataDB.OpenRecordset("Contact1&quot
Set Data1.Recordset = FoxDataRS

Data1.Recordset.MoveFirst

Call fillfields ' fill text boxes with field values
Shape1.FillColor = vbBlue ' to show edit mode

End Sub

Private Sub Command1_Click()
If Data1.Recordset.EditMode = 1 Then GoTo 10
Data1.Recordset.Edit
Shape1.FillColor = vbRed 'indicate edit mode active

10
End Sub

Private Sub Command2_Click()

If Data1.Recordset.EditMode = 0 Then MsgBox "not in edit mode": GoTo 10
n = 6
looptext = Text1.Text
Data1.Recordset.Value = looptext

Data1.Recordset.Update
10

End Sub

Private Sub Command3_Click()
Data1.Recordset.Close
Set Data1.Recordset = Nothing
Unload Me
End Sub

Private Sub Command4_Click()

Data1.Recordset.MoveNext
If Not (Data1.Recordset.EOF) Then
Call fillfields
Else
Data1.Recordset.MoveLast
End If
Shape1.FillColor = vbBlue
End Sub

Private Sub Command5_Click()
Data1.Recordset.MovePrevious
If Not (Data1.Recordset.BOF) Then
Call fillfields
Else
Data1.Recordset.MoveFirst
End If
Shape1.FillColor = vbBlue
End Sub

Private Sub fillfields()

For n = 1 To 21 ' no of text boxes
Text1.Text = "" 'incase Null value
Text1.Text = (Data1.Recordset & "&quot 'incase Null value
Next n

End Sub


If you can't beat them, hire someone who can, who wears gloves...

 

[vbkris]

well...
i have worked max with ADO only, is it possible for u to shift to ADO?

Known is handfull, Unknown is worldfull

 

[rorki]

No problem:

Built this string up via the Adodc1 control properties:

Provider=MSDASQL.1;Persist Security Info=False;User ID=tst;Extended Properties="Driver={Microsoft FoxPro VFP Driver (*.dbf)};UID=;SourceDB=T:\tm4\Data\CVMagic\contacts.DBC;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

how does this look - should I use a modified version in code...?

If you can't beat them, hire someone who can, who wears gloves...

 

[vbkris]

Nope dont touch it simply use it in the code...

Known is handfull, Unknown is worldfull

 

[rorki]

now in code I have this (if I alter text in a field it's live data right? if then I move to next record it should update automatically, but when I move back it's the same data as before...)
There are two versions below, both connect beautifully, can move forward and backwards through the records.

'database container
'oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBC;" & _
"SourceDB=T:\tm4\Data\CVMagic\contacts.dbc;" & _
"Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

'Free Table Directory
oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=T:\tm4\Data\CVMagic;" & _
"Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

mySQL = "SELECT * FROM contact1"

'Set recordset properties & open
With oRS
.Source = mySQL
.ActiveConnection = oConn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Open
End With

If you can't beat them, hire someone who can, who wears gloves...

 

[vbkris]

well what is the query that u use to update the records?

Known is handfull, Unknown is worldfull

 

[rorki]

ahhhh.... have I been missing something obvious???
You're saying I need to use a query in order to update the current record? I read somewhere that the update is automatic when I move to the next record - anything I altered in a text box is updated to the corresponding field...?
Is there a special way in which I bind text boxes to their respective fields in the current record?
Perhaps my initial question should have been, "how can I link a text box to a live field in a foxpro database, so that updates are instant?"
vbKris - am I losing it?!!

If it's nothing to do with the text box properties, then perhaps you could give me an example of a query that might work in my situation...PLEASE!

If you can't beat them, hire someone who can, who wears gloves...

 

[CCLINT]

1. Try using the OLEDB provider instead of the MS ODBC driver.

2. You are using a client side cursor, so the Update method will update the Data in the recordset, but not the database. For that you would need to use the UpdateBatch method.

3. What happens when you use a ServerSide cursor instead?

4. I would suggest just disconnecting the recordset from the active connection, and whenever you change a record, execute an Action query ("UPDATE ....&quot to change the record in the DATABASE.

 

[rorki]

okay, using the server side cursor makes no difference, still connects fine - cannot update still.
I now populate a flexgrid with two columns, fieldname and field value.(Rather than use text boxes)
Have changed the connection and update methods to the following:
>>>PORTION OF CONNECTION CODE<<<
'Set recordset properties & open
With oRS
.Source = mySQL
.ActiveConnection = oConn
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open
End With

If oRS.State = False Then Command6_Click
looptext = &quot;&quot;
For n = 1 To 50
gridx.TextMatrix(n, 0) = oRS.Name
gridx.TextMatrix(n, 1) = (oRS)
Next n

>>> UPDATE <<< this is where I'm getting errors...

Private Sub Command9_Click()
oRS(&quot;gender&quot = &quot;Male&quot;
oRS.Update (&quot;gender&quot, &quot;Male&quot;
End Sub

'use either way same error

Private Sub Command10_Click()
oRS(&quot;Gender&quot = &quot;Female&quot;
oRS.Update
End Sub

Error is:
runtime error'-2147467259 (80004005)':
[Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long.

I had thought that using field names would get around the 2028 byte limit, but it doesn't seem to matter? (There are around 170 fields in the table.)

Where now?
TIA (Thanks In Advance)

If you can't beat them, hire someone who can, who wears gloves...

 

[vbkris]

execute more than one query?

Known is handfull, Unknown is worldfull

 

[CCLINT]

First of all, you cannot update with a adOpenForwardOnly cursor. Use a Keyset.

Secondly, there are known problems with some drivers if too many fields are in the query.
Try limiting the number of fields used, or take a second look at the database design and consider breaking the table down into smaller tables - normalization of the database.

Thirdly, as I suggested, consider using disconnected recordsets (client side cursor only) and using action queries to update the database.

 

[rorki]

CCLINT - thanks for your persistence, went looking at other methods to reduce the amount of fields... now have this, and updates working too (kind of) more on this in a moment:

'database container
Set oConn = New ADODB.Connection
oConn.Mode = adModeReadWrite
oConn.Open &quot;Driver={Microsoft Visual FoxPro Driver};&quot; & _
&quot;SourceType=DBC;&quot; & _
&quot;SourceDB=T:\tm4\Data\CVMagic\contacts.dbc;&quot; & _
&quot;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;&quot;

mySQL = &quot;select firstname, surname, discipline, dated, datebirth, xquals, languages, xcareer from contact1 WHERE tag6 = 1&quot;
Set oRS = oConn.Execute(mySQL)


Okay, so I populate my text boxes with the field data, amend the data in the text boxes and send it back out with:

defaultupdate = &quot;UPDATE contact1 SET discipline = '&quot; & Text1(2).text & &quot;' WHERE tag6 = 1&quot;
oConn.Execute (defaultupdate) ' this works!
defaultupdate = &quot;UPDATE contact1 SET xquals = &quot; & Chr(34) & Text(4).text & Chr(34) & &quot; WHERE tag6 = 1&quot;
oConn.Execute (defaultupdate) ' this doesn't work!

In Fact, any long strings won't go through - I get the error
...[ODBC Visual FoxPro Driver]Command contains unrecognised phrase/keyword.

Getting so close now, how do I over come this limit...???

thanks both so far...

If you can't beat them, hire someone who can, who wears gloves...

 

[CCLINT]

As I mentioned before, try using the OLEDB provider instead of the ODBC driver

Change Chr(34) to Chr(39), or use the single quotes as you did in the first UPDATE query...

 

[chandraIRL]

Hi rorki
they is a bug in FPODBC driver (source white paper from Microsoft). there is a another way to access and update foxpro tables as I worked for a project foxpro as backend.
but it is not a straight way accessing the database.
the solution is make a link tables in MSACCESS (using ODBC drivers). then connect to MSACCESS then u can do what ever u want.
TRY IT
- CHANDRA

 

[rorki]

CCLINT - looked into OLEDB, seems I'd have to purchase VFP in order to install the vfpoledb.dll, unless you know another way (earlier versions of MDAC??)
No joy using CHR$ 39. Saw a couple other un-answered links from last year with same problem - long string not handled. Nothing back in email yet from them - if they ever fixed it...

CHANDRA - I've used linked tables before from Access to Excel and can't see this being a great solution - as you said, it's not direct and is probably slow as hell. I'll give it a look though...

If you can't beat them, hire someone who can, who wears gloves...