Automatic import not working

[Office User]

Trying to use code to automatically import txt files in Access table. The
code is below. The TransferText part was working before I put in the
FileCopy and Kill. In fact it was working too well and just continued to
loop through the code and import the file numerous times. Oops!

Any ideas why it's not working now? I don't get any error messages
specifically. I do see both MsgBox messages though.
Thanks,
Marcia

****************************
Private Sub cmdUpdates_Click()
'imports daily files run from CMS Supervisor
'then moves files to Backup folder on network drive
'prints daily report

On Error GoTo Err_Handler

Dim strfile1, strfile2 As String
Dim strpath, strpathBackup As String
strpath = "R:\~Field Support Team\Support Calls\Historical Support
Calls\Daily Calls\"
strpathBackup = "R:\~Field Support Team\Support Calls\Historical Support
Calls\Backups\"

ChDir (strpath)
strfile1 = Dir("*calls.txt")
Do While Len(strfile1) > 0
'imports calls file to All Calls table
DoCmd.TransferText acImportDelim, "Calls Import Specification", "All
Calls", strpath & strfile1, False
'moves files to Backup folder
FileCopy strpath & strfile1, strpathBackup & strfile1
Kill strpath & strfile1
strfile1 = Dir
Loop
MsgBox "Call file has been updated"

strfile2 = Dir("*.agents.txt")
Do While Len(strfile2) > 0
'imports agent file to Agent Data table
DoCmd.TransferText acImportDelim, "Agents Import Specification", "Agent
Data", strpath & strfile2, False
'moves files to Backup folder
FileCopy strpath & strfile1, strpathBackup & strfile1
Kill strpath & strfile1
strfile2 = Dir
Loop
MsgBox "Agent file has been updated"

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
conMod & ".ShowHighligher"
Resume Exit_Handler

End Sub

 

[Larry Daugherty]

How is it "not working now"?

 

[Office User]

By not working I mean it doesn't add the records to my table in Access. It
did add the records (too many times because of the Loop) before I added the
FileCopy and Kill commands.

Marcia

 

[Office User]

I tried taking the FileCopy and Kill commands out but seems it doesn't import
the data at all. Looks like I broke it good!

ChDir (strpath)
strfile1 = Dir("*calls.txt")
Do While Len(strfile1) > 0
'imports calls file to All Calls table
DoCmd.TransferText acImportDelim, "Calls Import Specification", "All
Calls", strpath & strfile1, False
strfile1 = Dir
Loop
MsgBox "Call file has been updated"

strfile2 = Dir("*.agents.txt")
Do While Len(strfile2) > 0
'imports agent file to Agent Data table
DoCmd.TransferText acImportDelim, "Agents Import Specification", "Agent
Data", strpath & strfile2, False
strfile2 = Dir
Loop
MsgBox "Agent file has been updated"

Thanks for any insight someone can give.
Marcia

 

[Larry Daugherty]

Hi Marcia,

I hope you have a backup copy of your application in which the code
last worked.

If you get back to the point where it was working, however feebly,
Make a backup and start over on a copy.

copy another set of target files into the target directory (it
couldn't be that you've deleted all of your target files)?? clean up
the table that receives the data.

I'd put the copy and Kill commands back in and then I'd open that
module and make the Immediate/Debug window visible and put a
breakpoint in before the start of the loop.

Be patient with yourself. If you aren't familiar with the debugger,
refer to help and post back with questions as necessary. You need to
single step thru your code and observe the values in your variables.
See if they're what you expected.
At some point, what you see should differ from what you thought you'd
see.
When you do post back, copy your current procedure code and paste it
into your email.

Your next post will probably be to tell us you have it solved. But do
post back and tell the point of the problem and the fix you applied.

HTH