Solved

How can I update data in a linked text file with Access 2013

Posted on 2013-11-06
10
677 Views
Last Modified: 2013-12-04
What is an alternative file format to dbf?  Access 2013 no longer supports dbf files.  I am looking for an alternative format, other than Access tables.  We like the data to be external to Access, this way we can link to over 100 tables without blowing out the 2 GB max size of a database.  I tried .txt and .csv, but the problem is we cannot update data within the files using Access.  We can append records, but updates and deletes are not allowed.  I know we can maintain updateable tables in Access then export to txt for reporting, but I just wonder if there is another way.
0
Comment
Question by:upsfa
  • 6
  • 3
10 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39628734
Access can link to multiple backend databases, so could you store some tables in BackendA, and others in BackendB, and others in BackendC (and so forth), and then simply link all those backends to the same Frontend?

Or move to something like SQL Server, MySQL etc. Access can link to any ODBC-compliant database.
0
 
LVL 1

Author Comment

by:upsfa
ID: 39628769
Yea, trying to avoid a large number of backends, but it may be unavoidable.  SQL is where we'd like to go, but that's not going to be a quick turn-around here.  We also have the issue that we archived 100+ dbf files each month for 10+ years.  Once we have Access 2013, we will still need a way to read that data.  Thinking about getting a copy of dbase just to read/convert the archives.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39630847
An old copy of Access 97 (or 2000?) could read the archived dBase files.

/gustav
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:upsfa
ID: 39630970
Yea, even 2010 would be fine, but our whole organization is moving to 2013, I have no control over it.  I was able to get an exception for our group, but we need to be off 2010 by January.  I will try to keep one machine with 2010, but eventually that will go once MS drops support of 2010.
0
 
LVL 1

Author Comment

by:upsfa
ID: 39695565
I've requested that this question be deleted for the following reason:

The need for the solution has passed.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39694713
So what did you do?

/gustav
0
 
LVL 1

Author Comment

by:upsfa
ID: 39695531
When weighing our options for the conversion away from dbf files during the upcoming migration to Access 2013, we looked at using txt files as an alternative to dbf files.  We have now determined that converting dbf files to text files would not support our needs.  While MS Access allows users to link to text files and supports running queries and reports, it does not allow users to edit data within the tables.  In addition, text files cannot be shared by multiple users concurrently, whereas dbf files and Access tables can.

We have decided that active data tables should be stored in MS Access databases version 2007 – 2013 (with the accdb file extension).  Access databases have a two gigabytes size limit.  Not all of our tables will fit into one Access database, so we will be creating multiple back-end databases to store the tables.  Initially, we thought we would have several databases hold tables that are closely related, but now will be using one back-end Access database for each table.  This is a large number of accdb files, but this configuration supports database maintenance and data recovery more efficiently.  There will be a master database that will link to each of the individual tables within each database.  This database is the unified back-end that all existing databases will be linked to for their source data.
0
 
LVL 1

Author Comment

by:upsfa
ID: 39695552
I accidentally, flagged the wrong question for delete.
0
 
LVL 1

Author Closing Comment

by:upsfa
ID: 39695566
Not that it wasn't one of our options initially, but it was what we ultimately chose to do, so thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39695653
That sounds as a well thought out option. Using dbf files would no matter what lead to a dead end.
Thanks for the feedback.

/gustav
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now