Question How to convert select into vba code

A

ahmedaliazad

Registered User.
Local time
Today, 14:40
Joined
Aug 10, 2018
Messages
29
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
 
Why bother? Select queries can't be "run".
 
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
 
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.
 
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
Click to expand...

Tick the 'hidden' property on the querydef.
 
so as you mentioned it is not a good idea if I convert the select queries into vba?
 
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.
 
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
Click to expand...

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.
 
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.
Click to expand...

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!
 
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,
 
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.
 
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?"
 
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.
 
You must log in or register to reply here.

Similar threads

A
convert macro to VBA
Replies
3
Views
183
tvanstiphout
T
H
How to run accde database into accdr (Microsoft Access Runtime Application)
Replies
4
Views
422
Hamatto
H
A
Convert tables from Access to SQL
Replies
4
Views
195
Pat Hartman
P
N
Solved Converting a longer date string into short date
Replies
8
Views
189
nector
N
V
Wired Extruder with Stepper Motor i3 Mega S
Replies
2
Views
97
Jon
Jon

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom