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.

Mike,

You should be using DAO or ADO to retrieve the data from Access. This does
not require that the entire Access application be opened, so you shouldn't
get a runtime error if someone else has the database open. You can read the
data from the Employees table into a Recordset object, which you can then
copy to the desired Excel range using the CopyFromRecordset method in Excel.
Doing some google and groups.google.com searches on "Access", "ADO",
"CopyFromRecordset", and "Excel" should get you some relevant information.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mike wrote:
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.