Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

f/u to kahgen's bulk importing question.

Posted on 1999-07-23
13
Medium Priority
?
276 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:archin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 6

Expert Comment

by:cjswimmer
ID: 2001751
try setting db = DBEngine.Workspaces(0).Databases(0)
0
 

Author Comment

by:archin
ID: 2001752
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 2001753
make sure you have "Dim db as Database" before these lines
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:archin
ID: 2001754
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
 
LVL 10

Accepted Solution

by:
brewdog earned 300 total points
ID: 2001755
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
 

Author Comment

by:archin
ID: 2001756
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
 

Author Comment

by:archin
ID: 2001757
brewdog,

I forgot to mention that debug highlighted the line,

DoCmd.TransferText acImportDelim, strTable, strFile

Russ
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2001758
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
 

Author Comment

by:archin
ID: 2001759
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2001760
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
 

Author Comment

by:archin
ID: 2001761
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
 

Author Comment

by:archin
ID: 2001762
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2001763
cool. Now we can both relax for a while, eh? :o)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question