J
JH
This is so bizarre.
My department is responsible for reviewing Information Systems from various
other departments within the company for policy/security compliance.
I built a (supposedly) simple database which I plan to use to track each
system review (or ticket) as it gets done. I'm also using it to generate
reports on systems due for review and the number of tickets closed, etc.
Call this the TicketDatabase
The system information is kept on a web application that all departments
have access to where they go and upload documentation and enter the pertinent
information. Call this the SystemDatabase. I do not have any backend access
to the SystemDatabase and can only get information from it in the form of an
excel summary sheet.
Because I am not the only person that uses the TicektDatabase, I wanted to
build it in such a way that even the most BASIC user could get in and perform
all the functionality... (i.e. updating the TicketDatabase with information
from the SystemDatabase).
To that end, I created a "magical" import button that will darn near do
everything except grab download the excel sheet from the SystemDatabase.
I wrote up really simple instructions how to get the excel spreadsheet out
of the SystemDatabase and where to save it on our server. The SystemDatabase,
by default, includes several rows at the top of the report that list who
downloaded it, when it was downloaded, etc. My instructions say to open the
“import.xls†file and delete the extra rows at the top.
And here is where things get weird. Without even saving the document (after
deleting the top 11 rows of fluff stuff), the user is able to go into Access
and push my little wonder button and the Import code will all execute
perfectly. The user can hit save and run it again and it will still run
perfectly. Now, if the user CLOSES the excel file and tries to run the
import code, it will ALWAYS return with “Run-time error ‘3274’: External
table is not in the expected format.â€
If you open the excel sheet back up, then try to run the import code, you
continue to get the same error even though NOTHING has changed.
When I do a debug, it always says my error is on my import line which is as
follows:
strSQL = "INSERT INTO import SELECT T1.* FROM [Excel
8.0;HDR=YES;IMEX=1;Database=S:\Concurrency_review_DB\import.xls].[import$A1:I800] AS T1"
While I’m OK with writing my instructions to tell the user to import only
while the excel sheet is open… I would *really* like to know what on earth is
going on with this. Am I missing something here?
My department is responsible for reviewing Information Systems from various
other departments within the company for policy/security compliance.
I built a (supposedly) simple database which I plan to use to track each
system review (or ticket) as it gets done. I'm also using it to generate
reports on systems due for review and the number of tickets closed, etc.
Call this the TicketDatabase
The system information is kept on a web application that all departments
have access to where they go and upload documentation and enter the pertinent
information. Call this the SystemDatabase. I do not have any backend access
to the SystemDatabase and can only get information from it in the form of an
excel summary sheet.
Because I am not the only person that uses the TicektDatabase, I wanted to
build it in such a way that even the most BASIC user could get in and perform
all the functionality... (i.e. updating the TicketDatabase with information
from the SystemDatabase).
To that end, I created a "magical" import button that will darn near do
everything except grab download the excel sheet from the SystemDatabase.
I wrote up really simple instructions how to get the excel spreadsheet out
of the SystemDatabase and where to save it on our server. The SystemDatabase,
by default, includes several rows at the top of the report that list who
downloaded it, when it was downloaded, etc. My instructions say to open the
“import.xls†file and delete the extra rows at the top.
And here is where things get weird. Without even saving the document (after
deleting the top 11 rows of fluff stuff), the user is able to go into Access
and push my little wonder button and the Import code will all execute
perfectly. The user can hit save and run it again and it will still run
perfectly. Now, if the user CLOSES the excel file and tries to run the
import code, it will ALWAYS return with “Run-time error ‘3274’: External
table is not in the expected format.â€
If you open the excel sheet back up, then try to run the import code, you
continue to get the same error even though NOTHING has changed.
When I do a debug, it always says my error is on my import line which is as
follows:
strSQL = "INSERT INTO import SELECT T1.* FROM [Excel
8.0;HDR=YES;IMEX=1;Database=S:\Concurrency_review_DB\import.xls].[import$A1:I800] AS T1"
While I’m OK with writing my instructions to tell the user to import only
while the excel sheet is open… I would *really* like to know what on earth is
going on with this. Am I missing something here?