Query Propeties and VBA (1 Viewer)

[faradhi]

I have an Append Query that updates an external database.

Since it is possible that the Destination DB may Move I would like to change the Query property "Destination DB" with VBA.

Can Anyone tell me how to do this.

 

[doulostheou]

The following example was found in the Access help files. For more detailed information search for destination in access help:

INSERT INTO Clients IN 'C:\Data\Clients.mdb'
SELECT Customer.[CompanyName], Customer.Phone
FROM Customer;

 

[raskew]

Microsoft provided a particularly poor example to resolve this situation.

For a more dynamic solution, you might try:

PARAMETERS [Enter the database] Text;
INSERT INTO Clients ( CompanyName, Phone ) IN 'Enter the database'
SELECT Customers.CompanyName, Customers.Phone
FROM Customers;

At the prompt you would enter something like C:\programs\widget\myapp.mdb, at which point all would go well so long as "C:\programs\widget\myapp.mdb exists. If not, it bombs. However, this may give you the starting point you need to experiment further.

Bob