Web Development

Ask something
Discussion Locked

How to save Access Queries

0 helpful
7 replies

by dave butler

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We have a custom built database that consists of an Access 2002 frontend to a SQL Server 2000 database. In order to distribute changes to the program (not the data) to local desktops, our consultant has implemented a login script that overrights the users mdb file each time they login. The problem with this is that users lose any queries they have built.

How can we allow the users to keep their personal queries without loading the production with extaneous stuff?

I would appreciate any tips, including useful links to sites.

Thanks,
Dave

7 replies

by dklippert

Quick take:
View the Query as SQL.
Copy the SQL code to a text file save and then later copy and paste into the new MDB.

by bob sellman

Create a table in the SQL back end with the UserID, a field to contain the name of the query, a field that contains the connection string, and a note field that will contain the actual text of the query. You might also want to have a description field where a user can put more information about the query.

When a user creates a new query and selects an option to save it, write the data listed above to the SQL table ("UserQueries"). This will let you do a few things:

1. Use one MDE (or MDB) file for your front end that you keep on the server.

2. When a user creates a new query (or selects one of his existing queries to execute), just delete a query you call "MyQuery" and recreate it using the user defined query, then execute it.

3. When you need to update users, just copy your new application database over the existing one. (Of course, everyone needs to be out of the database for the time it takes to do this).

Or, if you continue with the method you are currently using, the users will still not lose any of their personal queries.

Whichever method you use for updating the front end application, each user's personal queries will only be available to that user, since they are identified with the userid.

You probably should write two procedures, one to retrieve the specific query data and the other to modify or add a query.

by dave butler

Thanks for the tip. Though I have not tried this yet, it is certainly the most viable response received. I will pass it on to our programmer and ask that he contact you after his assessment and/or implementation.

This discussion is closed to new replies.