I help oversee a rather simple Acess 2000 databse in which files are downloaded into two separate tables in order to run reports. The files are comma-delimited, daily archive files from an automated med dispensing system, and have extensions that 2000 will not recognize. At present, we chnge the extensions to ".txt" in DOS command. The file names are the dates that are archived e.g. 030404a.txt, 030504a.txt etc. To import we open a TransferTxt macro in design view and change the names on the source line to each date file on the floppy. It's not a very efficient system. Would like to find a code that will change the extension (already found this on this forum) then loop thru the files on the floppy and download into appropriate tables. There are four different file types for each day, so on a floppy containing a week's worth of data, there are 28 different files, but we download only two types of files into two different tables. Consequently, we probably need two nearly identical modules for the specific files. I am a real amateur at using VB, so it is really a struggle for me. Any help would be GREATLY appreciated.
Dim strImpPath As String
Dim strImpFileNameExt As String
strImpPath = "A:"
strImpFileNameExt = Dir(strImpPath & "*.txt")
While (strImpFileNameExt <> "")
DoCmd.TransferText acImportDelim, strImportSpec, strTblName, strImpPath & strImpFileNameExt, 1
strImpFileNameExt = Dir
The first Sub imports three different Specs into three different files using the same tables and same text files. If your text files have different formats you will have to alter slightly (e.g. use sub folders on the A: Drive)
This answers the second part of your query (importing and looping through the files). You may be able to import in their current format. If not then use the Name Statement. Access XP help as below
Name Statement Example
This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, HD: is the default drive name and portions of the pathname are separated by colons instead of backslashes.
Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.
OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.
You would have to loop through files as in Reply (1)
Sorry it took so long to reply. The import section worked great, but could not get the renaming code to work because it apparently did not like a wildcard in filename. I was trying OlName = a:*.PYX MewName = *.TXT. and it kept coming up with an error code saying the file was not found.
I was wondering if there was a way to rename all of the "*.PYX" files on the floppy to "*.TXT", run the import code, then change the names back to "*.PYX. I could have another sub on a button click that would change the "*.DTX" files to "*.TXT", import the files, and then change these files back to the original file extension. That would eliminate the need to create folders on the floppy and move the files.