Replies

If you need to program this (if it has to be done regularly) try:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, “tbl_name”, (“Filename.xls”), True

The Questionnaire DB is a single DB. The Benchmarking DB is a FE/BE Database, with all reporting being done from the FE. When staff are completing the questionnaire they won’t have access to the network most of the time. In general, people doing the questionnaire will not have access to the benchmarking FE.

At this stage I don’t want to use ADO because this is getting rolled out next week, and I’m going on vacation for a month afterwards. The person who will be supporting the DB knows even less about access than I do, and any time I’ve played with queries and ADO, they aren’t visible in the db window. Is there a way of modifying a query using ADO that will leave it visible? The non-pilot version I’ll be creating when I get back should feature a bit more ADO.

Apologies if I’m being an idiot; I’ve only recently started developing in access again after doing practically nothing since v2.0; and some of it (particularly ADO) hasn’t quite sunk in yet.

Ta
Niall

Sorry if my explanation was a bit hairy.. I’ll try again;

We have a questionnaire database that people will bring to clients with them on their laptops to complete. When they come back into the office, they will need to upload the results to a Benchmarking database. depending on which client the questionnaire was completed for and which office they check back into. I have three queries which will move the data, but my attempts to set the sourcedb and destinationdb through vba don’t seem to work.

What I would like to do is ask the user what database they wish to upload the data to (using the FileOpenSave dialog) , and then change the source or destination databases on three queries to that database. And then run those queries.

Thanks
Niall