Hidden check of user info

[kuipers78]

Hello,

I've created a DB with seperated Frontend and Backend (FE/BE). The table "Users" (stored in BE) should be consulted when a user opens the FE and tries to log in. This isn't a problem, I've written a login form and connected the table to my FE.

However, the problem is that I want my users to be able to see and use the tables in the FE, except the "Users" table... And because this table is connected they can see and alter it. I can make it hidden, but that doesn't do the trick because with a simple menu option they can still see it...

Is there a way (using VBA-code) to read information from the table "Users" (BE) without adding or connecting it directly to my FE?

Any help will be greatly appreciated!

 

[ted.martin]

How about making a new Linked Users Table in the FE and then use DLOOKUP. Once you have the linked table in the FE you can design a filtered query for the DLOOKUP to to use as appropriate.

Might work for you.

 

[mattkorguk]

I have been having a look at this and you could perform a simple macro to link to the table required and then use a second macro to remove the link after you're performed the check you need to.
I've been trying it on a similar Db here, my problem is that I have a query based on my users table on my main start-up form?! So that's not going to work for me but the principle sounds right.

 

[kuipers78]

@ Ted Martin: correct me if I'm wrong, but your suggestion means that I still have to link the table to my FE. And that means that users can see the linked table between the other ones, which is exactly what I don't want...

@ Matt Korg: I don't use any macros at all. If it's that easy, then it's indeed possible with VBA-code as well. Any suggestions?

 

[mattkorguk]

OK, this will link in the table, but there is no vba to remove the link (according to Microsoft) so you'll have to use a macro.

DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\Databaselocation.mdb", acTable, "tableName", "What you'd like to call the table"

Microsoft site;

http://support.microsoft.com/kb/209730

 

[kuipers78]

Matt, thanks for your replies; very much appreciated

However, it's not really elegant to link the table and drop it after obtaining information from it... I was hoping there was a way to call it directly from the BE, but that's not very logical 'cause then you wouldn't have to link your tables...

I'm still hoping that there are other solutions, so if anyone has ideas I'd really like to hear them!

 

[Bungle]

You could use a pass-through query rather than linking the table. Connect a pass-through query to the BE database and save the connect string then paste the SQL you use to query the user table from code as below, run it, then clear or overwrite it afterwards to leave no trace.

paste SQL
dbs.QueryDefs("qMyPassThroughQuery").SQL = tMySQL

run query and do something with result

Obliterate SQL
dbs.QueryDefs("qMyPassThroughQuery").SQL = "nothing interesting here!"

 

[kuipers78]

Looks like a good solution to me Bungle, but could you be a bit more specific, please? How can I call a BE query with code in my FE? (that's what you're suggesting, right?)

 

[Bungle]

No your pass-through query will be on the FE and will link to the BE, but it doesn't link to any specific table, just to the database, so you can use it to select anything you want from the BE. Only when you paste the SQL does it know what table it's selecting from, and deleting the SQL then loses all links to the table. The users table will only be visible if someone manages to view the query during the very brief time it's populated, which if your code is efficient will be a second or less. You can also hide the query for added security.

 

[kuipers78]

Allright, it sounds very good but despite your explanation I still find it hard to understand how to create this.
Could you please post or email an example DB? If you don't want to (I can understand that) I have a few more questions which I hope you are willing to answer.
Thanks a lot for your provided information so far!

 

[kuipers78]

Allright, do I have to create this pass-through query among the query objects or in the VBA-code? How do I link it to my BE without adding tables to it? I have really no idea...

 

[Bungle]

Create a new query in your FE and save it. Don't add any tables, just leave it blank and save it. Now find the path of your BE database eg R:/Server/MyDb.mdb
To use your new query without storing any links just paste the SQL as in previous post but with an 'IN' clause to specify the path
eg SELECT MyFields FROM MyUsersTable IN 'R:/Server/MyDb.mdb';
Make sure you put the ' ' round the path.
Afterwards set the SQL to "" or anything you like, as above, and there will be no link left to any tables.

 

[kuipers78]

Yeah, now it works! Again, thanks a lot Bungle! You're great!