Hi Blake,
I believe this is the problem. As much as I hate macros in Access (ie. "Macros are for
weenies, code is cool"), this is a case where a macro can be used to help start the
process. Note: You can also use a form, which calls your function in its On_Open event
procedure.
You can create a macro which runs a function. The code must be a public function. It
cannot be a private function or a subroutine (public or private). The action argument in
the macro is RunCode, and you specify the function name in the lower window with a blank
set of parentheses if the function requires no arguments. Example:
Action: RunCode
Function Name: Export()
Test this much out first, and make sure you can get it to work from within Access. Then
drag your macro to the desktop (or any other folder). This should produce a shortcut to
the macro. Close Access. Test it outside of Access by double-clicking on your new
shortcut. It should still work. You may want to add code to the function to cause Access
to quit on it's own. Rename the shortcut to whatever you want, and then add the shortcut
file as a scheduled task.
Tom
___________________________________________
Option Compare Database
Option Explicit
Function Export()
On Error Resume Next 'Probably best, if you are running it unattended
Dim objAccess
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase ("C:\codescratch\db1.mdb")
objAccess.DoCmd.TransferText acExportDelim, "Expout Export Specification", "tblEmployees",
"C:\codescratch\out2.txt", False, ""
objAccess.CloseCurrentDatabase
Set objAccess = Nothing
DoCmd.Quit ' Quit Access
End Function
"Blake Patterson" <bl***@blakespot.com> wrote in message
news:90**************************@posting.google.c om...
YOU WROTE:
Very strange. I copied the code you show below into a new module,
created the folder
C:\codescratch, and a comma delimited export spec. with named Expout
Export Specification.
Your code works fine for me.
- - - - - - -
But I am not using it in a module. This is code is in a .vbs script
file sitting on the Windows desktop. I am trying to execute it by
clicking on the icon and running it. Is this my problem? You see, I
need to have Windows' task scheduler schedule this operation to run
nightly, which is why I am trying to use it in a .vbs script file.
Any thoughts? Thanks.
Code below:
' --- BEGIN CODE ---
dim objAccess
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase("C:\codescratch\db1. mdb")
ObjAccess.DoCmd.TransferText acExportDelim, "Expout Export
Specification" ,"table1", "C:\codescratch\out2.txt", False, ""
ObjAccess.CloseCurrentDatabase
Set ObjAccess = Nothing
' --- END CODE ---
.. . . . . . . . . . . . . . . . . . . . . .
"Tom Wickerath" <AO***********************@comcast.net> wrote in message
news:<Mc********************@comcast.com>...
Very strange. I copied the code you show below into a new module, created the folder
C:\codescratch, and a comma delimited export spec. with named Expout Export
Specification. Your code works fine for me.
_____________________________________________
"Blake Patterson" <bl***@blakespot.com> wrote in message
news:90**************************@posting.google.c om...
Did you ever get this worked out?
I am running this script:
' --- BEGIN CODE ---
dim objAccess
Set objAccess = CreateObject("Access.Application")
ObjAccess.OpenCurrentDatabase("C:\codescratch\db1. mdb")
ObjAccess.DoCmd.TransferText acExportDelim, "Expout Export
Specification" ,"table1", "C:\codescratch\out2.txt", False, ""
ObjAccess.CloseCurrentDatabase
Set ObjAccess = Nothing
' --- END CODE ---
...and it complains it can't find out2.txt. Why does it need to FIND
this file when it's doing an EXPORT is what I do not understand. The
specification is properly defined and when I use it in Access to do an
export to delim text file all goes well. But this .vbs script will
not write to the indicated file.
If I create a blank text file with that name and put it in the proper
location, I get no error and the script completes fine, but no write
occurs to that file. I can't understand why it is not working. Any
suggestions? Thanks.
bp
"Tom Wickerath" <AO***********************@comcast.net> wrote in message
news:<jK********************@comcast.com>... Exporting to Word Merge Format After Renaming Field Causes Error
http://support.microsoft.com/default...roduct=acc2000
__________________________________________
"Tim Eliot" <ti*@beacon.com.au> wrote in message
news:23**************************@posting.google.c om...
Just wondering if anyone has hit the following issue and how you might
have sorted it out.
I am using the command:
DoCmd.TransferText acExportMerge, , stDataSource, stFileName, True
after setting stDataSource and stFileName to the desired values. Most
of the time it works, but occasionally, typically as code changes are
being made to the module, the following message appears:
"The Microsoft Jet database engine could not find the object
<stFileName>. Make sure the object exists and that you spell its name
and the path name correctly."
Once this happens the file specified in stFileName can never be used
in an export again, whether through the above command, or exporting
directly from the query named in stDataSource or any other query.
Even if I go to a completely new Access database the message will
still come up if I attempt to export to that file name.
The problem is only with Access - all other applications seem to be
able to write to the file. Also, it is only with the file in the
exact path specified in stFileName; move to another directory and the
program will work for a while.
For now I get around the problem by continually changing an
unimportant part of the file name, but that is a rather inelegant
solution. If someone has a better solution I wouldn't mind giving it
a try.
Ta for your help
