TransferSpreadsheet acImport: can you add data UNSOLVEABLE???

[eighthman11]

Hello everyone Using Access 2000 and SQL Server 2008.

This problem probably has no solution but I'll ask it anyway.

My Access application needs to transfer data from an excel spreedsheet
to a table on SQL server. That in itself is not a problem.

Here are the conditions:
1) the excel spreadsheet is always in the same format but could be
named anything so I can't link the spreadsheet to the access
database. The user selects which excel spreadsheet to import from a
combo box on the form.
2) I can not change the current format of the spreadsheet nor can I
add additional columns to the spreadsheet.

Here's the problem:
I need to transfer the data to a SQL Server table but I need to add
some additional data (constants) to the table during the transfer
[ i.e. CurrentUser(), TimeStamp etc]. If linking was an option this
wouldn't be a problem. But it seems I'm forced to use
"TransferSpreadsheet".

Any help appreciated. Radical ideas welcomed. I'm stuck.

Thanks

Ray

 

[Dirk Goldgar]

Hello everyone Using Access 2000 and SQL Server 2008.

This problem probably has no solution but I'll ask it anyway.

My Access application needs to transfer data from an excel spreedsheet
to a table on SQL server. That in itself is not a problem.

Here are the conditions:
1) the excel spreadsheet is always in the same format but could be
named anything so I can't link the spreadsheet to the access
database. The user selects which excel spreadsheet to import from a
combo box on the form.
2) I can not change the current format of the spreadsheet nor can I
add additional columns to the spreadsheet.

Here's the problem:
I need to transfer the data to a SQL Server table but I need to add
some additional data (constants) to the table during the transfer
[ i.e. CurrentUser(), TimeStamp etc]. If linking was an option this
wouldn't be a problem. But it seems I'm forced to use
"TransferSpreadsheet".

Any help appreciated. Radical ideas welcomed. I'm stuck.

I don't see why this would be unsolveable. Link to the spreadsheet (using
TransferSpreadsheet acLink, ...), and have a stored append query that
selects the data from the linked spreadsheet table and also defines
additional, calculated fields. The stored query would be appending its data
to the SQL Server table.