I finally figured out the issue and have come up with a work around, but I'm hoping someone here has a less klugie solution.
I normally link to spreadsheets, not import them, but starting with a test spreadhsheet pre-filled by the user, I found some bad data (a period only (.) entered into a cell in a numbers column and a space (" ") used to delete a number in a cell instead of the Delete key in a numbers column). The showed up in the linked Access table as #NUM! and I was trying to find a way to trap these errors in Access and present them to the user, but I could never search out a successful way to do that (ISERROR didn't work). Basically, I found most Google results ultimately saying to import the data as text first and then operate on it from there.
So, I imported the data and worked on it as text and can find the errors and present them to the user on a form. I say on the form to open the spreadsheet, make the corrections, and then return to this form and click the "Reset Data" button. In the "Reset Data" On Click event, I was simply trying to open a "wait" form, close the "errors" form, reimport the spreadsheet using RunSavedImportExport, and then re-run through the routine to check for errors. That all *seemed* to work until I realized the import was not actually happening.
After quite a bit of Google searching, I tried (1) recreating the SavedImport spec and (2) using a SavedImport to append rather than replace the existing table. Try #1 did not work any better and try #2 would not import a large amount of the data due to "Type Conversion Error" because I couldn't find a way to indicate the import spec field types in the Append method. But, what I did figure out was that, with the RunSavedImportExport in the autoexec macro, the import *did* work when the application first opened. Hm.
I thought maybe RunSavedImportExport would work if I deleted the existing table first, so I did a lot of Google searching to find out how to delete a table. Here's where the light bulb really came on. When I ran the procedure to delete the table, I got an MS error message saying the table couldn't be locked because it was already in use. Really? I don't have the table open and the first thing I did in my VBA procedure was to close the form. Hm. So, when I stepped though the procedure, what appeared to be happening was that even though I had closed the form, because I was initiating the procedure from a button on the form, the form was never really fully closing (or at least the table associated with the query providing the data to the form was never really released) until the procedure had tried to perform *all* the steps and finally returned to "End Sub" of the "Reset Data" button procedure.
With that in mind, I tried only closing the "error" form and opening the "wait" form from the "Reset Data" on the "error" form, and then putting the rest of the steps in the On Open event of the "wait" form. Still it won't let loose of the import table. So, I finally just did the open "wait" form and close "error" form from the "Reset Data" button (renaming it to "Initialize Data Reset") on the "error" form; then I added a button on the "wait" form to "Complete Reset". By fully ending any action that got initiated from the "error" form, the import table seems to be released/unlocked so that the RunSaveImportExport actually will perform the import. But it certainly seems klugie to me that I would have to make the user click 2 separate buttons on 2 separate forms to get this thing to work, so I'm hoping maybe somebody here has a prettier solution (that's not overly complicated).
Thanks for any ideas/suggestions.
I normally link to spreadsheets, not import them, but starting with a test spreadhsheet pre-filled by the user, I found some bad data (a period only (.) entered into a cell in a numbers column and a space (" ") used to delete a number in a cell instead of the Delete key in a numbers column). The showed up in the linked Access table as #NUM! and I was trying to find a way to trap these errors in Access and present them to the user, but I could never search out a successful way to do that (ISERROR didn't work). Basically, I found most Google results ultimately saying to import the data as text first and then operate on it from there.
So, I imported the data and worked on it as text and can find the errors and present them to the user on a form. I say on the form to open the spreadsheet, make the corrections, and then return to this form and click the "Reset Data" button. In the "Reset Data" On Click event, I was simply trying to open a "wait" form, close the "errors" form, reimport the spreadsheet using RunSavedImportExport, and then re-run through the routine to check for errors. That all *seemed* to work until I realized the import was not actually happening.
After quite a bit of Google searching, I tried (1) recreating the SavedImport spec and (2) using a SavedImport to append rather than replace the existing table. Try #1 did not work any better and try #2 would not import a large amount of the data due to "Type Conversion Error" because I couldn't find a way to indicate the import spec field types in the Append method. But, what I did figure out was that, with the RunSavedImportExport in the autoexec macro, the import *did* work when the application first opened. Hm.
I thought maybe RunSavedImportExport would work if I deleted the existing table first, so I did a lot of Google searching to find out how to delete a table. Here's where the light bulb really came on. When I ran the procedure to delete the table, I got an MS error message saying the table couldn't be locked because it was already in use. Really? I don't have the table open and the first thing I did in my VBA procedure was to close the form. Hm. So, when I stepped though the procedure, what appeared to be happening was that even though I had closed the form, because I was initiating the procedure from a button on the form, the form was never really fully closing (or at least the table associated with the query providing the data to the form was never really released) until the procedure had tried to perform *all* the steps and finally returned to "End Sub" of the "Reset Data" button procedure.
With that in mind, I tried only closing the "error" form and opening the "wait" form from the "Reset Data" on the "error" form, and then putting the rest of the steps in the On Open event of the "wait" form. Still it won't let loose of the import table. So, I finally just did the open "wait" form and close "error" form from the "Reset Data" button (renaming it to "Initialize Data Reset") on the "error" form; then I added a button on the "wait" form to "Complete Reset". By fully ending any action that got initiated from the "error" form, the import table seems to be released/unlocked so that the RunSaveImportExport actually will perform the import. But it certainly seems klugie to me that I would have to make the user click 2 separate buttons on 2 separate forms to get this thing to work, so I'm hoping maybe somebody here has a prettier solution (that's not overly complicated).
Thanks for any ideas/suggestions.
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
