TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp table (1 Viewer)

ScottT

Registered User.
Local time
Today, 14:20
Joined
Mar 29, 2010
Messages
13
TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp table

I am using the following code to import an Excel 2003 spreadsheet with a single worksheet (ie: one tab only) into a security-enabled Access 2003 database.


DoCmd.TransferSpreadsheet acImport, , TEMP_SIMPLE_MASTER_TABLE, sFileName, True


TEMP_SIMPLE_MASTER_TABLE is a string constant naming the table to import into. This table is static, it is never dropped (deleted) but rather cleared before every import.

When I log in as admin, the import works fine, there are some "sheet1_$importerror" files created - fair enough.


When I log in as a custom user "testmasterload" though, that belongs to a group with significantly reduced permissions, I get the error message:

"Error: could not create; no modify design permission for table or query 'Sheet1$_ImportErrors"

The testmasterload user belongs to "Master Loaders Group" which has the following permissions:

Database: Open/Run
Table | New Tables/Queries: Read Design/ Modify Design + CRUD


Can anyone tell me what minimum permissions I have to grant for these import error tables to be created? I cannot give the group "administer" level rights.


This is where I expect you will ask why I want to compensate for import errors. Simple, the field that causes import error is a calculated field, contains a VLOOKUP and no matter if I use text(255) or memo datatypes which I consider to be catch-all, an import error is still created. This import error table NEEDS to be created but I don't want to give the user too many rights.
 

DCrake

Remembered
Local time
Today, 14:20
Joined
Jun 8, 2005
Messages
8,632
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

What happens if you create the table yourself first as an admin person then when the normal user runs it Access won't need to have the necessary permissions to create the table in the first instance as it already exists.
 

ScottT

Registered User.
Local time
Today, 14:20
Joined
Mar 29, 2010
Messages
13
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

What happens if you create the table yourself first as an admin person then when the normal user runs it Access won't need to have the necessary permissions to create the table in the first instance as it already exists.


Hi David - the issue is that the table "SheetN$_ImportErrors" (where N is any number from 1 upwards) is dynamically created by the TransferSpreadsheet command, not by me. However that table can only be created with the correct privileges, and I dont know what they are.

Even if I did create "Sheet1$_ImportErrors", Access would try to create a table named "Sheet2$_ImportErrors" and still fail.

I wish there was a way of specifying to TransferSpreadsheet "do not create import error tables" but there's no such option.
 

DCrake

Remembered
Local time
Today, 14:20
Joined
Jun 8, 2005
Messages
8,632
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

Why do have the NEED to allow errors to exist in source spreadsheet that will effectivly create the import errrors table. Surely if you eliminate the error at source you will overcome this problem.
 

ScottT

Registered User.
Local time
Today, 14:20
Joined
Mar 29, 2010
Messages
13
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

Why do have the NEED to allow errors to exist in source spreadsheet that will effectivly create the import errrors table. Surely if you eliminate the error at source you will overcome this problem.

Because the fields causing errors are VLOOKUPs. I tried memo datatypes, text, the fields just will not convert and so I'm resigned to having errors. The contents of the VLOOKUP fields are discarded anyway - I do not process them in my app - but they need to be catered for during the transferspreadsheet operation.

I am thinking maybe I should open the spreadsheet using ADO instead, and read the data row by row. Transferspreadsheet's just taking too much of my time.
 

DCrake

Remembered
Local time
Today, 14:20
Joined
Jun 8, 2005
Messages
8,632
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

In your import spec can you not choose to ignore this column? thus eliminating the error.
 

ScottT

Registered User.
Local time
Today, 14:20
Joined
Mar 29, 2010
Messages
13
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

In your import spec can you not choose to ignore this column? thus eliminating the error.

Import Spec?

No parameter of DoCmd.TransferSpreadsheet resembles an import spec; do you mean named range? No that wouldn't work either. The fields to be imported are not contiguous. There's plenty of unused columns mingling with the used ones.

I suppose I could ask the client to delete the column in the sheet before importing but tbh I'd like to know what permissions TransferSpreadsheet needs in case its a simple fix.
 

DCrake

Remembered
Local time
Today, 14:20
Joined
Jun 8, 2005
Messages
8,632
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

Can you post a sample spreadsheet to look at? Include the erronous column in it.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:20
Joined
Sep 12, 2006
Messages
15,613
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

surely the transferspreadsheet imports the values, not the formulas

the problem is that the import is expecting (say) the values to be numbers, and some of them are instead #name or some such - so they are errors.

you probably have a create table permission you can add.
generally your users will need reasonable permissions on tables to use them anyway, wont they?
 

ScottT

Registered User.
Local time
Today, 14:20
Joined
Mar 29, 2010
Messages
13
Re: TransferSpreadsheet gives me "Error; could not create" for ImportErrors temp tabl

Can you post a sample spreadsheet to look at? Include the erronous column in it.

Unfortunately I can't post the spreadsheet - it's confidential data.

I think the issue is that Access (or the Jet engine) is analysing the first 10 rows of data and establishing data types for columns from that. If the data types Access thinks I need "change" after the 10th row and can't map to the table being imported into (remember, its not a temp table, its a static table that is emptied before every import), Access tries to creat the import errors table and thats when the permissions problems start.

Believe it or not, I'm just going to save the file as a .CSV and import it with a specification via TransferText, because I really don't have the time to try figure out Access's mystical .XLS import functionality.

I'll let you know how this goes.

Thanks for your help.
scott
 

Users who are viewing this thread

Top Bottom