Help w/ODBC Linked Tables(VFP Local Views)

[EnS]

I am using VFP 6.0 Database as a repository housing one table of current data. I have created several local views to present different aspects of the data to the users which they access through MSAccess linked tables using an ODBC connection. The problem I'm having is that when my users create Access Queries using these linked "tables" and they pull in only certain fields, the resulting recordset does not contain the data associated with the fields that they pulled. Instead, it seems to give them data in the order of the underlying local view.

Can someone explain to me why this happens or point me to a good source of explanation? And, is there a workaround without having to create other VFP tables?

Thanks for your advice in advance

ERM

 

[JRB-Bldr]

" they access through MSAccess linked tables using an ODBC connection"

"when my users create Access Queries using these linked "tables" and they pull in only certain fields"

It sounds as though this might be best answered in one of the MS Access Forums
Microsoft: Access Queries and JET SQL Forum
forum701
Microsoft: Access Tables and Relationships Forum
forum700

Good Luck,
JRB-Bldr

 

[Mike Lewis]

EnS,

You said, Instead, it seems to give them data in the order of the underlying local view.

Are you saying that the order of the data is the problem? If so, the ORDER BY clause in the view definition should determine the order in which the records are retrieved. Is that what you are seeing?

That said, I think JRB-Bldr is right to suggest that you ask this in an Access-related forum.

Mike




Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[EnS]

Thanks for the reply guys...

Mike,
It does have to do with the order, but not with the order of the data records, but more with the order of the fields. I.e. local view: field1, field2, field3, field4, field5, field6, etc. with associative date in the record. When pulled as a query (field1, field2, field4, and field6) returns the record with data associated with field1, field2, field3, and field4. I wasn't sure where to post my question because I wasn't sure if my problem is with my local view (VFP), odbc, or Access.

Again, thanks for you responses and I’ll try submitted my post to the Access forum.


ERM

 

[Mike Lewis]

EnS,

I assume the order of the fields in the local views does match the order in the underlying table? The fields in the view will appear in whatever order they were specified in the SQL SELECT.

That said, in general it is not a good idea to rely on the order of the fields within a record. Is there any reason why you can't just access the fields by name instead? In other words, why is the order of fields an issue?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[EnS]

"...The fields in the view will appear in whatever order they were specified in the SQL SELECT..."

Absolutely, and I think that is what's throwing me for a loop...

Background...

FoxPro
data_table
cycle date loan_no cust_name loan_amnt prop_add active
06/18/2004 0123456789 Doe, John $1,000,000.00 1234 This St. True
06/18/2004 0123456789 Doe, John $1,000,000.00 1234 This St. False

FoxPro Local View
data_view
sql statement:
SELECT * FROM data_table WHERE active = True
results
cycle date loan_no cust_name loan_amnt prop_add active
06/18/2004 0123456789 Doe, John $1,000,000.00 1234 This St. True

Access
ODBC Linked Table: data_view
Users Query
sql statement:
SELECT loan_no, cust_name, prop_add, loan_amnt FROM data_view;
results
loan_no cust_name prop_add loan_amnt
06/18/2004 0123456789 Doe, John $1,000,000.00

Could this be caused by the ODBC (VFP Driver v6.01)? Or is it that Access can't use the view as a linked table for some reason.


ERM

 

[Mike Lewis]

EnS,

Could this be caused by the ODBC (VFP Driver v6.01)? Or is it that Access can't use the view as a linked table for some reason.

Don't know. I can only say that I have never had any trouble in accessing VFP local views from other ODBC clients, although I never really thought about the order of the fields.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[EnS]

Thanks Mike.

I appreciate the help. Have a good one.

ERM