Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with a work-around... (97)

    Hello everyone... Happy Friday!!

    I'm hoping for some help from the experts again... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Is there code that I can run from inside an open Access 97 database, that would import a text file into Excel, save it as an .xls ... and then import the Excel file into an Access table?

    I know this sounds crazy... but as I was telling you in my post last week, I've been coming across some seriously strange anomolies lately... and I'm trying to develop a workaround...

    Here's the situation... I developed an application for the department well over a year ago... It's been working fine until recently... Part of the process involved is importing a delimited text file from one of the network drives every day... All of a sudden, when it tries to import this text file, Access either hangs completely or comes up with an error about the Record Length being too long.... (I immediately thought that it wasn't recognizing the delimiters... but the reallllyyyy strange part is that if the same database.... exactly the same... is run from a computer on another floor... it works perfectly!!... So for a while now (I didn't know this... lol), the person upstairs has been taking the time to run the process and email a new text file to the guy who runs it down here.... The person in my department who gets this text file then saves it to his C:Temp folder, and runs the process with that path.... and it works.... ???? Sound a little crazy to you??? <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> LOL

    I've tried just about everything I can think of... I've tried importing it into a blank database.... I've tried it at different computers in this department ('cause I'm still missing the service packs for '97 on mine)... and nothing is different... If I try to import the original text file placed on the network drive... and/or if I try placing the original text file on another shared drive and import from there... Access hangs completely... or gives that error... (depending on its mood...) ...there's no identifiable pattern... LOL...

    Then I had a thought... Maybe I can import it into Excel???.... That worked perfectly!!!?? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> ... Go figure????

    Now... if someone knows why the problem is occuring to begin with.. wonderful... I'd love to hear it!.... But as of right now... I'm trying to finish this workaround.... I can make it work manually... It imports correctly into Access from the Excel file... However, the manager would prefer that the user not have to manually import the text file to Excel and then import into Access....

    I've never used VBA to access different Office applications in the same process.... Right now I have a macro that runs my code and some queries... Can I incorporate some code to do the import into Excel and then import into Access?... Is this possible?

    Hope that's not a dumb question.... Thanks in advance for any ideas you have...

    Trudi
    <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18><img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with a work-around... (97)

    This is a huge subject. I'll give you some ideas that hopefully get you on the way. You'll find lots of threads on this forum if you do a search for "Excel automation" or something like that.

    You can do (almost) anything in Excel from Access.

    I would suggest that you start by creating a macro in Excel, perhaps using the macro recorder, that imports the text file and saves it to a .xls file. It is much easier to create/write code in the application itself.

    Next, in any module in Access, select Tools/References...
    Look up Microsoft Excel 8.0 Object Library and make sure the check box to the left of it is checked.
    Now, you can write Excel code within Access.
    You have to create an Excel application object:

    Dim objXL As Excel.Application
    Set objXL = CreateObject("Excel.Application")

    (if Excel is already active, you can use GetObject - do a search if you want to know more)

    To use the macro you created in Excel, you have to put
    <pre>objXL.</pre>

    in front of references to Excel objects like Worksheets, etc.

    for instance

    ActiveSheet.Range("A"1:A10")

    becomes

    objXL.ActiveSheet.Range("A"1:A10")

    This way, you can incorporate the Excel macro in your Access code.

    At the end, you'll have to quit the Excel application and release memory:

    objXL.Quit
    Set objXL = Nothing

    It is a good idea to have error handling, so that you can make sure to quit Excel. Otherwise, you might end up with lots of invible instances of Excel.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Have you try to reinstall Office from Control Pannel/Add Remove Programs?
    If this doesn't work try to uninstall Office.
    Dowload Eraser97.exe
    Use the eraser utility to remove all the stuff of Office that remains on the pc after uninstalling Office by the add/remove Programs.
    Re-install Office.
    Francois

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    HansV!!! You're the best!!! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

    Thank you so much for the help! ...It worked perfectly... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Your instructions were wonderful!

    Have a wonderful weekend!

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Hi Trudi,

    The user downloads the file, how? if this is via ftp you may wish to check the transfer protocol that the user selects. I've got a few users who occasionally transfer their text files as binary rather than as ascii. this generally creates a problem where the record terminators are not complete so Access return a message RECORD LENGTH TO LONG.

    Check the record terminators to ensure that there is a CR/LF pair, if one is missing access gets confused, versions after Access 2 anyway.

    I've got some code that will process your text file and save a new version with a clean record terminator if you want it.

    I have processed the text file pre import in some applications but it does add to the processing time.

    If the problem only exist for one user, the problem relates to either what they are doing or their machine. I'd try to establish the cause before coming up with esoteric workarounds.

    Stewart

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Hi Stewart... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Ummm... k... At the risk of sounding like a complete idiot... <img src=/S/baby.gif border=0 alt=baby width=15 height=15>... lol.... can you tell me what "CR/LF" means??... (I don't think I'll ever remember all of these acronyms... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>) ...and how I would check that??

    I'd love to see the code that you have! ...Thank you so much for offering...

    The user doesn't download anything really... The text file is placed on a shared network drive by someone in another department... The file is placed on the drive with a .file extension.... and the code I wrote changes it to a .txt file and then it is imported to Access...

    I don't think it's something the user is doing... or the machine... because I am getting the same problem running it on my machine... AND the same problems occur if I try not using the code and doing each action manually... It's very strange how it works fine if the people on the other floor run it.... and the fact that it worked for well over a year here, with no problems at all... I'm truly confused about how this could be happening...

    *sigh* ....Oh well... It's not the first time that Access has gotten me confused... and I'm sure it won't be the last....

    Have a great day!

    Trudi
    <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18>,<img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Trudi,

    There are no stupid questions. CR = Carriage Return. LF = Line Feed. Access seems to need this combination to determine the end of a record for text import.

    To check what the text file is using for record terminators I've attached a small db. Open your text file and copy a few lines. Paste them into the first record in the table and run the function read in the module. the instances of lf or cr are shown in the debug window 10 = lf 13 = cr.

    The sample data will display 13 13 10 for each record and access will not be able to import this file as there is not a clean record terminator that it can understand.

    The function for fixing a text file is as follows.

    Cheers
    Stewart

    Function text_clean(InDirectory As String, Infile As String, Optional OutDirectory As String = "", Optional Outfile As String = "")

    '################################################# ####################################
    '# Function takes input file and reads it, converting Line Feeds to Carriage Returns #
    '# This in effect cleans up files from the VAX and makes them easier to import into #
    '# Microsoft Access 97. #
    '# #
    '# If the Output File Name is not specified then the original file is overwritten, #
    '# Else the new file created is specified by OutFile #
    '# #
    '# #
    '# #
    '# #
    '# This program assumes 8.3 format, it will work with others, if the file name has #
    '# two "." in the name, then there may be some unexpected results #
    '################################################# ####################################

    On Error GoTo Error_Text_Clean
    text_clean = ""


    Dim x As Integer
    Dim y As Integer
    Dim Message As String
    Dim a_char As String
    Dim a_line As String
    Dim overwrite As Boolean
    Dim Path_InFile As String
    Dim Path_OutFile As String
    Dim Path_FileDelete As String
    Dim filelength As Long
    Dim currentrecord As Long
    Dim meterReturn As Variant

    overwrite = False

    x = FreeFile

    If Dir(InDirectory & Infile) = "" Then
    MsgBox "Input File " & Infile & " ,In Directory: [" & InDirectory & "] Does Not Exist, Please check import file has been created and try again", vbCritical
    Exit Function
    End If

    Path_InFile = InDirectory & Infile
    Open Path_InFile For Input As x

    y = FreeFile

    If Len(Outfile) + Len(OutDirectory) = 0 Then
    overwrite = True
    End If

    If overwrite = True Then
    Path_OutFile = InDirectory & "temp.txt"
    Else
    Path_OutFile = OutDirectory & Outfile
    End If

    Open Path_OutFile For Output As y
    currentrecord = 0
    filelength = LOF(x)

    meterReturn = SysCmd(acSysCmdInitMeter, "Checking Import Text File Format", filelength)

    Do While Not EOF(x)
    Do
    a_char = Input(1, #x)
    currentrecord = currentrecord + 1
    meterReturn = SysCmd(acSysCmdUpdateMeter, currentrecord)

    If InStr(vbCrLf, a_char) = 0 Then
    a_line = a_line & a_char
    End If

    Loop While a_char <> vbLf
    Print #y, a_line
    a_line = ""
    Loop

    Close x
    Close y

    If overwrite = True Then
    Path_FileDelete = InDirectory & Left$(Infile, InStr(Infile, ".")) & "old"
    If Dir(Path_FileDelete) > "" Then '(delete .old file if it exists)
    Kill Path_FileDelete
    End If
    Name Path_InFile As InDirectory & Left$(Infile, InStr(Infile, ".")) & "old" 'rename existing input file to .old
    Name Path_OutFile As Path_InFile 'rename output file to input file name
    End If

    text_clean = Path_OutFile
    meterReturn = SysCmd(acSysCmdClearStatus)
    Exit Function

    Error_Text_Clean:

    Close x
    Close y

    Message = "An Error Occured while processing " & Infile & vbCr & "Import File May Be Corrupted"
    MsgBox Message, vbCritical
    MsgBox "Please Write Down this Error Number and Description and seek assistance" & vbCr & "Error Number " & Err.Number & vbCr & "Error Description " & Err.Description, vbExclamation
    text_clean = ""
    meterReturn = SysCmd(acSysCmdClearStatus)
    End Function
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Stewart... You're a lifesaver!!

    I thought Hans' idea about bringing the file into Excel was working... but I ran into a few problems with that... 1) It would only allow me to import 16,384 records from the .xls file.... (the table has over 20,000) ... and 2) For some reason beyond me, Excel was formatting a text field (even though I had created an import spec... Strange but true)... and that was resulting in a lot of queries not working... joins, etc... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Anyway.... I tried fixing the file with your code and it worked perfectly.... Thank you so much for your help.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a work-around... (97)

    Fantastic, I'm glad to have helped.

    The fact that the code fixed the import file meant that there was a problem with record terminators.

    Stewart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •