Code to Create a Candidate Key? 1

[Sware]

I have a Visual Basic application that uses the Visual FoxPro Driver to manage .DBF files (tables) created by Clipper. These tables are "free tables" and thus cannot have a Primary Key assigned to them. Can someone provide the code to create a Candidate Key? Thanks.

 

[Olaf Doschke]

use the table excllusive, select it, then simply:

index on <expression> tag <tagname> candidate

Creates a candidate index. But I did never do clipper, so I don't know if the origin of that table is clipper, if clipper can handle this kind of index.

Bye, Olaf.

 

[Sware]

Olaf,

Thanks for your response. I'm not sure I understand your input. The application in which I wish to create the Candidate Key on the .DBF table is a Visual Basic application, not a Clipper application. I'm using ADO in VB6, e.g. with a Connection, Recordset, etc.

 

[Olaf Doschke]

Well, that's the pure vfp code. If you don't have vfp to do it (creating an index is a once only thing), then you can do this via your AdoDB.Connection (you surely will have one)with the Execute method.

...
oConn.Open()
oConn.Execute("...")

' select an empty "workarea" (a vfp concept, I won't expand on this now)
oConn.Execute("select 0")
' open the table in that workarea exclusively (could fail!, make sure noone has the table open
oConn.Execute("use ([c:\some\path\to\the\table\table.dbf]) in 0 exclusive")
' create the candidate index
oConn.Execute("index on <expression> tag <tagname> candidate")
' close the table/workarea
oConn.Execute("use")

replace <expression> with eg a field you want to index and <tagname> with the name the index should have, eg. again the field name.

After you did that the index is there. Adding a record with a value already in an existing record then raises an error.

Bye, Olaf.

 

[bborissov]

Sware, just one you should keep in mind: After index that table with Candidate key you will not be able to open it with any CLIPPER tools or CLIPPER applications. That is because VFP will change the DBF header.
This is a warning just in case the same DBF must be used from your application AND from some old CLIPPER one. If this is not the case you are free


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

 

[Sware]

bborissov,

Wow - what an astute observer you are! I would have never thought of that. However, the good news is that the .DBF's for the Clipper and VB applications, and the applications themselves, will always be in different folders -- no chance for conflict. In fact, from an end user standpoint, he/she will uninstall the Clipper app after installing the VB app.

But, thanks forthe warning.

 

[Sware]

Olaf,

I can't get the VB compiler to accept your code. I get a syntax error on:

conn1.Execute ("SELECT 0")

 

[Olaf Doschke]

So it seems conn1 is your adodb.connection object. Can you show us the code you have o declare and set conn1?

Bye, Olaf.

 

[Olaf Doschke]

maybe just

conn1.Execute "Select 0"

without the paranthesis.

Bye, Olaf.

 

[Mike Lewis]

SWare,

May I jump in.

Am I right is saying that your need to create a candidate key is really a one-off requirement? You don't need to do this on a regular basis. You only want to create the key so that you can send your UPDATE commands, without the WHERE clause being generated.

If that's right, it's seem like you are spending a lot of time and effort creating the key via an ODBC EXEC call. Is there perhaps someone in your company who has a copy of Visual FoxPro, who could do the job much more easily from within that product? Or, is there someone you can ask to write a small, free-standing program to do the job?

It seems to me that, once the key has been created, you will be able to run your VB code against the table any time you want, without any futher hassle.

Sorry if I've misunderstood this point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

Mike,

Yes, it is a one-off (one-time) requirement, but it will be so for each end user after he/she converts their present Clipper files to revised content/fields of the VB files. The conversion code is in the Clipper application.

Clipper doesn't have a Candidate Key option, although it does have a Unique (Index) option which seems to have an equivalent effect for my purpose (or does it?). I was able to create the Unique Index in the Clipper application and copy the index file to the folder with the VB files. However, I assume I still have to INDEX ON in the VB application to activate the index and I can't come up with the code statement(s) to do that.

I'd rather create the Candidate Index in the VB application. To handle the one-off situation I can include a simple "Indicator File" with the converted (or skeleton startup) data files and use it to ensure that the Candidate Index is created only once.

Olaf,

When I use conn1.Execute "SELECT 0" I get a "Syntax Error or Access Violation" error.

 

[ilyad]

Hi Olaf,

INDEX ON command is not supported in OleDb, but you can write a program and use EXECSCRIPT instead. Yesterday in another similar thread (I don't see it now) Borislav already provided this solution thourhg lcStr variable.

The only mistake he made (and it dawned on me right after I turned off my PC) was in using single quote instead of a double.

Sware, if you still have this code, replace ' with " in lcStr declaration and I believe it should work.

 

[Mike Lewis]

SWare,

Clipper doesn't have a Candidate Key option, although it does have a Unique (Index) option which seems to have an equivalent effect for my purpose (or does it?).

No. Clipper's Unique key is not the same as FoxPro's candidate or primary key. A Unique key is one that contains a single instance of each distinct value. It doesn't mean the values are unique.

I assume I still have to INDEX ON in the VB application to activate the index and I can't come up with the code statement(s) to do that.

No, you don't need to do that. It's enough that the candidate key exists. You don't have to do anything in VB to activate it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[ilyad]

Of course, it may work only if you can get an exclusive access to VFP tables, you need to change.

 

[Mike Lewis]

Oops. I hit Submit Post too soon.

It's true that you don't have to activate the key within VB. But you do still need to tell it which field you have placed the index on.

You do that by making a setting in your RecordSet object (someone else might be able to tell you exactly how). You don't need to use the EXEC command to tell VFP to do it. (And, in any case, if you did, it wouldn't be INDEX ON, it would be SET ORDER TO, but that's not at all relevant).

Sorry, I hope I'm not confusing you more than you are.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

INDEX ON command is not supported in OleDb

Sorry, I didn't test my code thoroughly. There are two chapters in the VFP help and the ole db provider help on supported and unsupported language. INDEX / INDEX ON is in both lists!! There are several of those, the help is very buggy about that!

I doubt Execscript can do more commands and sneak around limitations, it may just be that executing several lines at once works better than one at a time.

Sware, I assumend you already have code to access the tables as is, please provide that code, so we can go from there, thanks!

Bye, Olaf.

 

[Sware]

Well, I'm more confused than ever! It seems there are two problems with tring to establish the Candidate Index with conn1.Execute -- syntax problems with the code and which functions to include in the code (e.g. INDEX ON or SET ORDER TO). There's also Mike's point about making a setting in the recordset object versus using the conn1.Execute command, which I have no idea how to do.

I tried the following with both OLEDB and ODBC connection strings for conn1, and with both INDEX ON and SET ORDER TO:

lcStr = "SELECT 0" & vbCrLf & _
"USE " & App.Path & "\GHM_V70.DBF" & "IN 0
EXCLUSIVE" & vbCrLf & _
"INDEX ON GS_ID TAG GS_ID CANDIDATE" & vbCrLf
conn1.Execute "EXECSCRIPT(" & lcStr & ")"

(Note that the line that begins with EXCLUSIVE is a continuation of the preceding line.) This resolves the syntax problems and results in the following for lcStr:

SELECT 0
USE C:\Program Files\Microsoft Visual Studio\VB98\
GHM_V70.DBF IN 0 EXCLUSIVE
INDEX ON GS_ID TAG GS_ID CANDIDATE

(The 3rd line is a continuation of the 2nd line.) When I run I get the following error on the conn1.Execute statement: 2147217900(80040e14):Function Name is missing

Again, I've run with both INDEX ON and SET ORDER TO, and with both ODBC and VFPOLEDB. I get the same error with all combinations.

The following are the connection strings for conn1. Again, the indented lines are continuations of the preceding line.

ODBC:
conn1.ConnectionString = "Provider=MSDASQL;Driver=
{Microsoft Visual FoxPro Driver}; Extended Properties=
"";;SourceDB=" & App.Path & "\;SourceType=DBF;
Deleted=Yes;"";"

OLEDB:
conn1.ConnectionString="Provider=vpfoledb;" & Data Source=
" & App.Path & "\GHM_V70.DBF;" & "Mode=ReadWrite|Share
Deny None;" & "Collating Sequence=MACHINE;" & "Password
=''"

For both of these connections:

Set conn1 = New ADODB.Connection
conn1.CursorLocation = adUseClient
conn1.Mode = adModeReadWrite

Set rs1 = New ADODB.Recordset
rs1.CursorLocation = adUseClient
rs1.Open "SELECT * FROM GHM_V70.DBF", conn1, adOpenStatic, adLockOptimistic

Both connections are used successfully in opening the recordset and using its fields to populate VB forms with data. The original problem is the "Statement Too Long" error on rs1.Update. The current problem involves trying to establish a Candidate Index to sole the original problem.

I guess we're back to Ground Zero. Help!!!

 

[Mike Lewis]

SWare,

There's also Mike's point about making a setting in the recordset object versus using the conn1.Execute command, which I have no idea how to do.

I have no idea either, but I feel sure it must be easy. It must also be a very common requirement.

Let's be clear what we are looking for.

Olaf is looking for a way of creating the required index from within your VB code. I'm taking a different path: assuming you can create the index externally (if it doesn't already exist), then you only need to tell your VB code the name of the index (or of the indexed field).

If you need to create the index in your VB code (which I understand would be because you have a large and dispersed user base, and so cannot find a way of doing it otherwise), then you need to stay with Olaf. I'm sorry I can't help with that.

But if there is a way of creating the index externally (perhaps via a stand-alone VFP program that your users can run once only), all that remains is to figure out how to tell you RecordSet object the name of the index field. Right now, I don't know how to do that, but I'm sure it must be easy to find out.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Sware]

Mike,

You've stated the "clear up" accurately. I am not able to create theindex externally - it (Candidate Index) apparently can't be created with Clipper and I don't have the FoxPro application. And, as you say, even if I did find a way to create it externally, there's still the issue of telling the recordset object the name of the index field.

So, I guess I may be stuck with handling the situation in VB code (i.e. stay with Olaf, et al). However, I understand that there may be another alternative - writing my own Update statement (with just the GS_ID field in the WHERE clause) instead of using ADO's rs1.Update.

 

[Mike Lewis]

I understand that there may be another alternative - writing my own Update statement (with just the GS_ID field in the WHERE clause) instead of using ADO's rs1.Update.

That sounds like a promising approach.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk