Macro in 97 won't run on a particular PC

[wls64]

I have an Access 97 app (not written by me) in which there is a macro that calls a VB function.&nbsp;&nbsp;This function uses a file containing folder names to pull in data from a fileserver into local tables.&nbsp;&nbsp;If I run this app on my PC I receive the &quot;Operation Complete&quot; message at the end of the function, but no records have been placed in the local files.&nbsp;&nbsp;If I run it on a different PC then the macro runs, it flips to the tables tab and higlights the different tables and records are pulled in.&nbsp;&nbsp;Running debug steps thru the function, but still no records are placed in the local tables.<br><br>Does anyone know how I can find out what is causing this:&nbsp;&nbsp;dll mismatch (I tried to match up the dll and ocx versions, but it didn't help), or ????&nbsp;&nbsp;I am a Unix Oracle DBA, so I can't say I'm a PC expert, much less Access.&nbsp;&nbsp;<br><br>Thank you!

 

[PaulF]

If you could post the code for the function, we could look at it and see where any potential bugs are at<br><br>PaulF

 

[wls64]

Here's the code....&nbsp;&nbsp;I didn't see anything weird.&nbsp;&nbsp;And since it runs on 1 PC, but not another, I don't see how it could be the code itself.&nbsp;&nbsp;That's why I was thinking it must be support supporting file or dll.<br>------------------------------------------------------<br>Function ImportTablesByFile()<br>Dim sDirectory As String<br>&nbsp;&nbsp;&nbsp;Open &quot;c:\hold.txt&quot; For Input As #3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While Not EOF(3)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Line Input #3, sDirectory<br>Debug.Print sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ImportTable &quot;tab1&quot;, sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ImportTable &quot;tab2&quot;, sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ImportTable &quot;tab3&quot;, sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ImportTable &quot;tab4&quot;, sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ImportTable &quot;tab5&quot;, sDirectory<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoEvents<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Operation Complete&quot;<br>End Function

 

[famousb]

ok, this may be a dumb question, but the code references<br>c:\hold.txt<br>does this exist on your computer as well as the other users? <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[PaulF]

couple of questions&nbsp;&nbsp;<br><br>where is the value for sDirectory set at?<br><br>what does ImportTable do? Is it another function call?<br><br>I'm not familar with the code used here.<br><br>PaulF

 

[wls64]

1.&nbsp;&nbsp;Yes, hold.txt is on both PCs.<br>2.&nbsp;&nbsp;This is code created by a former employee.&nbsp;&nbsp;ImportTable is another function, here's the code:<br><br>Sub ImportTable(sFile As String, sDirectory)<br>On Error Resume Next<br>Dim dbTemp As Database<br>Dim rsTemp As Recordset<br>Dim sTemp As String<br>DoCmd.SetWarnings False<br>'&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.RunSQL &quot;DELETE * FROM [&quot; & sFile & &quot; raw]&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Open &quot;i:\hold\&quot; & sDirectory & &quot;\&quot; & sFile & &quot;.dat&quot; For Input As #1<br>&nbsp;&nbsp;&nbsp;&nbsp;If Err = 53 Then<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;i:\hold\&quot; & sDirectory & &quot;\&quot; & sFile & &quot;.dat Not Found&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;ElseIf Err = 76 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;i:\hold\&quot; & sDirectory & &quot; Not Found&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Open &quot;c:\windows\temp\temp.dat&quot; For Output As #2<br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbTemp = DBEngine.Workspaces(0).Databases(0)<br>'&nbsp;&nbsp;&nbsp;&nbsp;Set rsTemp = dbTemp.OpenRecordset(sFile & &quot; raw&quot;, DB_OPEN_DYNASET)&nbsp;&nbsp;&nbsp;&nbsp;' Crea<br>&nbsp;&nbsp;&nbsp;&nbsp;Do While Not EOF(1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Line Input #1, sTemp<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Trim(sTemp) &lt;&gt; &quot;&quot; Then<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsTemp.AddNew<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsTemp!Record = Trim(sTemp)<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rsTemp.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Print #2, sTemp<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop <br>&nbsp;&nbsp;&nbsp;&nbsp;Close #1<br>&nbsp;&nbsp;&nbsp;&nbsp;Close #2<br>'&nbsp;&nbsp;&nbsp;&nbsp;rsTemp.Close<br>'&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.RunSQL &quot;DELETE * FROM [&quot; & sFile & &quot; temp]&quot;<br>'&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText A_IMPORTFIXED, sFile, sFile & &quot; temp&quot;, &quot;c:\windows\temp\temp.dat&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText A_IMPORTFIXED, sFile, sFile, &quot;c:\windows\temp\temp.dat&quot;<br>'&nbsp;&nbsp;&nbsp;&nbsp;dbTemp.Execute &quot;INSERT INTO [&quot; & sFile & &quot;] SELECT * FROM [&quot; & sFile & &quot; temp];&quot;&nbsp;&nbsp;&nbsp;<br>DoCmd.SetWarnings True<br>End Sub<br><br>Like I said before, both computers have Access97 and other Access apps run on my PC, as do other macros and queries in this app.&nbsp;&nbsp;I just can't figure out why this one macro will not run on my PC, but runs fine on the other one.&nbsp;&nbsp;<br>Thanks!

 

[famousb]

next question, do you have access to the I:\? and have c:\windows\temp\temp.dat on your computer.<br><br>I know these are basic questions, but they can very often be the root of the problem. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[PaulF]

do the following exist on your PC<br><br>directory with subfolders:&nbsp;&nbsp;&nbsp;i:\hold\ <br><br>file:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;c:\windows\temp\temp.dat&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i:\hold\ Somefolder\tab1.dat<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i:\hold\ Somefolder\tab2.dat<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i:\hold\ Somefolder\tab3.dat<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i:\hold\ Somefolder\tab4.dat<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;i:\hold\ Somefolder\tab5.dat<br><br>Import Specifications<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tab1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tab2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tab3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tab4<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tab5<br><br>And when you say you debug and the process runs through all the way, what does sDirectory show in the debug window?<br><br>PaulF

 

[wls64]

OK, yes, I have access to all the network directories and have the temp.dat file on my PC.&nbsp;&nbsp;sDirectory shows the value of the first folder name in the hold.txt file(and if you step thru long enough, it moves on to each folder in the list). And temp.dat is filling up with values from the files on the I:\ drive as I step thru the function in Access.&nbsp;&nbsp;But one thing I see in the debug is under 'Connection' it shows '&lt;Operation not supported for this type of object&gt;'.&nbsp;&nbsp;Haven't tried this yet on the working PC to see if this shows up there too.&nbsp;&nbsp;Once I step all the way thru it goes to the end of the function and pops up the &quot;operation complete&quot; message box.&nbsp;&nbsp;But the records are still not in the local tables.<br><br>Don't understand what you mean about &quot;import specifications&quot;.&nbsp;&nbsp;The routines in this app do the importing.&nbsp;&nbsp;There is so much stuff in the debug window I really don't know what items I should be looking for as signals to what is going wrong.<br><br>Thanks!!<br>

 

[PaulF]

This line shown below is in the ImportText function. <br><br>DoCmd.TransferText A_IMPORTFIXED, sFile, sFile,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;c:\windows\temp\temp.dat&quot;<br><br>It is Access code to import a text file whose name is set by the variable sFile, and uses an import specification file named by the variable sFile, therefore, since sFile is going to be &quot;Tab1&quot; thru &quot;Tab5&quot; then you should have 5 import specification files in your mdb named Tab1 thru Tab5.<br><br>To run a test on this, open the mdb this process is in.&nbsp;&nbsp;Select File -&gt; Get External Data -&gt; Import from the menu.&nbsp;&nbsp;When the dialog box appears, select Text File for File of Types, then highlight a text file and Click on Import.&nbsp;&nbsp;When the Import Wizard box appears there will be an Advanced... button on the bottom left.&nbsp;&nbsp;Click on this button and a new form view will appear.&nbsp;&nbsp;Click on the Specs.. button and this is where the specifications that are available in your database are listed.&nbsp;&nbsp;You should see Tab1 thru Tab5.&nbsp;&nbsp;Since the procedure doesn't bomb out I assume they exist.<br><br>But the problem you're experiencing may be in the Connection, which I don't see in the code you've provided so far.<br><br>PaulF

 

[wls64]

Paul, thanks for the idea about checking the Import.&nbsp;&nbsp;When I did I did not have 'Text' as one of my file types....after investigation I discovered that while the Text driver was loaded the dll or the registry entry was bad.&nbsp;&nbsp;A trip to the MS web site reavealed that this is a common problem.&nbsp;&nbsp;(Search for ISAM drivers under Access 97)...after I renamed the dll and reinstalled the drivers it worked.<br><br>Thanks for your help Brian and Paul!

 

[PaulF]

Glad to see you got it working<br><br>PaulF