Linking to Tables in SQL vrs delimited Text files

[Guest]

I have requested that my company store my data on a SQL server with an ODBC
connection for me to link my Access database to. They want to give me a text
delimited file to link to. I am familiar with linking to SQL and find it
works well. Can you tell me if there are any advantages/disadvantages to
linking to a delimited text file. All my reports are populated using a master
query in Access so the ability to use views in SQL is not an issue. My main
concern is speed since we often will be querying up to 1 million records at a
time. Also, I will not be changing the data in anyway and the text file will
be updated with new records every 10 minutes from another outside source (SAP)

 

[david epsom dot com dot au]

There is no standard for delimited text files, and every
implementation is different. It is common to find problems
with text files including misplaced end-of-line characters
and extra embedded delimiters.



Access has trouble where a column contains both numbers and text.
Since you will only be reading the data, you can overcome this problem by
forcing all of the columns to be read as text.

 

[Guest]

I may have misstated the situation somewhat. I will be creating calculated
fields from some of the linked fields, so I am unable to define all columns
as text.

 

[John Vinson]

I have requested that my company store my data on a SQL server with an ODBC
connection for me to link my Access database to. They want to give me a text
delimited file to link to. I am familiar with linking to SQL and find it
works well. Can you tell me if there are any advantages/disadvantages to
linking to a delimited text file. All my reports are populated using a master
query in Access so the ability to use views in SQL is not an issue. My main
concern is speed since we often will be querying up to 1 million records at a
time. Also, I will not be changing the data in anyway and the text file will
be updated with new records every 10 minutes from another outside source (SAP)

Ouch...

SQL has indexes, and is very, very good at moving data around.

Text files do not, and you must do all the heavy lifting yourself. I'd
be really surprised if a query based on a million-row text file
(especially if the query does any complex calculations) could even
FINISH in ten minutes; it would at the very least cause conflicts
since Access would need exclusive access to the file while the report
is being generated, while SAP may need to grab the file and change it
during that time.

I would emphatically NOT recommend attempting to use a text file for
this purpose. SAP *is* a database application and I see no benefit
whatsoever to throwing that capability away!

John W. Vinson[MVP]

 

[david epsom dot com dot au]

Then you may need to convert the text to numbers or dates
AFTER it has been linked into Access. This will not be a major
additional problem, but is an extra thing to keep in mind.

Or you can just reject all data with problems.

Since you are dealing with a text file, everything
starts as text, and it is easiest if it can be left
that way. It is, of course, an argument for using
a database table instead of a text file.

I would imagine that with 1 million records, you
are going to have at least 20MB files. Regardless
of how you do this, 20MB is going to take a while
to read from disk. Yes, a binary form is more
compact, and for numeric data will almost certainly
be faster. Have you considered using dBase files
for the data interchange, instead of text files?

(david)