C
cw via AccessMonster.com
I'm using the following code to Import all the Text files in the Folder.
------------------------------------------------------------
Private Sub ImportAllFiles_Click()
'Procedure to import all files in a directory and
'assumes they are all the correct format for an ASCII fixed-length import.
Dim strFolder As String
Dim strFile As String
Dim tblName As String
Dim fileName As String
fileName = Forms!frmCDRCICData.Combo27
mDofReport = Format(Date, "yyyymmdd")
DoCmd.SetWarnings False
'First Delete Old data from the tblCABsRawFiles Table
DoCmd.OpenQuery "001qryDelOldRecords", acNormal, acEdit
'Second we will Import all .txt files in the specified folder
strFolder = "H:\Billing\CABs Raw Files\Current\"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
tblName = "tblCABsRawFiles"
DoCmd.TransferText acImportFixed, "CDR_CIC", tblName, strFolder &
strFile, True
strFile = Dir
Loop
'Lastly Export to a Fixed Length .txt file and save to the H:\Billing\CABs
Raw Files\specific cics\ folder
DoCmd.TransferText acExportFixed, "TblCABsRawFiles Export Specification",
"qryCIC", "H:\Billing\CABs Raw Files\specific cics" & "\" & mDofReport &
"_CIC_" & fileName & ".txt", False, ""
Beep
MsgBox "EXPORT COMPLETE ! File was exported correctly to H:\Billing\CABs
Raw Files\specific cics!", vbOKOnly, "CDR_CIC FILE EXPORT UTILITY"
DoCmd.SetWarnings True
End Sub
----------------------------------------------------
This is working great right now but with growing text file sizes what I need
to change is this:
- Instead of actually Importing the Text files in the Folder
- I just want to link to them & run my export query from the linked Tables
How do I alter my code to LINK instead of IMPORT the files?
How would I deal with the multiple monthly changing Text filenames in the
Folder?
Can I LINK to the Text Files in a Folder w/o specifying their actual names?
My thoughts were to change the acImportFixed to acLinkFixed but not sure if
this will give me "dynamic" linked tables?
Any help much appreciated,
Thanks,
cw
------------------------------------------------------------
Private Sub ImportAllFiles_Click()
'Procedure to import all files in a directory and
'assumes they are all the correct format for an ASCII fixed-length import.
Dim strFolder As String
Dim strFile As String
Dim tblName As String
Dim fileName As String
fileName = Forms!frmCDRCICData.Combo27
mDofReport = Format(Date, "yyyymmdd")
DoCmd.SetWarnings False
'First Delete Old data from the tblCABsRawFiles Table
DoCmd.OpenQuery "001qryDelOldRecords", acNormal, acEdit
'Second we will Import all .txt files in the specified folder
strFolder = "H:\Billing\CABs Raw Files\Current\"
strFile = Dir(strFolder & "*.txt")
Do While Len(strFile) > 0
tblName = "tblCABsRawFiles"
DoCmd.TransferText acImportFixed, "CDR_CIC", tblName, strFolder &
strFile, True
strFile = Dir
Loop
'Lastly Export to a Fixed Length .txt file and save to the H:\Billing\CABs
Raw Files\specific cics\ folder
DoCmd.TransferText acExportFixed, "TblCABsRawFiles Export Specification",
"qryCIC", "H:\Billing\CABs Raw Files\specific cics" & "\" & mDofReport &
"_CIC_" & fileName & ".txt", False, ""
Beep
MsgBox "EXPORT COMPLETE ! File was exported correctly to H:\Billing\CABs
Raw Files\specific cics!", vbOKOnly, "CDR_CIC FILE EXPORT UTILITY"
DoCmd.SetWarnings True
End Sub
----------------------------------------------------
This is working great right now but with growing text file sizes what I need
to change is this:
- Instead of actually Importing the Text files in the Folder
- I just want to link to them & run my export query from the linked Tables
How do I alter my code to LINK instead of IMPORT the files?
How would I deal with the multiple monthly changing Text filenames in the
Folder?
Can I LINK to the Text Files in a Folder w/o specifying their actual names?
My thoughts were to change the acImportFixed to acLinkFixed but not sure if
this will give me "dynamic" linked tables?
Any help much appreciated,
Thanks,
cw