Export results from a Query

[Guest]

I'm trying to export results from a Query in the delimited format by running
a macro, using the transfer text method in the macro. When the macro runs it
posts a "Database or Object" is read only. I am unable to find the setting
to correct this. I would also like to export this data at a specific time of
day and also on demand. Any help would be appreciated.

Jon

 

[John Nurick]

Hi Jon,

If the text file has a non-standard extension (e.g. somehthing other
than .txt, .tab, .csv) you're likely to get that "read only" error
message.

To run it on demand, put a commandbutton on a form and set it to run the
macro when clicked.

Exporting it at a specific time of day gets more complicated. Do you
want this to happen even if Access is not running at the time?

 

[Guest]

Hi John
Thanks for responding. I run this query and direct the ouput to a folder.
I don't actually have a file created at this point that this data is
appending to. My thinking is that it should create the file. Maybe the
folder name also needs to include a "Name" for the file? In the transfer
text settings I have it directing the output to C:\certbib for the folder
name. The name of the query is "tmp". If I just run the query and export
the results to this folder from the query it works just fine, there will be a
file in the folder named "tmp". I have saved this as a specification from
the query and put it in on the transfertext settings but it still has the
same error.

I was thinking of having this data output at midnight each day. My thinking
was to not have the DB open. Do you think it would be possible to create a
VB program that could reside on the desktop and use the Windows task
scheduler to run this?
I'm just starting out in VB so I have a ways to go before I could compently
write the code to make this happen.

Jon

 

[Guest]

Hi John
Just wanted to let you know I found what I was doing wrong. I was not
including a file name after the folder to export to. Using another macro and
putting a short cut to it on the desktop I can point to this shortcut with
the windows scheduler and have the transfer done each day.
Thanks a lot for the nudge.

 

[John Nurick]

Hi Jon,

Access will create the file but you have to specify the filename in the
macro as well as the folder, e.g. "C:\certbib\tmp.csv".

For unattended export when Access isn't running, I'd avoid using Access
if possible. Instead, I'd set up a Windows scheduled task that launches
a VBScript to do the export, using OLE automation and the DAO library to
work with the database file without using Access itself. (Most queries
will work all right this way; parameter queries and queries that use
custom VBA code won't).

Here's a sample VBScript that exports the query qryTest from the
database to C:\temp\MyFile.csv. Paste it into a text file with a name
ending .vbs, and modify as required. You'll find information in Windows
Help and on the web about calling a script from a scheduled task.


'Sample VBScript to export data from an MDB database to a CSV file
'without opening Access

'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const QRY_NAME = "qryTest" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END

 

[Guest]

Hi John
I was able to get this to work the way we needed it. Now have a new
situation. I put in a larger drive and reloaded Win2k on it, rebuilt the DB
as it was and now when I try to run this macro shortcut on the desktop it
works good but it posts a dialog box on the desktop stating that it can't
find the shortcut. You have to click ok to remove the dialog box but it does
not affect the way the macro runs. This DB is very small so I can move it
over to my XP machine via a jump drive and run it the same way and it works
just fine without posting that error on the desktop.
Any thoughts?
thanks,
Jon

Hi Jon,

Access will create the file but you have to specify the filename in the
macro as well as the folder, e.g. "C:\certbib\tmp.csv".

For unattended export when Access isn't running, I'd avoid using Access
if possible. Instead, I'd set up a Windows scheduled task that launches
a VBScript to do the export, using OLE automation and the DAO library to
work with the database file without using Access itself. (Most queries
will work all right this way; parameter queries and queries that use
custom VBA code won't).

Here's a sample VBScript that exports the query qryTest from the
database to C:\temp\MyFile.csv. Paste it into a text file with a name
ending .vbs, and modify as required. You'll find information in Windows
Help and on the web about calling a script from a scheduled task.


'Sample VBScript to export data from an MDB database to a CSV file
'without opening Access

'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String

Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const QRY_NAME = "qryTest" 'Query or table to export

'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"

wscript.echo strSQL
oDB.Execute strSQL

oDB.Close
'END

Hi John
Thanks for responding. I run this query and direct the ouput to a folder.
I don't actually have a file created at this point that this data is
appending to. My thinking is that it should create the file. Maybe the
folder name also needs to include a "Name" for the file? In the transfer
text settings I have it directing the output to C:\certbib for the folder
name. The name of the query is "tmp". If I just run the query and export
the results to this folder from the query it works just fine, there will be a
file in the folder named "tmp". I have saved this as a specification from
the query and put it in on the transfertext settings but it still has the
same error.

I was thinking of having this data output at midnight each day. My thinking
was to not have the DB open. Do you think it would be possible to create a
VB program that could reside on the desktop and use the Windows task
scheduler to run this?
I'm just starting out in VB so I have a ways to go before I could compently
write the code to make this happen.

Jon