Import Marco 2 questions

[Guest]

Need advise,
1. built macro that imports Excel spreadsheets into Access. Using only the
Swicthboard for user interface (they can not see database windows). Want to
alert user if import results in an error table. Can I add a line to the
marco that will show user import error table if one is created during the
import process while ignoring the statement if no error table is created.
2. how do I get the import macro command "TransferSpreadsheet" to create a
primary key. Right now I can't and have to execute a delete query to clear
the table before importing, however primary key begin at last record imported
(not 1) don't want to compact database each time.

 

[Steve Schapel]

Joeman,

1. In a VBA procedure you would be able to write code to check for the
existence of the import errors table after the import. As far as I
know, this can't be done directly with a macro, but there would be ways
to make it happen indirectly. For example, you could make a form based
on the import errors table (if it existed), and then use an OpenForm
action at the end of your import macro to open this form, with a
Condition in the macro, using the DLookup function, which would mean the
form would only open if the table exists. Hope that might give you
something to work on.

2. Sounds like you are talking about an AutoNumber field, right? I
guess you can do the TransferSpreadsheet into a temporary table, with an
AutoNumber field in it, and then run an Append Query based on this
temporary table, to put the data into the permanent table. The value
you would append into the primary key field of the permanent table would
be something like this...
[YourAutoNumberField]-DMin("[YourAutoNumberField]","YourTemporaryTable")+1

 

[Guest]

Brilliant!! Both will work perfectly. Thanks for your help!!

Joeman,

1. In a VBA procedure you would be able to write code to check for the
existence of the import errors table after the import. As far as I
know, this can't be done directly with a macro, but there would be ways
to make it happen indirectly. For example, you could make a form based
on the import errors table (if it existed), and then use an OpenForm
action at the end of your import macro to open this form, with a
Condition in the macro, using the DLookup function, which would mean the
form would only open if the table exists. Hope that might give you
something to work on.

2. Sounds like you are talking about an AutoNumber field, right? I
guess you can do the TransferSpreadsheet into a temporary table, with an
AutoNumber field in it, and then run an Append Query based on this
temporary table, to put the data into the permanent table. The value
you would append into the primary key field of the permanent table would
be something like this...
[YourAutoNumberField]-DMin("[YourAutoNumberField]","YourTemporaryTable")+1

--
Steve Schapel, Microsoft Access MVP

Need advise,
1. built macro that imports Excel spreadsheets into Access. Using only the
Swicthboard for user interface (they can not see database windows). Want to
alert user if import results in an error table. Can I add a line to the
marco that will show user import error table if one is created during the
import process while ignoring the statement if no error table is created.
2. how do I get the import macro command "TransferSpreadsheet" to create a
primary key. Right now I can't and have to execute a delete query to clear
the table before importing, however primary key begin at last record imported
(not 1) don't want to compact database each time.