Change Data Source each time we open database

[Krash878]

I have a database that we use to monitor user network traffic. I use a simple form and a query that is pointed at a .txt file for the information.

We have multiple sites and would like our database to ask us where the log file is instead of hard mapping the query. Each proxy server generates the same file.

How do I do this?

Krash

 

[CautionMP]

Krash878,
Instead of using a link table and query to support your form, dynamically set the Recordsource for the form to an SQL string that uses an IN clause.

Here is an example that opens the file [tt]Query1.txt[/tt] located in [tt]C:\Data[/tt] using the Query1 Link Specification.

SELECT *
FROM [Query1.txt]
IN "C:\Data" "Text;DSN=Query1 Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;";

To get all the details for the IN clause you can open the linked table up in design view, open the table properties, and look in the description field.
Or you can open MSysObjects (hidden table) and pull the [tt]Connect[/tt], [tt]Database[/tt], and [tt]ForeignName[/tt] fields for your linked table.

Once you have this you just need a mechanism that allows the user to change [tt]C:\Data[/tt] in the string to point to the correct log file location, you could use a file dialog or an API to do this. Then the updated SQL string you can set as the forms recordsource property when the form is opend.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)