Macro to change data structure.

[Guest]

I'm trying to create a macro to automate a process of running a make table
query from tables I have linked with ODBC. Access interprets the data type
as text. They are intended to be used as date/time.

The following are the steps I tested so far:
SetWarnings No (table will be overwritten)
OpenQuery as datasheet in edit mode
OpenTable in design view in edit mode
SendKeys to tab 28 times, change data type to date/time, tab 3 times, change
data type to date/time
Close and save the table.

The problem I have is once the table attempts to save there are 4 records
violating the date/time type. And a DeleteObject action appears in my macro
with a D in the comment column. What would one suggest to accept this dialog
proceed and lose the 4 records and complete the close process of the table
without warnings and user intervention?

Thank you for your help.

 

[Nikos Yannacopoulos]

Shar,

I personally despise SendKeys, for it's got a personality of its own and
does anything but what it was intended to once too often... and, believe
me, I'm not the only one! Whatever one might use it for, there's a
better, more robust way to do. In your case, for instance, you could
change a table field type programatically. In your case, though, you
needn't do this at all, what you need is to create the field as
Date/Time in the first place, and here's how:

Make a query on the linked table, including a calculated field on the
date field instead of the original field; use function CDate or
DateSerial on the original field, so it returns it as a true date. Add
one more calculated field on the original date field, using function
IsDate; this will return True (-1) on the valid dates, and False (0) on
the invalid ones, so you van filter on it to only return records where
the value returned is True. Now make this query your Make-Table query,
and the job is done!

To get rid of the standard warnings, precede the OpenQuery action in
your macro with a SetWarnings one, with argument Off. Though not
necessary (warnings are automatically reset to On after the macro
execution ends), it is good practice to do so in the macro by means of a
second SetWarnings at the end, with argument On. The logic behind it is
so that, should you convert the macro to VB code at some point, and run
it as such, the warnings are reset at the end of the procedure, which,
unlike macros, does not automatically happen in code.

HTH,
Nikos

 

[Guest]

Thank you Nikos, your suggestion did the trick.

Shar,

I personally despise SendKeys, for it's got a personality of its own and
does anything but what it was intended to once too often... and, believe
me, I'm not the only one! Whatever one might use it for, there's a
better, more robust way to do. In your case, for instance, you could
change a table field type programatically. In your case, though, you
needn't do this at all, what you need is to create the field as
Date/Time in the first place, and here's how:

Make a query on the linked table, including a calculated field on the
date field instead of the original field; use function CDate or
DateSerial on the original field, so it returns it as a true date. Add
one more calculated field on the original date field, using function
IsDate; this will return True (-1) on the valid dates, and False (0) on
the invalid ones, so you van filter on it to only return records where
the value returned is True. Now make this query your Make-Table query,
and the job is done!

To get rid of the standard warnings, precede the OpenQuery action in
your macro with a SetWarnings one, with argument Off. Though not
necessary (warnings are automatically reset to On after the macro
execution ends), it is good practice to do so in the macro by means of a
second SetWarnings at the end, with argument On. The logic behind it is
so that, should you convert the macro to VB code at some point, and run
it as such, the warnings are reset at the end of the procedure, which,
unlike macros, does not automatically happen in code.

HTH,
Nikos