Connecting to an ODBC table

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.

Re: Connecting to an ODBC table

On Mon, 05 Nov 2007 15:35:13 -0800, Salad wrote:

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.

Re: Connecting to an ODBC table

Tom van Stiphout wrote:
> On Mon, 05 Nov 2007 15:35:13 -0800, Salad wrote:
>
> 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.

Re: Connecting to an ODBC table

On Tue, 06 Nov 2007 05:45:46 -0800, Salad wrote:

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.




>
>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.

Re: Connecting to an ODBC table

Salad wrote in news:13iva5he8ttvj15@corp.supernews.com:

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

I'm trying to think of some set of circumstances under which I would create
any table without a primary index.

But I can't.

--
lyle fairfield

Re: Connecting to an ODBC table

lyle fairfield wrote:
> Salad wrote in news:13iva5he8ttvj15@corp.supernews.com:
>
>
>>Is it the rule that any table that you want to use via ODBC must be a
>>UNIQUE record?
>
>
> I'm trying to think of some set of circumstances under which I would create
> any table without a primary index.
>
> But I can't.
>
LOL.

Re: Connecting to an ODBC table

On Wed, 07 Nov 2007 03:48:11 GMT, lyle fairfield
wrote:

>Salad wrote in news:13iva5he8ttvj15@corp.supernews.com:
>
>> Is it the rule that any table that you want to use via ODBC must be a
>> UNIQUE record?
>
>I'm trying to think of some set of circumstances under which I would create
>any table without a primary index.
>
>But I can't.

It's common when the "table" is really a SQL "View".

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!