Access 16: how to schedule export of table data to a text file daily at specific time (1 Viewer)

akika

Registered User.
Local time
Today, 14:31
Joined
Aug 7, 2018
Messages
102
heya,

pls help..
I have an access application with several tables & forms & reports..
I need to generate daily @05:00am and 22:00pm all data from 1 table customer_tbl in a text file in a specific folder //tsfshare/customer_track/
on a share drive.

how to do this scheduling?
 

akika

Registered User.
Local time
Today, 14:31
Joined
Aug 7, 2018
Messages
102
can u pls assist on how to do that?
m new in that .. never created macro in access & scheduling tasks.
Do u hv a DB sample plz
 

plog

Banishment Pending
Local time
Today, 16:31
Joined
May 11, 2011
Messages
11,611
No, I don't have a sample. Start googling:

Task Scheduler
Microsoft Access export
Microsoft Access macro
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:31
Joined
Feb 28, 2001
Messages
27,001
I don't have a sample either.

However, one thing to be aware of when using macros - don't forget to include a Quit at the end of the macro.

One thing to be aware of when using Windows Task Scheduler - by default the operation will run as user SYSTEM, which might be restricted if you are at a large commercial site or government site. For smaller private sites, less of an issue - but it is never something to be ignored. If your database is user sensitive, you might have an issue to consider based on how the app is being launched.

I'll also add to plog's suggestion: Read up on Access command line options (switches) and in particular, option "/x:macroname"
 

ncwatt

New member
Local time
Today, 21:31
Joined
Feb 14, 2019
Messages
7
You could use a VBScript which is run from Task Scheduler. I did create one for you which worked with the attached database to produce the attached text file.

I did have to ensure I had the Microsoft Access Database Engine Redistributable installed otherwise the VBScript would fail because it couldn't find the provider.

Here's the code in the VBScript.

Code:
Dim connStr, objConn, getNames, objFile
'''''''''''''''''''''''''''''''''''''
'Define the driver and data source
'Access 2007, 2010, 2013 ACCDB:
'Provider=Microsoft.ACE.OLEDB.12.0
'Access 2000, 2002-2003 MDB:
'Provider=Microsoft.Jet.OLEDB.4.0
''''''''''''''''''''''''''''''''''''''
connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\OneDrive\Documents\test\test.accdb"

'Define object type
Set objConn = CreateObject("ADODB.Connection")

'Open Connection
objConn.open connStr

'Define recordset and SQL query
Set rs = objConn.execute("SELECT * FROM customer_tbl")

' Open a text file for writing
Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile("E:\OneDrive\Documents\test\customers.txt",2,true)

' Loop through all records in the recordset
Do While Not rs.EOF
	objFile.WriteLine(rs.Fields(0) & "," & rs.Fields(1) & "," & rs.Fields(2) & "," & rs.Fields(3))
	rs.MoveNext
Loop

' Close the text file
objFile.Close

'Close connection and release objects
objConn.Close

Set objFile = Nothing
Set rs = Nothing
Set objConn = Nothing

Hopefully this will get you on the right path.
 

Attachments

  • test.accdb
    556 KB · Views: 132
  • customers.txt
    85 bytes · Views: 117

Users who are viewing this thread

Top Bottom