users want ad hoc queries/reports

[scratch]

Hi,

How can I separate my application so the users can still use queries and reports on a db? I don't want them touching the tables or application directly so it looks like I need a way to create a separate application or something along those lines that is linked to the tables that the users want to query. Any suggestions?

Thanks,

scratch

 

[Pat Hartman]

You can create a new empty db with links to the existing tables. They will be able to update data unless you have secured the be but they won't be able to modify the structure. Then they can build what every they want and save it in their own personal database. NEVER let them save their own objects in the main database because that will make an updating nightmare for you if you want to replace the fe with a changed version and they want you to save their "stuff".

 

[scratch]

Hi Pat,

They will be able to update data unless you have secured the be but they won't be able to modify the structure.

I don't want them updating data either so I have to "secure the be". Do you mean back-end? And how do you secure it so they can only update the tables through the main application and not through the empty db that will house the ad hoc queries? I'm working only with Jet and nothing else.

scratch

 

[ghudson]

Then you need to transfer [export] the data table(s) to the "users" ad-hoc db so that they do NOT have access to the live data in your db.

be = back end
fe = front end

 

[Pat Hartman]

Rather than linking to the be tables, you can import the data each time the user opens his ad hoc database. The data will be almost current.

Don't forget to set the compact on close option for this db because constant deleting and importing data will cause severe bloat.

 

[scratch]

Rather than linking to the be tables, you can import the data each time the user opens his ad hoc database.

How do you set that up? The only way I know how to import tables is through the get external data option which means that I have to rely on the users to do that each time which I don't want to do. Can this be automated?

scratch

 

[Pat Hartman]

Use the TransferDatabase method to import the tables. If you are not comfortable with VBA, create a macro and then convert the macro to VBA. Copy the generated code and put it in the Open event of the database's startup form. This form can be hidden if the user isn't going to use it so he won't even know what is going on unless you tell him. The only thing is the db will take longer to open than others given the amount of work going on in the background.

 

[scratch]

      DoCmd.TransferDatabase acExport, "Microsoft Access", _
              "C:\test.mdb", acTable, "Clinic", "Clinic", False

This works fine for one table but I want to get all the tables to be ported over at once so do I have to runthe DoCmd.TransferDatabase each time for each table or is there way to adjust the args so they are all ported over? And are the relationships/referential integrity kept intact for the tables that are imported?

scratch

 

[ghudson]

This might push you in the right direction... Export All Database Objects Into Text Files

 

[scratch]

Thanks for the help. I can import all the tables and also delete them all. The only potential problem I see is that I can't bring the relationships in. Since the point of bringing all these tables to a seperate db is so the users can query them, do I really need to bring the relationships in? If I need them, is there any way of bringing the relationships?

Another thing: is there any worry about not bringing the tables that have "MSys" or "~" at the beginning of their names?
scratch

 

[ghudson]

I do not know how to transfer releationships from one db to another.

No, you NEVER want to bring a system [MSys] table or a temp [~] table into another db.

 

[Pat Hartman]

There is no reason to copy the relationships since you are not updating the data. The thing that the users loose is the automatic join that Access does when you create a query that joins two related tables. The user will always need to create the join himself. If this becomes a problem, you'll need to write some VBA to discover the relationships in the primary database and create them in the user's database.