I've been trying to automate in Visual Basic the transfer
of data from Excel to Access. I have created an Excel
spreadsheet that multiple users use at the same time.
They import data from Access (using MS Query and it works
real well) and they produce output that needs to be sent
back to the same Access database. I have written VB code
in Excel to open the Access database and to use the
Transferspreadsheet Method from Access to pull in the
data. This works great provided the database is not
already opened by the user or someone else. The problem
that I have is that either the user gets an error message
that the database is opened by someone else and is it
locked out to them, or the changes in the spreadsheet are
not transfered but the data on the currently saved
version on the server is transfered.

Is there a way to do this in a multiple user environment?

Here is some sample code that I am using.

Set appMS = GetObject("G:\Database.mdb")
appMS.Application.DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"Employees","G:\Excel\Template.xls", True, "A1:G12"
appMS.Application.Quit
Set appMS = Nothing

Any help would be greatly aprreciated.

Thanks.