need help with syntax for docmd.transfertext, ontimer event

[trasco]

Hello,
I am trying to get a csv file, todaysptappts.csv to import into an access
table, Todaysptappts, twice a day at a certain time. Each time the file
will overwrite the table.
The database will be left open 8a-5p. It's on a network, in a different file
folder than the file I want to import. I have gotten a manual import to work
with a macro, using a file spec, "Todaysptappts Import Specification", but I
would like to attach this code to the database switchboard form, as an
ontimer event. I've tried writing code to get it to import once a day, for
starters, but get a syntax error .

My code is:

Private Sub Form_Timer()
If Time >= #4:24:00 PM# And Time < #4:27:00 PM# Then
DoCmd.TransferText(TransferType=acImportDelim,
Todaysptappts_Import_Specification,todaysptappts,"\\filefolder1\filefolder2\
filefolder3\filefolder4\filefolder5\filefolder6\filefolder7\todaysptappts.
csv",0,,)
Else
Cancel
End If
End Sub

Can someone plz point me in the right direction??

 

[Tom van Stiphout]

Remove the parentheses. Put a space between TransferText and
TransferType.

-Tom.
Microsoft Access MVP

 

[JimBurke via AccessMonster.com]

Just make sure your timer interval value is set so that you'll be guaranteed
to come within your specified timeframe once and only once, otherwise you
could miss it or end up sending the file more than once. e.g.,if you wanted
to use the timeframe of 4:24; to 4:27 I think you could use a timer interval
of something like 2 minutes (this would be a timer interval of 120000, since
they are specified in milliseconds) - this should make sure that you fall
within the interval once and only once.

 

[JimBurke via AccessMonster.com]

Whoops, faulty logic there. You'll need to keep track of whether the file was
sent for each interval. You probably want something like this:

define module-level boolean varibles, e.g. fileSentInt1, fileSentInt2

then in the On Timer event, assuming you have intervals of 9:00 AM to 9:03 AM
and 4:24 PM to 4:27 PM and an interval of 1 minute (interval = 60000):

If Time >= #9:00:00 AM# And Time < #9:03:00 AM# Then
if not fileSentInt1
do your import...
fileSentInt1 = true
end if
Else
If Time >= #4:24:00 PM# And Time < #4:27:00 PM# Then
if not fileSentInt2
do your import...
fileSentInt2 = true
end if
Else
If Time > #9:03:00 AM#
fielSentInt1 = false
end if
If Time > #4:27:00 PM#
fielSentInt2 = false
end if
End If
End If

Just make sure your timer interval value is set so that you'll be guaranteed
to come within your specified timeframe once and only once, otherwise you
could miss it or end up sending the file more than once. e.g.,if you wanted
to use the timeframe of 4:24; to 4:27 I think you could use a timer interval
of something like 2 minutes (this would be a timer interval of 120000, since
they are specified in milliseconds) - this should make sure that you fall
within the interval once and only once.

Hello,
I am trying to get a csv file, todaysptappts.csv to import into an access

[quoted text clipped - 21 lines]

Can someone plz point me in the right direction??

 

[trasco via AccessMonster.com]

Thanks for the thoughtful suggestions - I will attempt. I forgot to ask,
is there a command to bypass the access warning, 'You are about to run a
delete query.. Are you sure you want to run this type of query?' I have a
similar event that will append data, so I don't want to use the Kill command
(?)

Whoops, faulty logic there. You'll need to keep track of whether the file was
sent for each interval. You probably want something like this:

define module-level boolean varibles, e.g. fileSentInt1, fileSentInt2

then in the On Timer event, assuming you have intervals of 9:00 AM to 9:03 AM
and 4:24 PM to 4:27 PM and an interval of 1 minute (interval = 60000):

If Time >= #9:00:00 AM# And Time < #9:03:00 AM# Then
if not fileSentInt1
do your import...
fileSentInt1 = true
end if
Else
If Time >= #4:24:00 PM# And Time < #4:27:00 PM# Then
if not fileSentInt2
do your import...
fileSentInt2 = true
end if
Else
If Time > #9:03:00 AM#
fielSentInt1 = false
end if
If Time > #4:27:00 PM#
fielSentInt2 = false
end if
End If
End If

Just make sure your timer interval value is set so that you'll be guaranteed
to come within your specified timeframe once and only once, otherwise you

[quoted text clipped - 9 lines]

 

[JimBurke via AccessMonster.com]

Try this:

Application.SetOption "Confirm Action Queries", False

You probably want to issue that when your appl starts up (in the Open event
of your main form) - you typically don't want those messages showing up in a
production environment.

Thanks for the thoughtful suggestions - I will attempt. I forgot to ask,
is there a command to bypass the access warning, 'You are about to run a
delete query.. Are you sure you want to run this type of query?' I have a
similar event that will append data, so I don't want to use the Kill command
(?)

Whoops, faulty logic there. You'll need to keep track of whether the file was
sent for each interval. You probably want something like this:

[quoted text clipped - 30 lines]