Jason & Ted - Linking

  • Thread starter Thread starter Toby
  • Start date Start date
T

Toby

All right guys, first lets all get on the same page
because I'm getting confused. Now Jason after messing
around awhile I seem to need to do exactly the same as
you. I need to search through a folder and link to
multiple files, all of which then need to be stored in 1
Table so that I can create reports and queries of ALL the
data. I think we both now have the code to search through
files, correct? I also have code to link data. Now I have
to figure out how to take all these links and store them
in one table. Im thinking a querie. How are you coming
along and please give me your feedback/help on this. Much
thanks to Ted in assisting us through this and Ted I would
appreciate if you continue along with us, as I am trying
to find a solution to getting these files off a server and
not locally :( ...
 
Hi Toby,

Instead of thinking in terms of "linking to multiple files", think in
terms of importing them one at a time. Unfinished code fragment:

strFN = Dir("C:\Folder\*.txt")
Do While Len(strFN) > 0
'import this file to Access table
DoCmd.TransferText blah blah blah
'rename this file so we don't use it again by accident
Name strFN As strFN & ".done"
'get next filename
strFN = Dir()
Loop
 
Hi Toby,

I agree with the other post that you will likely be
better off importing the info, but it may depend on
various factors such as how large the amount of data is,
how often it changes, what you want to do with it, etc.

Let's say you decide to import the data, then you just
need to decide on what process you will use to be able to
import the changes periodically and keep your database
table up to date. The best way to do this will depend on
a number of factors.

You may want to post back with a little more info such as
how large these files would be (would it be a burden to
your database to import?), how often and in what way is
the data going to be used, how often will it change, will
the data in individual files change or will additional
files just be added to contain additional data, etc.
Then I or someone else could probably give a good outline
of the process that you may want to use.

Also, regarding your desire to work directly from the
network(without copying to a local drive first), you This
shouldn't be a problem. The question that Jason had that
I'm not sure about is what to do if the data is on a web
server. I know that you can use Application.Filesearch
to browse network files, but I've never tried using it
for a web server address (www...) and I don't know
whether or not this will work. I would assume there is a
way to do it, but I haven't had the need before so I'm
not sure what the process would be.
 
Hi John,

I want to thank you for this post. In the past I have
always used Application.Filesearch for locating files,
but the Dir() function will be much simpler in many
cases. I never knew it existed. I kind of glossed over
your message before (read it, but didn't look closely at
the code). I'm glad I looked at it a second time. It's
always nice when you find an easier way of doing things.
Thanks so much.

-Ted
-----Original Message-----
Hi Toby,

Instead of thinking in terms of "linking to multiple files", think in
terms of importing them one at a time. Unfinished code fragment:

strFN = Dir("C:\Folder\*.txt")
Do While Len(strFN) > 0
'import this file to Access table
DoCmd.TransferText blah blah blah
'rename this file so we don't use it again by accident
Name strFN As strFN & ".done"
'get next filename
strFN = Dir()
Loop

All right guys, first lets all get on the same page
because I'm getting confused. Now Jason after messing
around awhile I seem to need to do exactly the same as
you. I need to search through a folder and link to
multiple files, all of which then need to be stored in 1
Table so that I can create reports and queries of ALL the
data. I think we both now have the code to search through
files, correct? I also have code to link data. Now I have
to figure out how to take all these links and store them
in one table. Im thinking a querie. How are you coming
along and please give me your feedback/help on this. Much
thanks to Ted in assisting us through this and Ted I would
appreciate if you continue along with us, as I am trying
to find a solution to getting these files off a server and
not locally :( ...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Ok.. That seems to be the best logical approch to our dilemma. Thank you John. If it is not too much to ask, could someone please put that code into a format that can actually be used? I mean I am so confused right now, I really don't know where I am. I mean... we going to make that a function or... *terrible lost* but I do understand the theory

Thanks much
~jason
 
OK I DID IT. YAY

Private Sub Command24_Click(
LocateFile ("*.xls") ' calls the LocateFile function and uses * for wildcard for strfilename to match
End Su
Function LocateFile(strFileName As String
Dim vItem As Varian
With Application.FileSearc
.FileName = strFileNam
.LookIn = "J:\yourdirectory" 'change this to whatever your path needs to b
.SearchSubFolders = False 'leave as false, don't want to search in lower folders
.Execut
For Each vItem In .FoundFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable", vItem, True, "
Next vIte
End Wit
End Functio


OK THOUGH.... the only problem is that it will import from all of the files EACH time you press the hot button! I need to eliminate this bug by doing that renaming tool thingy that was mentioned. Could someone PLEASE modify this code to STOP the duplication of records or files that have already once been imported into the new table? I think John had the answer via a renaming loop or something. PLEASE??? I have to go tell my boss that I have found the solution! (well partially) I love you guys (n gals

~jason
 
Hi Jason,

Glad to see you are getting closer. I would think that
there are a few ways to keep from importing data that has
already been imported. Perhaps the easiest way would be
to set certain fields in the table as the primary key.
Then, any duplicates of the combination of those fields
should be rejected by the import.

Another way that I can think of would be to log each
filename in a table as it is imported (via code). Then,
as going through your loop, before importing you would
check to see if the filename exists in your log table.
But, this would only work if the contents of a given
filename will not change over time, and if other files
will not contain duplicate information of each other.
Let me know if this is the case and I can give some
sample code.

I'm sure there are other ways as well, but I would think
one of those would work ok. Let me know what you think
would work best for you.

-Ted
-----Original Message-----
OK I DID IT. YAY!

Private Sub Command24_Click()
LocateFile ("*.xls") ' calls the LocateFile
Click to expand...
function and uses * for wildcard for strfilename to match.
End Sub
Function LocateFile(strFileName As String)
Dim vItem As Variant
With Application.FileSearch
.FileName = strFileName
.LookIn = "J:\yourdirectory" 'change this to whatever your path needs to be
.SearchSubFolders = False 'leave as false, don't
Click to expand...
want to search in lower folders.
.Execute
For Each vItem In .FoundFiles
DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel9, "YourTable", vItem, True, ""
Next vItem
End With
End Function



OK THOUGH.... the only problem is that it will import
Click to expand...
from all of the files EACH time you press the hot button!
I need to eliminate this bug by doing that renaming tool
thingy that was mentioned. Could someone PLEASE modify
this code to STOP the duplication of records or files
that have already once been imported into the new table?
I think John had the answer via a renaming loop or
something. PLEASE??? I have to go tell my boss that I
have found the solution! (well partially) I love you guys
(n gals)
 
Hi Ted,

Primary key (or "no duplicates" index) to prevent accidental importation
of duplicate records: this is needed regardless of anything done with
the import files.

Rather than log the filenames (unless there's a specific need for this),
I'd just have the import code do something with each file after
importing it.
-rename, as in my example elsewhere in this thread (using the VBA Name
statement)
-delete (using the VBA Kill statement)
-move to an "archive" folder (also using the Name statement)
 
Hey guys..

After talking with a few people, I have realized that I must move these files to a subdirectory called "archive". I was thinking I could just execute a "move *.xls" command of sorts after the importation. How exactly would I go about this

~jason
 
It's in the message to which you were replying.

Move each file immediately after you've imported it. If you wait until
you've finished importing a batch before you archive themn, you won't
know where you are if there's a crash part way through the batch.
 
Back
Top