Connecting to an ODBC table

[Salad]

Is it the rule that any table that you want to use via ODBC must be a
UNIQUE record?

And if you want any speed associated with the table it should be indexed?

I was having some difficulty linking to some FoxPro files last week. I
can do so on 1 table now because it has a unique field...but has no
index. The table opens like its on downers, staggering under the brunt
of displaying 1000 records.

The other table doesn't have a unique field and after a couple of
minutes of deciding whether or not it should die or live, it gives up
the ghost and dies.

My problem might be due to the ODBC FoxPro drivers. I'd hate to think
SQL Server suffers the same speed degradation I have with FoxPro tables.

[Tom van Stiphout]

On Mon, 05 Nov 2007 15:35:13 -0800, Salad <oi*@vinegar.comwrote:

Comments in-line.

>Is it the rule that any table that you want to use via ODBC must be a
UNIQUE record?

No, but without uniqueness your data will be readonly. Uniqueness
(best expressed with a PK) is a really good idea from a db design
standpoint.

>
And if you want any speed associated with the table it should be indexed?

Yes.

>
I was having some difficulty linking to some FoxPro files last week. I
can do so on 1 table now because it has a unique field...but has no
index. The table opens like its on downers, staggering under the brunt
of displaying 1000 records.

The other table doesn't have a unique field and after a couple of
minutes of deciding whether or not it should die or live, it gives up
the ghost and dies.

My problem might be due to the ODBC FoxPro drivers. I'd hate to think
SQL Server suffers the same speed degradation I have with FoxPro tables.

Try it. SQL Server express edition is a free download. Import a few
tables. Add a PK for each. Connect to the tables. Who-haaa.

There may be more than one flavor of FoxPro drivers on your machine.
Try them all.

-Tom.

[Salad]

Tom van Stiphout wrote:

On Mon, 05 Nov 2007 15:35:13 -0800, Salad <oi*@vinegar.comwrote:

Comments in-line.

>>Is it the rule that any table that you want to use via ODBC must be a
UNIQUE record?

No, but without uniqueness your data will be readonly. Uniqueness
(best expressed with a PK) is a really good idea from a db design
standpoint.

Yeah, I selected all fields in the hopes that made them unique.

>

>>And if you want any speed associated with the table it should be indexed?

Yes.

Thanks for validating my concept that an index would be nice.

>

>>I was having some difficulty linking to some FoxPro files last week. I
can do so on 1 table now because it has a unique field...but has no
index. The table opens like its on downers, staggering under the brunt
of displaying 1000 records.

The other table doesn't have a unique field and after a couple of
minutes of deciding whether or not it should die or live, it gives up
the ghost and dies.

My problem might be due to the ODBC FoxPro drivers. I'd hate to think
SQL Server suffers the same speed degradation I have with FoxPro tables.

Try it. SQL Server express edition is a free download. Import a few
tables. Add a PK for each. Connect to the tables. Who-haaa.

There may be more than one flavor of FoxPro drivers on your machine.
Try them all.

There are. However, they all say driver version 6.01.8629.01 and from
posts on Google in the past that's the version of the driver everyone
mentions.

I'm not sure why the person that created these tables never created a
unique id...simply by creating a field and replacing it's value with the
recno() value and why they were supplied w/o an index file. If I had
had input on the process...but I didn't.

>
-Tom.

[Tom van Stiphout]

On Tue, 06 Nov 2007 05:45:46 -0800, Salad <oi*@vinegar.comwrote:

It may not be too late. "Hey boss, you can either have me fix
(rewrite?) that ol' FoxPro app, or we'll be limping along"

Perhaps adding a field would suffice. Similar to adding a surrogate PK
over a new AutoNumber field in an Access table. Chances are that won't
break the FoxPro app, and will greatly benefit your Access app.

-Tom.

<clip>

>
I'm not sure why the person that created these tables never created a
unique id...simply by creating a field and replacing it's value with the
recno() value and why they were supplied w/o an index file. If I had
had input on the process...but I didn't.

>>
-Tom.