How to create queries using EXTERNAL ACCDB file?

[IgorOV]

Hi guys,
Just wondering what I should do to create a query using external ACCDB
In VBA Code, to add data to my DB, I use
Set db = OpenDatabase("D:\DB.accdb")
and then normal VBA commands.
Is there any way of creating a query using MSACCESS query wizard?
Please, may anyone give me a light on how I should proceed to create query using external ACCDB file?
I am not beginner on this, but it is being my first time using external DB
Regards,


Igor

[Dal Jeanis]

There are three ways that you can proceed -

1) You can LINK your database to the desired tables in the external database using the External Data tab's Import and Link group, and after that, just treat the tables just like any other table.

2) You can use VBA to build your SQL manually.

3) If you aren't confident in your SQL ability, you could also TEMPORARILY Import the external tables into a junk version of your database, and use the query wizards to build your query, then copy the resulting SQL into your VBA in your real database.

[IgorOV]

Hi Dal,

The problem having the tables linked is the user having the ability to access the tables through the "left frame", just giving a double click... I dont want that... Do you know any solution for that?

Actually I wanna use the query just to provide information to the reports, I am not confident with SQL, but yes I can try bringing the resulting SQL to the VBA in my DB

Can the SQL command bring/open information from another ACCDB file (the external)?

Thx

Igor

[Dal Jeanis]

Yes, use options 2 and 3 - bring the tables into a junk version of your database to work out your SQL, and then use VBA to open the database and run that query against it.

[IgorOV]

I dont want the user to have access to the table straight through the ms access
I want it to access the tables through the forms, where it will add data to the "edit" fields and then click in a button to run the VBA code.
That why I thought of having a separated ACCDB with password and then open it using VBA like,
Set db = OpenDatabase("D:\DB.accdb", False, False, "MS Access;PWD=password123")
with that I prevent the user to open the tables
creating the ACCDE I prevent the user to access my VBA code and forms design
Now I would like to learn how I can create queries using SQL or another way to bring information from the "DB.accdb" and fill the report


How can I run SQL code to open "DB.accdb" and then run the "SELECT"... I know a little bit of SELECT, but I dont know if I can open the DB.accdb to run the SELECT

Understand?

[Dal Jeanis]

I do understand. Let me ask another question first, though. How much do you need to keep the users out of this table? Access is not very secure, and if a user is smart and curious, he can get to anything that the database can get to.

If your users are either not smart or not curious, or if they have real work to do and won't be poking around much, then you can hide the linked table from them in the nav pane without too much work. Actually, I hide the entire nav pane and control the workflow myself, but that method is probably above your experience level.

[IgorOV]

I understand access is not "that" secure, but the users are not "that" smart too, however if the table is there, just shinning to the users, of course they will be curious enough to double click on them and check what happens
I would be very happy if you can teach me how you hide the nav pane and stuff....

Because the only way I found to control that, was by removing the MS ACCESS and installing just the RUNTIME, so they will only see the main form and what that form lets them go around

OR if there is a way of creating a query by running SQL commands which should open an. external accdb file.

Regards,

IGor

[IgorOV]

My main business is NETWORK SECURITY, but I like programming and sometimes I do things using msaccess to help my clients.

[Dal Jeanis]

The answer is yes to all of the above. You can hide the nav pane completely and control the user interface completely, or you can hide particular objects from the nav pane and lock the nav pane from being changed, or you can use VBA behind a form to attach to a database, create a query against the attached item, and assign that new query to the form's recordsource. I don't have a sample handy, but I'll look tomorrow.

[IgorOV]

Dal,
actually I would like learn how to open an. external accdb file through SQL command line in ACCESS or another way, but still having external DB file with the password
I found where I can hide NAV PANE or TABLES/QUERIES/FORMS and stuff.... HOWEVER I have not found HOW I can keep those hidden things using password, because the same way I did that, the curious user can un-do that.

If you have then that sample OR a tutorial I can read and learn, I would be very thankful.

Thanks,

Igor

[Dal Jeanis]

I'm not ignoring you, the first three methods I tried didn't work as expected. I was thinking that you could attach to a query or stored procedure in a second database that then accessed the backend, thus leaving the backend totally hidden. Didn't work.

Currently, I'm leaning toward the standard "lock down all the access and turn off the shift key" method. Unfortunately, I don't remember where anyone posted a recent comprehensive version of that.

[ItsMe]

Dal

Are you referring to the UDF ChangeProperty?
https://www.accessforums.net/securit...lbar-3536.html

Finding the correct combination seems to do the trick for my needs.

I also use the following to hide all of the Ribbon and toolbar etc. I then import a few custom shortcut menus and add custom controls to call menu items.

DoCmd.ShowToolbar "Ribbon", acToolbarNo

[IgorOV]

I think I found out how to solve that problem.
I dont know if it is the best way but worked form e.

I have all the tables saved in a safe DB with password and the main accdb with forms has an. empty copy of each table.
I create my query using VBA which accesses the safe table and copy to the respective empty table and then open the reports

the report uses : ON LOAD event to run query e copy the table contentes and then ON CLOSE event, it deletes everything from the local table and goes empty again

Is this way smart?

regards,

IGor

[ItsMe]

I guess that is one way to go about it.

For me, I just use linked tables. It sounds like you have a split db design already. The added thing about having to connect to multiple DB's should not be an issue in a split design.

FE copy, all of the users have forms and reports. BE DB's have the tables. On the FE, hide all of the queries, toolbars, Nav Pane, linked tables, etc. This is how I do it. I am having trouble relating to your dilemma.

Another option would be to create executable files for your FE's.

I don't see a way to use DAO to create a recordsource for your report.

[IgorOV]

I tested everything and it is working great
Main ACCDB (MADE: ACCDE)
- Forms
- Using VBA code to access the DB file with the DATA TABLES
- Reports
- Using VBA code in the ON OPEN event to run a query and fill the empty tables with data from DB file
- Using VBA code in the ON CLOSE event to empty the local tables
- Empty Copy of Tables and one table which has the DB location, for example: //server/db/db.accdb

DB Accdb file (Password protected)
- TABLES with all the data and also username / password


"ItsMe", you said something about creating executable file for my main file... you mean using another language, like VB Studio?

Regards,