f/u to kahgen's bulk importing question.

Hi brewdog,

I saw your response to khagen who wanted to import a lot of text files. I then attempted to apply the code.  As you suggested, I removed the If..EndIf section, since I am importing all files using one spec into the same table (from a floppy).

When I click my import button, I get the message "compile error:  variable not defined".  After clicking "ok", Access highlights "Set db" of the "Set db = CurrentDb" line below.
____________
Private Sub Import_CurrEval_Text_Files_Click()

Dim strFile As String
Dim strDirectory As String
Dim strTable As String
Set db = CurrentDb
Do While strFile <> ""
    strTable = "tblCurrEval"
DoCmd.TransferText acImportDelim, strTable, "a:\" & strFile
    Str File = Dir
Loop
MsgBox "The import is complete.", vbInformation, "Import Finished"

End Sub
_____________
I would appreciate any suggestion you could make.
Thanks, archin
archinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cjswimmerCommented:
try setting db = DBEngine.Workspaces(0).Databases(0)
0
archinAuthor Commented:
Hi cjswimmer,

Thanks for your input.  I tried your suggestion but got the same message. "Set db" is still highlighted.  I replaced
 
Set db = CurrentDb

with

Set db = DBEngine.Workspaces(0).Databases(0)


0
cjswimmerCommented:
make sure you have "Dim db as Database" before these lines
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

archinAuthor Commented:
Hi cjswimmer,

That helped some.  The "variable not defined" msg no longer displays.  I also now get the "import is complete" msg. but no data exists in my table.

Here's the latest code:

Private Sub Import_CurrEval_Text_Files_Click()

Dim strFile As String
Dim strDirectory As String
Dim strTable As String
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
Do While strFile <> ""
    strTable = "tblCurrEval"
DoCmd.TransferText acImportDelim, strTable, "a:\" & strFile
    strFile = Dir
Loop
MsgBox "The import is complete.", vbInformation, "Import Finished"

End Sub
_____________
note:  I've put 5 text files on a floppy(a:\) having the *.txt extension.  These files I have successfully imported in the past to the same table.
Would the default specs have any influence on my success?  These files have text which is delimitted by quotations and separated by commas.
Thanks, Russ
0
brewdogCommented:
It looks like you missed a line in code: you never tell it where to start with the Dir command. In your case, you'd need to add this line:

strFile = Dir("a:\*.txt")

So your final code might look like:

Private Sub Import_CurrEval_Text_Files_Click()
  Dim strFile As String
  Dim strDirectory As String
  Dim db As Database
  Set db = DBEngine.Workspaces(0).Databases(0)
  strFile = Dir("a:\*.txt")
  Do While strFile <> ""
      DoCmd.TransferText acImportDelim, "tblCurrEval", strFile
      strFile = Dir
  Loop
  MsgBox "The import is complete.", vbInformation, "Import Finished"
End Sub

Does that work?

brewdog
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
archinAuthor Commented:
Hi brewdog,

I added the missing line of code you suggested and now I get "Run-time error 2522: The action or method requires a File Name arguement".  

Thanks, archinn
0
archinAuthor Commented:
brewdog,

I forgot to mention that debug highlighted the line,

DoCmd.TransferText acImportDelim, strTable, strFile

Russ
0
brewdogCommented:
ahhhh . . . I think we'd have to do this:

DoCmd.TransferText acImportDelim, strTable, "a:\" & strFile

if I remember right, the Dir is only going to return the name of the file, which will make Access wonder in which directory the file resides. If you still get the error, place the word Stop at the top of the procedure and try to run it. Access will stop at the word Stop, and you can press F8 to step through each line; holding your mouse over strFile will tell you what file the code is working on importing.
0
archinAuthor Commented:
Hi brewdog,

I added the line,

DoCmd.TransferText acImportDelim, strTable, "a:\" & strFile

and got the same error(however, this time at least Access tried to read the floppy). So I added the word, Stop, right after the DoWhile line.  I clicked my import button and stepped thru the code.  When I got to the above Do.Cmd line, the same "Run-time error 2522" displayed.  I then clicked debug and held the mouse over strFile and it displayed my file, "06_26_99.txt".  I could not step past this line and the code did not proceed to any other file. The same error msg displayed each time.  Here's one side note.  I have four other files on the disk named 06_12_99.txt, 06_19_99.txt, 07_03_99.txt, 07_10_99.txt.  It tried to pick up the middle file first.
Thanks, Russ



0
brewdogCommented:
hmm, I'm not sure why it would skip files. I tried the code we've worked out, and it pulls files in just fine. I'm wondering if Access isn't liking the file names for some reason. (A coworker just had a problem importing Excel spreadsheets that had purely numeric names.) What happens if you change the file names (temporarily) to regular text names, i.e., June1299.txt, June1999.txt, June2699.txt?
0
archinAuthor Commented:
brewdow,

I changed all five files to a.txt, b.txt, c.txt, d.txt, and e.txt.  When I executed and stepped thru the code, I got the same error on the same line.  Interestingly, when I placed the mouse over strFile, it displayed the e.txt file.  Does that make sense?  Anyway, to make sure you have the exact code I used, here it is.  Thanks, archin.
______________
Private Sub Import_CurrEval_Text_Files_Click()

  Dim strFile As String
  Dim strDirectory As String
  Dim strTable As String
  Dim db As Database
  Set db = DBEngine.Workspaces(0).Databases(0)
  strFile = Dir("a:\*.txt")
  Do While strFile <> ""
      Stop
      strTable = "tblCurrEval"
      DoCmd.TransferText acImportDelim, strTable, "a:\" & strFile
      strFile = Dir
  Loop
0
archinAuthor Commented:
Hi brewdog,

I figured out what the problem was.  I never created a file spec to coincide the file's fields with my table.  I found out by creating a macro to import a single text file and got an error message that "F1" or, Field1, could not be found in my destination table.  Then I got it.  So I created my first file spec and it worked.
Thanks, archin
0
brewdogCommented:
cool. Now we can both relax for a while, eh? :o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.