Help with a work-around… (97)

Topic

New Reply

Hello everyone… Happy Friday!!

I’m hoping for some help from the experts again…

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??? 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!!!?? … 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

Reply | Quote

Replies

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

objXL.

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.

Reply | Quote

HansV!!! You’re the best!!!

Thank you so much for the help! …It worked perfectly…
Your instructions were wonderful!

Have a wonderful weekend!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Stewart…

Ummm… k… At the risk of sounding like a complete idiot… … lol…. can you tell me what “CR/LF” means??… (I don’t think I’ll ever remember all of these acronyms… ) …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
,

Reply | Quote

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

Reply | Quote

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…

Anyway…. I tried fixing the file with your code and it worked perfectly…. Thank you so much for your help….

Reply | Quote

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

Reply | Quote