dealing with max number of fields

[Guest]

For research and productivity purposes we have been entering and uploading pt
testing information in an Access db. It is not intended for use on the Web.
Recently we’ve joined a research Network that will utilize some of our data.
Instead of re-entering data, I would like to upload it, but have encountered
problems and don’t know how to handle it. The other sites enter directly into
the Network db and do not track the other info we do. Our Access db has over
300 fields, but because the data is not collected in the same table, or
queried at the same time, the 255 field limit was never an issue. But now
that each record we’re trying to upload will have 278 fields, it has become a
huge issue. And I don’t know how to get around it.

Is there a better db to use? I looked at File Maker – they have unlimited
fields, but they don’t have a combined date/time field – and most of the
source data we import into Access comes with date and time in the same field.
Oracle is too big & expensive for our purposes.

What can I use to export smaller amounts of data to re-join into one table?.
I don't have any experience with SQL or macros. Excel has the same issue
with number of fields as Access does. The final product ready for import
should be in .csv or .xml format. Expense is an issue.

Any suggestions?

(And please don't start the lectures I've seen on other posts about the max
number of fields, ie, normalization or 'we've never seen so many fields in
one db, so you must being doing something wrong'. The data points are unique
and many, there's a lot of things to track when patients come to the hopital
for testing.)

The bottom line our data is in Access and we need to import 278 fields in
one record to another computer.

 

[Brendan Reynolds]

If you're going to be supplying the data as a CSV or XML file, I'm not sure
why the limit on the number of fields in a JET table should be a problem for
you? You could, for example, open multiple recordsets to retrieve the data
from the JET tables, loop through them, and print the data to a text file.
It's a bit of a chore to write the necessary code to loop through the
recordsets and keep them in synch with one another, but there are no great
technical hurdles to overcome.

 

[Guest]

Well, there is one big technical hurdle to overcome - I have no idea what you
said!

What is a JET table? What type of loop are you taking about? I have never
used the SQL or macro writting end of Access an know nothing about it.

 

[Brendan Reynolds]

Ah, sorry, I see that you did say that you 'don't have any experience with
SQL or macros', I'm afraid I over-looked that.

I'm afraid I'm not sure what to tell you. The solution I had in mind does
require a working knowledge of both SQL and VBA.

 

[John Nurick]

If programming is ruled out, the simplest thing I can think of is to
export two delimited textfiles (let's call them T1.txt and T2.txt).

T1 contains the first n fields of the desired output, and T2 contains
the reminder. The files have the same number of lines, and line x in T1
contains fields from the same record as line x in T2.

Two files like that can be merged into one wider file by using the
"paste" utility, part of the Windows version of the Gnu utilities (free
download from http://unxutils.sourceforge.net ). A command line like
this

paste T1.txt T2.txt > Widefile.txt

will do the job for tab-delimited files; or

paste -d, T1.txt T2.txt >Widefile.csv

for CSV.

 

[Brendan Reynolds]

Sounds good to me, John, thanks. I hope it solves the original poster's
problem.