ImportExportText Macro Deletes File

[bdmoran]

Hello,

I created a Macro with 2 steps:

1. OpensQuery
2. ImportExportText

This worked perfectly during testing on my local machine. However, when I tried to implement on a server downstairs, the importexporttext portion deletes the file I created instead of exporting the data. I get the error:

Microsoft Office Access cannot find the object '|'.@Make sure the object exists and that you spell its name correctly.

I checked my file path numerous times (S:\SuiteAccess.txt) and I'm sure this is not the issue. I'm thinking maybe it has to do with server connections or something. I don't get the file is there before I run the Macro and then after I run the macro, get the error message, the file I created is no longer there.

If anyone has any insight on this that would be great. Thank you.

 

[lameid]

Sounds like it is a difference in pathing (you say you double checked but it is suspect that a Server would see S: or that it is definitively the same drive) OR permission like the account you are running from does not have permission to the folder (note a different account may have different mappings). Those are the obvious... then patch level and configuration like registry settings can change default behavior or fix issues.

I don't use macros as I cut my teeth back when VBA was simply the right way to do things with no discussion (I understand macros are more powerful now and VBA does not work with Sharepoint Extensions for Access) but I would expect there to be an option to specify whether you are overwriting or appending to the file, look it up and explicitly set it appropriately.

 

[bdmoran]

Thank you for the reply.

I'm not too familiar with VBA. My goal would be to either auto execute this code when the Database opens (which I would like to schedule daily) or auto execute this code at a specific time everyday. Is there a way to do this through VBA? From my research, the AutoExec command always pointed to a Macro -- which I am now trying to avoid. Please see code below. Any suggestions or thoughts for best practices would be great !


Sub ExportData()
DoCmd.OpenQuery "qry_Masterfile", , acReadOnly
DoCmd.TransferText acExportDelim, "1", _
"qry_MasterFile", "S:\SuiteAccess.txt"

End Sub

 

[lameid]

First you do not have to open a query to export it.

Sub ExportData()
DoCmd.TransferText acExportDelim, "1", _
"qry_MasterFile", "S:\SuiteAccess.txt"

End Sub

Secondly there are two ways to make stuff happen in access when it opens, the Autoexec Macro and a Startup Form.

The Autoexec macro has the advantage of being an object over a database property so if you ever import all your objects (maybe something is corrupt) you don't have to worry about remembering which form is the startup form and remember to set it. Also a macro has less overhead than a form. To execute code from a macro you use the runcode action... I think your code has to be a function, doesn't have to actually return anything but a function nonetheless.

To do something on a form, you have two options, a startup event like ON OPEN or ON LOAD; or the ON TIMER Event. These events fire when the condition is met like opening or loading the form or after the time in the timer property has elapsed (repeats). Because you have an object that will stay open, you can have code that runs periodically, kind of like a schedule but Access does have to be open and remain open for the event to keep running. Some people are 100% against having macros and go this route. As stated above, I think it is cleaner to not rely on the start up property for when things go wrong in a bad way not that they necessarily will but one less thing to remember in a crisis is always a good thing.

That said, if all you really want to do is export data from a query, you might look into writing a simple .Net application to do it (Express versions of Visual Studio are free). Then you could schedule a command line program to fire in the Windows scheduler and not worry about the overhead of Access. Likely you could search forums to find examples or maybe source for a more robust version. I'd be muddling my way through this option and might be a bit much to try to tackle if you don't code at all.