How to set a form to a specific directory

[exm206]

I have a database that I am using to automate the importing of text files. I am using the TransferText method to do this. What I am trying to do is write some sort of code in the module that sets it so Access will only look in a certain folder for the text file. For example, I only want it to look in the c:\TextFiles folder. Is there something as simple as

Set Path c:\TextFiles

or is it something more complex? Hopefully, I was clear in what I am looking for. Thanks a lot for any help you could give me.

 

[bat17]

Can you post your code so that we can see what needs tweaking?

Peter

 

[exm206]

Thanks for your time. Here is the code I am using:

Function UserInputFileName()
Dim UserFileName As String
UserFileName = InputBox("Please enter a file name", "File Name Request")
DoCmd.RunSQL "DELETE AutoClaim.* FROM AutoClaim;"
DoCmd.TransferText acImportFixed, "AutoClaim Import Specification", "Autoclaim", _
UserFileName, "0"
DoCmd.Beep
MsgBox "File was successfully imported!", vbOKOnly, "Message Box"

End Function

Private Sub ImportButton_Click()
Call UserInputFileName
End Sub

The problem, more specifically, is that when the user encounters the input box asking them to enter the name of file they want to import, they have to enter it in this format: c:\autoclaim.txt

I was hoping that somewhere in the module I could tell Access to only look in the c drive, so the user would only have to enter autoclaim.txt without the path before it. Thanks again for any help you can give me. I really appreciate it.

 

[bat17]

Function UserInputFileName()
Dim UserFileName As String
Dim strPath As String
strPath = "c:\"
UserFileName = InputBox("Please enter a file name", "File Name Request")
DoCmd.RunSQL "DELETE AutoClaim.* FROM AutoClaim;"
DoCmd.TransferText acImportFixed, "AutoClaim Import Specification", "Autoclaim", _
strPath & UserFileName, "0"
DoCmd.Beep
MsgBox "File was successfully imported!", vbOKOnly, "Message Box"
End Function

should do it. Or you could use:-

DoCmd.TransferText acImportFixed, "AutoClaim Import Specification", "Autoclaim", _
"c:\" & UserFileName & ".txt", "0"
so that they dont have to add the extension either

HTH

Peter

 

[mdmilner]

I know this is a little beyond the scope of your question, but have you looked at something like this? It opens windows API call to allow a user to select an individual file for use. A few modifications and you could grab the filename and perform your existing transfertext function on it.

http://www.mvps.org/access/api/api0001.htm

Alternatively, I've got a code snippet that allows the user to drill down to a specific directory then dumps all filenames to the debug window. I'm not sure where I grabbed it from (at the moment) - I'd prefer to find the original link before I re-posted it though, as I think that's the proper 'etiquette' for the code of others.

Mike