Secure Queries through running in VB code

[Curious]

Hi people,

I've been considering how to setup my database to secure my queries.

In this case, the data in the tables is not so important, as the process that is used to filter the data and produce an output.

I was thinking of an alternative way to secure the queries, without going the route of setting up User Group security.

I had considered running all the queries through VB code, and then securing access to them by created an .MDE database version.

Am I dreaming, or is this a reasonable option to consider?

 

[llkhoutx]

Access and "Securing anything in Access" is an oximoron.

 

[jkl0]

It is doable. You need to convert your queries to SQL statements and use the SQL for your recordsource or filter (after you make sure all of your statements work, then you can delete the queries). Then, when you convert your Mdb to an Mde there is no code to access.

 

[Curious]

I have been slowly moving my SQL code to a VB string. So far, it hasn't been a straight process, and I had to read a lot of other forum examples to understand the correct syntax... and still I'm not getting the same query results for some queries.


llkhoutx, what would you suggest instead?


I suppose I should explain what I'm doing. Basically, I'm transferring a vertical data set into a horizontal data set. Here are my steps:

1. Import the raw data from an external data source (.xls file), vertical data set, and paste it as is into a table (Table1) that assigns an AutoNumber ID to each row.
2. Run a SELECT query (Query1) that grabs only the data I want from Table1.
3. Run a 2nd SELECT query (Query2) that further refines what I want from Query1.
4. Run a 3rd SELECT query (Query3) that grabs data from Query2 and using multiple DLookup & Dmin functions that reference the AutoNumber ID and lays out the data as a horizontal data set.
5. Run a CREATE TABLE query (Query4) that grabs that data from Query3 and puts it in a new table (Table2), which I can use as a data source for other applications.

I'm not sure if it is possible to do all this through VB. It is all working fine through using queries.