Destination DB Property (2000 (9.0.3821 SR1))

Topic

New Reply

Hi,

I am developing an application in Access 2000 and am having a problem with setting the DestinationDB and sourcedatabase properties for an Append query from code.

I have tried:

strFilter = ahtAddFilterItem(strFilter, “Access Files (*.mdb)”, “*.mdb”)
strexportFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=False, _
DialogTitle:=”Select the Benchmarking Database…”, _
Flags:=ahtOFN_HIDEREADONLY)

queries![qry_uploadauditdetails].destinationdb = strexportFileName
queries![qry_uploadanswers].destinationdb = strexportFileName
queries![qry_LastUploadCompanyNumber].sourcedatabase = strexportFileName

DoCmd.OpenQuery “qry_UploadAuditDetails”
DoCmd.OpenQuery “qry_UploadAnswers”

The need for this function arises from our need to benchmark audit results which will be captured by a number of people.

At this stage, the only way I can see to get around this is to delete the existing append query and create a new one each time this is run.

Regards and thank

Niall Eddery

Reply | Quote

Replies

I don’t quite understand what you’re doing. I’m guessing that you’re using the common dialog call to pop up the FileOpenSave dialog, but does that mean that you are NOT using linked tables? This whole thing would be easier with ADO if you are not using linked tables, but it isn’t clear exactly what you’re doing. [indent]

---

The need for this function arises from our need to benchmark audit results which will be captured by a number of people.

---

[/indent] This doesn’t really explain anything. Why are you trying to append data to an arbitrarily chosen database and how do you expect to guarantee that the target database even has the table you want to append to?

Reply | Quote

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

Reply | Quote

Are these databases single mdbs, not split FE/BE? Is there a reason you’re not using ADO to do this?

Reply | Quote

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

Reply | Quote

Queries created with ADO are hidden because MDBs don’t have a collection of Views or Stored Procedures and ADO doesn’t know about queries. It still sound to me like it would be easier to do it a different way. Why not export the data to a mailmerge format, which is tab delimited text? You could build an import/export spec to handle the text file and then import the text files from the Benchmarking database. Otherwise I’d say it would be easier to temporarily link a table fron the target database to your questionnaire db and append to that instead of trying to set the destination database. I know others have wrested with setting a destination database, but I can’t remember ever trying that method. It’s always been simpler to create a temporary link.

Reply | Quote