Question How to convert select into vba code (1 Viewer)

ahmedaliazad · Aug 17, 2018

Hello,

How I can convert the select queries to vba code in my access application,

I already converted the update and append queries to vba by using code:
docmd.runsql "SQL script for query",

but for select query this way is not working, are there any other way I can convert the select queries?

thanks

pbaldy · Aug 17, 2018

Why bother? Select queries can't be "run".

ahmedaliazad · Aug 17, 2018

its give me the error,

if I convert all select queries into vba code then I can remove the queries on my database to prevent users from importing it can you help bro

Frothingslosh · Aug 17, 2018

Also, keep in mind that running SQL statements via VBA rather than saved querydefs causes database bloat. If this is going to be your standard approach, the users will need to compact and repair their front ends fairly regularly unless you're going with the 'download a new front end every time' approach.

It also makes the query run more slowly, because it has to be compiled first every time.

Why are you going this route? If you don't want the users to have access to the queries, the first thing I'd suggest is to instead intercept any calls to the navigation pane. If that's not enough, mark all of your queries as 'hidden'. If THAT isn't enough, talk to Ridders - he knows some way to bury objects so they can't be found just by nav pane manipulation.

Frothingslosh · Aug 17, 2018

ahmedaliazad said:
its give me the error,

if I convert all select queries into vba code then I can remove the queries on my database to prevent users from importing it can you help bro

Tick the 'hidden' property on the querydef.

ahmedaliazad · Aug 17, 2018

so as you mentioned it is not a good idea if I convert the select queries into vba?

Frothingslosh · Aug 17, 2018

It causes bloat and decreases performance.

How about we take a step back and ask WHY you're doing this.

Are you actually having a problem with people stealing data?
Is this for work, for school, or are you trying to build an application to sell?
Is this some sort of security requirement?

Securing an Access database has a lot of steps to make it secure, and it can never be made fully 100% secure. Even if you create all data connections programmatically and save nothing but forms and modules, your connection requests (which by their nature will require any login and/or password info) can be intercepted by packet sniffers, and there are tools out there to decompile accde files.

If you need actual security on the data, then you need to use a different back-end than Access. I'm partial to SQL server, but really any modern database system would work. And like I said, if you wanted to seriously secure your front end, Ridders can provide help - he designs and sells a number of apps, and does this as a matter of course - but it's going to be involved and somewhat complex.

Mark_ · Aug 17, 2018

ahmedaliazad said:
if I convert all select queries into vba code then I can remove the queries on my database to prevent users from importing it can you help bro

Are you only concerned with queries? I'd expect you'd be much more concerned about any special code you've had to write or the actual data in tables.

Frothingslosh · Aug 17, 2018

Mark_ said:
Are you only concerned with queries? I'd expect you'd be much more concerned about any special code you've had to write or the actual data in tables.

Based on his answer in his other thread, I think he wants the user to be unable to see both queries and tables. He specifically said he was concerned about users importing them into blank databases. Specifically, this is for an application he intends to sell.

It is theoretically possible to build an Access front end with zero permanent links and queries - I have a copy of one that does that saved here at work - but it is a GIGANTIC hassle. Way more work than just hiding the tables and queries and locking down an accdb.

As it is, he's going to need to learn to disable the shift-bypass, turn off the nav pane, create a custom ribbon, update and check the windows registry, create registration keys and an installer program, figure out how patching is going to be handled, etc, and I *SO* do not have the time to teach all that. Hell, some of that I still need to learn!

ahmedaliazad · Aug 17, 2018

I did some of steps for securing my database like:

stop bypassing shift key
hide navigation pane
and some other security,

I tried to hide my query is working but when check the shw hidden objects on new access database then is show all queries to import,

are there anyway to secure my db, remember at the end I will make an accde file,

Frothingslosh · Aug 17, 2018

The only person I know for a fact can do what you're asking is Ridders. He knows some way to 'deep hide' queries and tables so that they can't be found by just showing hidden objects.

You need to be aware, though, that there is absolutely no way to absolutely secure an Access database file, even an executable. A determined enough person with the appropriate knowledge will find your queries and data no matter what you do.

And honestly, it really does sound like this might be a bit beyond your current VBA skill. I would strongly recommend working more on your overall VBA and Access ability before taking something like this on. Maybe even look into Microsoft certification first.

Mark_ · Aug 17, 2018

OK, who are you trying to prevent from accessing your query?
1) Your customers so they can't add additional users?
2) Your customers employees so they can't change their own permissions?
3) Prevent non-employees of your customers from accessing the data?

Depending on what portion of security you are working on, you should get different answers. This also leads into "Is this part of a site license system?"

June7 · Aug 17, 2018

Since SELECT queries aren't 'run', mostly only purposes for building them in code would be to dynamically set RecordSource and RowSource properties or to open DAO or ADODB recordset objects. Setting RecordSource and RowSource properties in design directly with SQL statements instead of referencing query objects can eliminate some.

I have encountered query structure that required another query as source and it had to be independent saved query, could not be nested.

Question How to convert select into vba code (1 Viewer)

ahmedaliazad

Registered User.

pbaldy

Wino Moderator

ahmedaliazad

Registered User.

Frothingslosh

Premier Pale Stale Ale

Frothingslosh

Premier Pale Stale Ale

ahmedaliazad

Registered User.

Frothingslosh

Premier Pale Stale Ale

Mark_

Longboard on the internet

Frothingslosh

Premier Pale Stale Ale

ahmedaliazad

Registered User.

Frothingslosh

Premier Pale Stale Ale

Mark_

Longboard on the internet

June7

AWF VIP

Similar threads

Users who are viewing this thread