Can an access form write to a dbf file?

[Ted]

My employer purchased an inexpensive database built on dbf files (in
conjuction with .cdx files). I'm decent with access and have been using it
to collect and track data in preparation for populating the dbf files. Is
there a way for access to write to those dbfs. I've found fragments of code
for ADODB but never a complete example.

Many thanks in advance.


__________ Information from ESET Smart Security, version of virus signature database 4359 (20090822) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Albert D. Kallal]

In general, you should be able to link to those dbf files.

Once done, then you don't need worried things like ADDOB etc, but you just
bind the external tables to a form, or even build reports based on those
tables.

the code/process you have now that updates tables should thus work the SAME
on those external tables.

So, you just need to get linked tables working.

here is a repost on connecting to dbf files.

You also leave out what version of access. it not clear if those .dbf tables
are dbaseII, dbaseIII, dbaseIV files or are in fact FoxPro files. With cdx
indexes, it does seem you have FoxPro files. You should determine this
issue. Assuming these are FoxPro files, then the following should at least
give you the ability to read/view the data in ms-access:


(the ability to update those files will depend on how the original
application is setup and if things like indexing etc. uses custom code.

You likely should check with the vendor of the software as to what version
of dbase or FoxPro those data files are, and further check if the VPF
drivers can be used to update those files.

Here some info on how to use these files from ms-access:

===

Originally posted by Cindy Winegarden Microsoft Visual FoxPro MVP:

Start with the latest ODBC driver for FoxPro and Visual FoxPro, available
from
http://msdn.microsoft.com/vfoxpro/downloads/addons/odbc.asp. Download and
install it.

(the above seems wrong now..try the following <nb: Albert K.>


http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx


You will need to determine whether you have FoxPro "free" tables or a
"database container" (contains metadata about the tables themselves).

To do this, navigate to your data directory and look for the presence of a
DBC
file. If its there you have a "database" otherwise you have free tables.

Now open up the ODBC dialog. In WinXP its Start | Administrative Tools |
Data Sources (ODBC). Choose either the User DSN or the System DSN. (The
difference lies in whether you want other people to be able to see it if
they use your machine.)

Click Add... Scroll down to the Microsoft Visual FoxPro Driver. Scroll over
to verify that its version 6.01.8629.01. Click Finish.

Now you're at the ODBC Visual FoxPr Setup dialog. In the first box (Data
Source Name) enter a friendly name that you will use to recognize this data
source - My FoxPro Data Source. You can add more text in the description
box.

Below are two options. Choose Visual FoxPro database (.DBC) or Free Table
Directory - whichever you have determined is correct.

Browse to locate your directory, or your specific DBC file.

You can click the Options button for more choices.
The defaults should be
ok. "Exclusive" refers to whether you want to have exclusive access to the
data while you're using it. "Null" allows you to enter Null values or not.
Older FoxPro tables do not allow Nulls. "Deleted" means to hide deleted
records. This ones important if you are working with primary keys - more in
a minute. "Fetch data in background" will allow you to see the first few
lines of a large table while the rest is being retrieved. You can also
indicate a collating sequence if you're using a non-English alphabet.

Click OK and you're done.

Now, about deleted records. When a record is deleted in a FoxPro or Visual
FoxPro table its merely marked as deleted and filtered out, but is still
physically present in the table. If you have a unique index and you delete
the record with a key value of 123 you can not enter another record with
this value, even though you cant see the record. To get rid of records
entirely, you must issue a PACK command. However, the best rule to follow is
to not reuse primary keys.

 

[Ted]

When I try that I get a message that 'File is not in expected format.'. I
tried this on both 2003 and 2007 machines with no luck. I've tried adodb
but the closest I get there is that 'No installable ISAM could be found'.
Maybe the files aren't really dbf like the trainer said. It's name and
extension is invitems.dbf

Many thanks for your help.

In general, you should be able to link to those dbf files.

Once done, then you don't need worried things like ADDOB etc, but you just
bind the external tables to a form, or even build reports based on those
tables.

the code/process you have now that updates tables should thus work the
SAME on those external tables.

So, you just need to get linked tables working.

here is a repost on connecting to dbf files.

You also leave out what version of access. it not clear if those .dbf
tables are dbaseII, dbaseIII, dbaseIV files or are in fact FoxPro files.
With cdx indexes, it does seem you have FoxPro files. You should determine
this issue. Assuming these are FoxPro files, then the following should at
least give you the ability to read/view the data in ms-access:


(the ability to update those files will depend on how the original
application is setup and if things like indexing etc. uses custom code.

You likely should check with the vendor of the software as to what version
of dbase or FoxPro those data files are, and further check if the VPF
drivers can be used to update those files.

Here some info on how to use these files from ms-access:

===

Originally posted by Cindy Winegarden Microsoft Visual FoxPro MVP:

Start with the latest ODBC driver for FoxPro and Visual FoxPro, available
from
http://msdn.microsoft.com/vfoxpro/downloads/addons/odbc.asp. Download and
install it.

(the above seems wrong now..try the following <nb: Albert K.>


http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx


You will need to determine whether you have FoxPro "free" tables or a
"database container" (contains metadata about the tables themselves).

To do this, navigate to your data directory and look for the presence of a
DBC
file. If its there you have a "database" otherwise you have free tables.

Now open up the ODBC dialog. In WinXP its Start | Administrative Tools |
Data Sources (ODBC). Choose either the User DSN or the System DSN. (The
difference lies in whether you want other people to be able to see it if
they use your machine.)

Click Add... Scroll down to the Microsoft Visual FoxPro Driver. Scroll
over
to verify that its version 6.01.8629.01. Click Finish.

Now you're at the ODBC Visual FoxPr Setup dialog. In the first box (Data
Source Name) enter a friendly name that you will use to recognize this
data
source - My FoxPro Data Source. You can add more text in the description
box.

Below are two options. Choose Visual FoxPro database (.DBC) or Free Table
Directory - whichever you have determined is correct.

Browse to locate your directory, or your specific DBC file.

You can click the Options button for more choices.
The defaults should be
ok. "Exclusive" refers to whether you want to have exclusive access to the
data while you're using it. "Null" allows you to enter Null values or not.
Older FoxPro tables do not allow Nulls. "Deleted" means to hide deleted
records. This ones important if you are working with primary keys - more
in
a minute. "Fetch data in background" will allow you to see the first few
lines of a large table while the rest is being retrieved. You can also
indicate a collating sequence if you're using a non-English alphabet.

Click OK and you're done.

Now, about deleted records. When a record is deleted in a FoxPro or Visual
FoxPro table its merely marked as deleted and filtered out, but is still
physically present in the table. If you have a unique index and you delete
the record with a key value of 123 you can not enter another record with
this value, even though you cant see the record. To get rid of records
entirely, you must issue a PACK command. However, the best rule to follow
is
to not reuse primary keys.


__________ Information from ESET Smart Security, version of virus
signature database 4360 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4360 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[MikeB]

Do you mean to interact with the dbf files as an ongoing process or do you
wish to bring the data into Access?

 

[Ted]

I would like to use an access form to populate the dbf. It is so easy to
proof and correct data in access as opposed to the dbf.
Many thanks in advance

Do you mean to interact with the dbf files as an ongoing process or do you
wish to bring the data into Access?






__________ Information from ESET Smart Security, version of virus
signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Ted]

No, I'm stuck with the dbf program. I can export and link the dbf data but
that doesn't help me populate the dbf.

Many thanks for your help

If these dbf files will be used by only the Access program and not some
old
dBase app then could just import the dbf tables into Access project.

I would like to use an access form to populate the dbf. It is so easy to
proof and correct data in access as opposed to the dbf.
Many thanks in advance

Do you mean to interact with the dbf files as an ongoing process or do
you
wish to bring the data into Access?

[quoted text clipped - 20 lines]

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

--
Message posted via AccessMonster.com



__________ Information from ESET Smart Security, version of virus
signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[MikeB]

I have another DBMS (RBase) that I exported my ContactList table as DBaseIII
to a test directory. I created a new Access07 database (2003 Compatible)
and linked to the DbaseIII file and created a form based on the linked
table.

It is fully functional with editing, adding and deleting. This took all of
5 minutes.

I did not use ODBC for linking, just the native link provided by Access.

 

[Albert D. Kallal]

When I try that I get a message that 'File is not in expected format.'.

Are you saying you get the above when you use the visual FoxPro drivers?

 

[Ted]

Your question solved the problem. I'd had not tried drivers other than the
ones distributed with 2003 and 2007. I downloaded the visual Fox Pro
Drivers and created an ODBC link that does read and write.

Grateful thanks to you and everyone who took a moment to help me. Thank you
all.

Are you saying you get the above when you use the visual FoxPro drivers?

Are you saying you get the above when you use the visual FoxPro drivers?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)



__________ Information from ESET Smart Security, version of virus
signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Ted]

I appreciate your help. I think ignorance on my part was the problem. It
was suggested that I might need the visual foxpro drivers which enabled an
editable ODBC link. I don't know what I was doing wrong but I was never
able to create an ordinary link. Maybe it was because my office
installation was an express setup instead of the custom installation where
you can install a complete range of drivers.

Again I am grateful for your help.

I have another DBMS (RBase) that I exported my ContactList table as
DBaseIII to a test directory. I created a new Access07 database (2003
Compatible) and linked to the DbaseIII file and created a form based on the
linked table.

It is fully functional with editing, adding and deleting. This took all
of 5 minutes.

I did not use ODBC for linking, just the native link provided by Access.








__________ Information from ESET Smart Security, version of virus
signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4361 (20090823) __________

The message was checked by ESET Smart Security.

http://www.eset.com