Can this Access VBA code be performed by an Access Macro?

Question

I have successfully created a VBS file that Windows Task Scheduler can use to export data from my database, by triggering an Access macro. Now i need to import - most steps are simple and can be achieved in the Access macro but I need to re-create table links first (field names and positions often change in the source files and it seems to mess things unless i first delete and re-create the links.) Here's the VBA code - is there a corresponding macro action that i have missed?

'delete and recreate links to Account and Company
Dim db As DAO.Database

' Re-link the CSV Table
Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "Contact":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="Contact", _
    FileName:="c:\db\contact.csv", HasFieldNames:=True
db.TableDefs.Refresh

On Error Resume Next:   db.TableDefs.Delete "Account":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="Account", _
    FileName:="c:\db\account.csv", HasFieldNames:=True
db.TableDefs.Refresh

db.Close
Set db = Nothing

Christian Specht Christian Specht · Accepted Answer · 2013-08-15T20:34:58

Yes, the macro action you missed is called Run Code.

The only caveat is that Run Code can only call VBA functions. So you can't just paste your code in the macro. Instead, you need to put it into a VBA function in an MS Access module, like this:

Public Function ReCreateTableLinks()

    'delete and recreate links to Account and Company
    Dim db As DAO.Database

    '... paste the rest of your code here

End Function

By the way, are you saying that you are using Windows Task Scheduler, which calls a VBS file, which opens the Access database and executes the macro?

Am I understanding this correctly?
If yes: you don't need the VBS file at all if it does nothing but open the Access database.
You can just execute the Access database directly with Windows Task Scheduler.
If you name the macro autoexec, it will execute automatically when the Access database opens.

Can this Access VBA code be performed by an Access Macro?

1 Answers