General discussion

Locked

How to save Access Queries

By Dave Butler ·
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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How to save Access Queries

by DKlippert In reply to How to save Access Querie ...

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.

Collapse -

How to save Access Queries

by Dave Butler In reply to How to save Access Querie ...

This works, but is too cumbersome, especially for 'non-techies'.

Collapse -

How to save Access Queries

by Glen_McLeod In reply to How to save Access Querie ...

Change the option to save the queries to SQL Server instead of storing them locally.

Glen

Collapse -

How to save Access Queries

by Dave Butler In reply to How to save Access Querie ...

This does not solve the problem of "loading the production (system) with extaneous stuff.

Collapse -

How to save Access Queries

by Bob Sellman In reply to How to save Access Querie ...

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.

Collapse -

How to save Access Queries

by Dave Butler In reply to How to save Access Querie ...

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.

Collapse -

How to save Access Queries

by Dave Butler In reply to How to save Access Querie ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums