Sinner said:
Hi,
I was wondering if this can be done.
I will place the link name of delimited text file in a table1 in filed
name like "c:\abc\test1.txt", "c:\abc\test2.txt", "c:\abc\test3.txt"
and so on.
The delimiting character is "|".
The next column is Select yes/no in which I have option to tick the
file that I want to select.
Now I want a query to include the file/files that I select in table1.
It can be single file or multiple file.
Since the text files change frequently, I wish not to import them or
link them.
Instead I want to have a selection query of my choice.
I hope I have explained it neatly.
Looking forward for a solution.
That sounds like a comprehensive end-to-end task that might be more
suitable for a final exam in an Access course. Without getting bogged
down in details, here's one possible (air) plan.
Do it using VBA code with some SQL thrown in.
Perhaps have a listbox to select the names of the text files. You can
use the Dir() function to get all the files from a directory or use a
file picker to add files one by one from different directories to a
table used to populate the listbox. Extra credit is available for
having an option to pick the directory.
For each checked table name in the listbox, use VBA code to ensure that
a link to the table exists. Perhaps include extra information in the
connection string, such as an IMEX value, so that the information gets
read correctly. It might be wise to change characters in the path, such
as : or \, to something friendlier for an Access table name when
assigning a name to the linked file in VBA code.
I assume that the delimited text files have the field names on the first
line so that you don't end up with something like Field1, Field2, etc.
Next you have to get a list of all the distinct field names from all the
linked tables chosen in the listbox. Use the TableDef for each file
name chosen in the checkbox (using the same character substitutions) to
get the names of all the fields in all the tables. Come up with a list
of distinct field names. That can be done by running a SELECT DISTINCT
query on a table containing all the field names you gathered or by
adding only field names that aren't in a string array to that string array.
Make sure that the number of distinct field names is <= 255. Create an
output table with those field names, checking to make sure that they are
all valid field names. That table can be created entirely in VBA or by
creating a DDL query and executing it. Be sure to run a DDL query to
DROP the table if it already exists. Note that the table must be
created each time instead of being emptied and refilled because, in
general, it is not known in advance which fields are being used. Maybe
make all the fields text fields.
Then loop through all the checked file names again to get the data in
each table. Perhaps create dynamic append queries that use the input
table name, the output table name and the field names in the current
table to create the SQL string. It might be wise to surround each field
name with square brackets when forming the SQL string to give the query
a better chance of running if the field names can contain spaces,
reserved words or special characters. Then you can run a .Execute on a
DAO.Database object to run each append query, making sure that the
append has completed before moving on to the next table in the list.
That technique assumes that if any two tables contain the same field
name, then that information goes under the same field name in the output
table.
Note: I haven't tried linking to a delimited text file lately, but doing
so manually might produce a connection string that gives you a hint
about what connection strings to create in code. If you need any
details, ask, and perhaps someone here can supply more information.
James A. Fortune
(e-mail address removed)
