How to change read-only VFP ODBC to all access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I'm using Access 2003 to read/(write) to FoxPro free files. I don't know
the FoxPro version, but it is relatively new. I installed the latest ODBC
driver referred to in other posts, but now can only read, not write.

Did I do something wrong in the installation? Is there a way to get
read/write/delete?

Thanks in advance!

Greg
 
Hi Greg,

Be sure you have the latest FoxPro and Visual FoxPro ODBC driver,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates. It is only
compatible with VFP6 (and earlier) formats.

Over time (VFP7, 8, and 9) new data features have been added to Visual
FoxPro that are not compatible with ODBC. However, tables created with these
more recent versions that don't have any of the new data features will be
ODBC-compatible. Consequently you will want to confirm which version of
FoxPro the tables were created with, and whether they have any of the new
data features or not.

The FoxPro and Visual FoxPro OLE DB data provider is compatible with all
versions of FoxPro. It's downloadable from the address above.
 
Thank you, Cindy!

I had opportunity over the weekend to make iterative connections, and found
that when I define "unique" fields upon connection, I am able to change the
data. However, if I decline to define those unique fields when prompted, I
cannot change the data. Also, if I select unique fields which do not mirror
the "key" as defined in the FoxPro database, I get a strange view of the
records - including multiples of the same record.

I appreciate your knowledge and help. It was from your earlier posts that I
knew to apply the latest drivers. Thanks!

Greg
 
Hi Greg,

I'm not sure what you mean by "define "unique" fields upon connection." Can
you explain? Also when you "select unique fields which do not mirror the
"key"" what does your SQL look like? How do you know the fields (field
combinations) are unique in the data?
 
Sorry, Cindy, didn't mean to be obtuse.

When I connect to the FoxPro database, I select [Link to tables], then in
the resulting dialog box I choose files of type ODBC. This brings up the
ODBC drivers, from which I select Machine Sources and the FoxPro Tables
driver.

This driver then asks for the path to my FoxPro tables, which I supply.
When I click [Finish], the tables are shown, and I am allowed to select which
table(s) to link. After the selection, a dialog box pops up for each and
every table I selected, asking me to specify which fields make the records in
this table unique.

At that point, if I -cancel- the selection process, I still get the table
link in Access but can only read the records, not write to them. However, if
I go ahead and select all fields which make up the original FoxPro key for
that table, I can both read and write and the records present themselves as
expected.

If I do not specify the key fields, the whole key fields, and nothing but
the key fields in the "unique" dialog box, the records are read/write, but
can present multiples or none (for some keys) when viewed through the link.

Am I getting clearer, or just muddying the waters?

Greg
 
Pontificateur said:
When I connect to the FoxPro database, I select [Link to tables], then in
the resulting dialog box I choose files of type ODBC. This brings up the
ODBC drivers, from which I select Machine Sources and the FoxPro Tables
driver. .....
Click to expand...

Good so far, but I'd also try using Administrative Tools to create an ODBC
data source that points specifically to the directory where your tables are,
and I'd try both User and System DSNs.
At that point, if I -cancel- the selection process, I still get the table
link in Access but can only read the records, not write to them.
Click to expand...

That would be because ODBC might not have a good way to know which row to
update. However, in my experience with ODBC data sources that point directly
to the table directory and are saved as User or System data sources the
tables open just as if they were Access tables and you can change anything
you want by editing the cells. Access/ODBC must have some sort of PK system
underneath to know which row you're working with.
However, if
I go ahead and select all fields which make up the original FoxPro key for
that table, I can both read and write and the records present themselves
as
expected.
Click to expand...

Makes sense.
If I do not specify the key fields, the whole key fields, and nothing but
the key fields in the "unique" dialog box,
Click to expand...

Um, why would you want to specify it any other way?

Visual FoxPro tables have indexes that can be specified as unique and that
enforce non-duplication of keys. Such an index can be for a single field
only (InvoiceDetailID) or a combination of fields such as Str(InvoiceNumber)
+ Str(LineItemNumber) for an invoice details table.
the records are read/write, but
can present multiples or none (for some keys) when viewed through the
link.
Click to expand...

I'm not surprised at your unpredictable results. When a Fox index is
specified as Primary or Candidate then the Fox data engine would expect to
use these indexes when identifying the row you're on via ODBC.

Is there a problem with specifying the unique fields? Do you know what the
Fox index expressions are, or are you having trouble guessing them? If you
really don't know what they are and the tables aren't big or confidential
you could zip them up and send them to me and I'll take a look.
 
Cindy:
...in my experience with ODBC data sources that point directly
to the table directory and are saved as User or System data sources the
tables open just as if they were Access tables and you can change anything
you want by editing the cells.
Click to expand...

Excellent suggestion, and one I plan to try tomorrow! I did not consider
that creating an ODBC source specific to my purpose - a System or User source
- could possibly prevent my having to manually specify the key fields for
every table.
Um, why would you want to specify it any other way?
Click to expand...

Agreed. In my digging I came across the symptoms stemming from choosing
partial key fields or no key fields and wanted to pass along those results,
also.
Is there a problem with specifying the unique fields? Do you know what the
Fox index expressions are, or are you having trouble guessing them? If you
really don't know what they are and the tables aren't big or confidential
you could zip them up and send them to me and I'll take a look.
Click to expand...

Thank you for the offer. This particular application includes a "DDFile"
table listing the appropriate key fields for each table. There are close to
200 tables, and I did not want to manually select key fields for every one.
(Bound to miss at least one!) That's why I hope your suggestion to create a
new ODBC helps, otherwise I'll be trying to write code to create the links
based on that DDFile table!

Greg

+++++++++++++++++++++++++++++++++++++++++++


Cindy Winegarden said:
Pontificateur said:
When I connect to the FoxPro database, I select [Link to tables], then in
the resulting dialog box I choose files of type ODBC. This brings up the
ODBC drivers, from which I select Machine Sources and the FoxPro Tables
driver. .....
Click to expand...

Good so far, but I'd also try using Administrative Tools to create an ODBC
data source that points specifically to the directory where your tables are,
and I'd try both User and System DSNs.
At that point, if I -cancel- the selection process, I still get the table
link in Access but can only read the records, not write to them.
Click to expand...

That would be because ODBC might not have a good way to know which row to
update. However, in my experience with ODBC data sources that point directly
to the table directory and are saved as User or System data sources the
tables open just as if they were Access tables and you can change anything
you want by editing the cells. Access/ODBC must have some sort of PK system
underneath to know which row you're working with.
However, if
I go ahead and select all fields which make up the original FoxPro key for
that table, I can both read and write and the records present themselves
as
expected.
Click to expand...

Makes sense.
If I do not specify the key fields, the whole key fields, and nothing but
the key fields in the "unique" dialog box,
Click to expand...

Um, why would you want to specify it any other way?

Visual FoxPro tables have indexes that can be specified as unique and that
enforce non-duplication of keys. Such an index can be for a single field
only (InvoiceDetailID) or a combination of fields such as Str(InvoiceNumber)
+ Str(LineItemNumber) for an invoice details table.
the records are read/write, but
can present multiples or none (for some keys) when viewed through the
link.
Click to expand...

I'm not surprised at your unpredictable results. When a Fox index is
specified as Primary or Candidate then the Fox data engine would expect to
use these indexes when identifying the row you're on via ODBC.

Is there a problem with specifying the unique fields? Do you know what the
Fox index expressions are, or are you having trouble guessing them? If you
really don't know what they are and the tables aren't big or confidential
you could zip them up and send them to me and I'll take a look.
Click to expand...
 
Excellent suggestion, and one I plan to try tomorrow! I did not consider
that creating an ODBC source specific to my purpose - a System or User
source
- could possibly prevent my having to manually specify the key fields for
every table.
Click to expand...

You'll still need to specify the key fields when you link the data.
...This particular application includes a "DDFile"
table listing the appropriate key fields for each table. There are close
to
200 tables, and I did not want to manually select key fields for every
one.
(Bound to miss at least one!) That's why I hope your suggestion to create
a
new ODBC helps, otherwise I'll be trying to write code to create the links
based on that DDFile table!
Click to expand...

That sounds like the way to go.
 
Back
Top