B
_Bigred
I have a table with the names of approx 3500 records.
I want to have a query pull a random record from this table
table: PowerTabDirectory
Field: Item (this is a autonumber)
Field: FileName (this is the name of the files)
I tried a query with the item & fileName in it, and added a field with the
below in it:
RandomRecords: Rnd(IsNull(PowerTabDirectory.ItemID)*0+1)
But when I run the query it prompts me for a parameter for the ID, if i hit
a number say 7 it will return will pull up the table with a RandomRecords
field on it and will have a value like 0.75421243 etc....
Ultimately I was trying to create a database of many (15,000) powertab
files, that would allow me to create a file list and then open a random file
with the query.
I created the FileList using I: DIR *.ptb /S/B>C:\FileList.txt
then importing it into access, then i was trying to have a module in the db
and a query make it a clickable link to the file. (IT DIDN'T work)
Is there a good way to pull random records using a query, and is there a
method to make the FileList (which has the complete file path) a clickable
link within access - and then store my powertab (.ptb) files in the same
directory as the db?
(MY ORIGINAL METHODS used were from a previous reply I got to another NG
post) I will paste it below my name (below)
TIA,
_Bigred
|
|
V
If these are unstructured or loosely structured text files, and you want
in effect to index them, a relational database such as Access is not the
most convenient tool for the job. Specialist "text database" software
such as Bekon Idealist (which I've been using for more than 12 years),
InfoSelect or AskSam will make it much easier to do powerful searches.
Or you could leave the files as they are, and learn to use text-file
search tools such as 'grep' to quickly find the one(s) you want. 'grep'
and other classic text file tools are a free download from
http://unxutils.sourceforge.net/
But if you do need to use Access, here's how to get started:
Assuming that all the files are in a convenient and sensible structure
of folders (e.g. a top-level folder "C:\Documents and Settings\Bigred\My
Documents\GuitarInfo" and maybe subfolders below it) I'd probably
proceed as follows:
1) Open a Windows command prompt, use the CD command to navigate to the
"GuitarInfo" folder, and then use a pair of commands like this
DIR *.TXT /S /B > C:\FileList.txt
DIR *.CRD /S /B >> C:\FileList.txt
(note > in the first and >> in the second.)
This creates a textfile containing a list of all the files.
2) Import the list of files into Access.
3) OPen the table in design view, rename the existing field to FileSpec
and add a memo field (let's call it Contents).
4) Get the FileContents() function from
http://www.j.nurick.dial.pipex.com/Code/VBA/FileContents.htm and paste
it into a module.
5) Create and run an update query that uses FileContents() to populate
the Contents field, i.e. updating Contents to something like this:
FileContents("C:\Documents and Settings\Bigred\My Documents\GuitarInfo\"
& [FileSpec], -1)
With 15,000 files this could take hours to run, so while you're testing
be sure to put a criterion on the query that will just select a few
files (e.g. a criterion on [FileSpec] of
Like "Strings\[AB]*"
will get you only the files in the Strings subfolder that begin with A
or B.)
I want to have a query pull a random record from this table
table: PowerTabDirectory
Field: Item (this is a autonumber)
Field: FileName (this is the name of the files)
I tried a query with the item & fileName in it, and added a field with the
below in it:
RandomRecords: Rnd(IsNull(PowerTabDirectory.ItemID)*0+1)
But when I run the query it prompts me for a parameter for the ID, if i hit
a number say 7 it will return will pull up the table with a RandomRecords
field on it and will have a value like 0.75421243 etc....
Ultimately I was trying to create a database of many (15,000) powertab
files, that would allow me to create a file list and then open a random file
with the query.
I created the FileList using I: DIR *.ptb /S/B>C:\FileList.txt
then importing it into access, then i was trying to have a module in the db
and a query make it a clickable link to the file. (IT DIDN'T work)
Is there a good way to pull random records using a query, and is there a
method to make the FileList (which has the complete file path) a clickable
link within access - and then store my powertab (.ptb) files in the same
directory as the db?
(MY ORIGINAL METHODS used were from a previous reply I got to another NG
post) I will paste it below my name (below)
TIA,
_Bigred
|
|
V
If these are unstructured or loosely structured text files, and you want
in effect to index them, a relational database such as Access is not the
most convenient tool for the job. Specialist "text database" software
such as Bekon Idealist (which I've been using for more than 12 years),
InfoSelect or AskSam will make it much easier to do powerful searches.
Or you could leave the files as they are, and learn to use text-file
search tools such as 'grep' to quickly find the one(s) you want. 'grep'
and other classic text file tools are a free download from
http://unxutils.sourceforge.net/
But if you do need to use Access, here's how to get started:
Assuming that all the files are in a convenient and sensible structure
of folders (e.g. a top-level folder "C:\Documents and Settings\Bigred\My
Documents\GuitarInfo" and maybe subfolders below it) I'd probably
proceed as follows:
1) Open a Windows command prompt, use the CD command to navigate to the
"GuitarInfo" folder, and then use a pair of commands like this
DIR *.TXT /S /B > C:\FileList.txt
DIR *.CRD /S /B >> C:\FileList.txt
(note > in the first and >> in the second.)
This creates a textfile containing a list of all the files.
2) Import the list of files into Access.
3) OPen the table in design view, rename the existing field to FileSpec
and add a memo field (let's call it Contents).
4) Get the FileContents() function from
http://www.j.nurick.dial.pipex.com/Code/VBA/FileContents.htm and paste
it into a module.
5) Create and run an update query that uses FileContents() to populate
the Contents field, i.e. updating Contents to something like this:
FileContents("C:\Documents and Settings\Bigred\My Documents\GuitarInfo\"
& [FileSpec], -1)
With 15,000 files this could take hours to run, so while you're testing
be sure to put a criterion on the query that will just select a few
files (e.g. a criterion on [FileSpec] of
Like "Strings\[AB]*"
will get you only the files in the Strings subfolder that begin with A
or B.)