TransferSpreadsheet Plus a Function

[Cathy]

I am presently using a TransferSpreadsheet command to import information from
an Excel spreadsheet to a table.

On this table I have a field to capture the UserID. I am getting the UserID
by using the following function:

Public Function UserID()

UserID = Environ("USERNAME")

End Function


I can not use this function as a default in the table. I haven't been able
to figure out a way to attach it to the TransferSpreadsheet. I can use a
Modify Table query to apply it after the data is loaded, but then there is
risk if two users are importing their data at the same time. (This is a real
risk for this table because many users have to import their information
within a small window of time.) We don't want the users to add their UserID
to their spreadsheets. We wanted this to be done in the background as an
audit trail.

What other options do I have to import the UserID with the data?

Thank you,
Cathy

 

[PieterLinden via AccessMonster.com]

I am presently using a TransferSpreadsheet command to import information from
an Excel spreadsheet to a table.

On this table I have a field to capture the UserID. I am getting the UserID
by using the following function:

Public Function UserID()

UserID = Environ("USERNAME")

End Function

I can not use this function as a default in the table. I haven't been able
to figure out a way to attach it to the TransferSpreadsheet. I can use a
Modify Table query to apply it after the data is loaded, but then there is
risk if two users are importing their data at the same time. (This is a real
risk for this table because many users have to import their information
within a small window of time.) We don't want the users to add their UserID
to their spreadsheets. We wanted this to be done in the background as an
audit trail.

What other options do I have to import the UserID with the data?

Thank you,
Cathy

I think your only option is to link to the spreadsheet and use a query to
append the data. Then you can add the GetOSUserName() function to the query
without a problem.
http://www.mvps.org/access/api/api0008.htm

 

[Cathy]

Pieter,

Thank you for your response, but unfortunately that won't work either. We
will have many users importing their data. It wouldn't be feasible to link
to all of the spreadsheets.

Is there a way that I can lock the table between users?

Thanks again,
Cathy

 

[pietlinden]

Pieter,

Thank you for your response, but unfortunately that won't work either.  We
will have many users importing their data.  It wouldn't be feasible to link
to all of the spreadsheets.

Is there a way that I can lock the table between users?

Thanks again,
Cathy

If you have to import all the data in a short period of time, you
could have a temporary or holding table in each user's front end.
Import directly into that, update with the username, then append to
the final table in the back end. Then who imports when won't matter,
because all the "incomplete" data will only be on the user's front end
database.

 

[Cathy]

That would be a perfect idea if it was a quick load of data. Instead it was
taking over 15 minutes to load data to a local table, and is taking over 5
minutes now that we switched to an Oracle table.

Thank you so much for trying.

Cathy

 

[Guest]

pp>