I can still see hidden Access tables in Excel......

[aaronb50]

I have my Access Tables hidden but I just realized that in Excel, on the Data tab, then clicking on From Access, I can select the program and then select the tables.

If I already have them hidden, how else can I go about stopping Excel from seeing them?

 

[aaronb50]

And actually I can see the tables from other Access programs as well.

I thought hiding the tables meant no outside Access programs could see them?

 

[sneuberg]

I believe encrypting the tables with a password is the only way to prevent that.

 

[aaronb50]

If I encrypt them with a password, can users still modify them using a front end?

 

[aaronb50]

Ok that worked.

But now the front end cant get into it.

Can I hard code the front with the password?

 

[aaronb50]

So I cant see linked tables if they are hidden.

This is strange.

As of now, all I can think of is to create a copy of my back end, delete and relink all the tables and link the front end to it and try to hide that real back end.

 

[sneuberg]

This video shows you how to split and password a database. In the video you will see that you need to delete the linked tables and reestablish them.

If you open the backend directly in code with an open state you will need to add a password to that too.

 

[aaronb50]

I will check this out first thing in the morning.

Thanks!!

If you hide the tables, why can you still see them from other programs if they are local tables but not linked tables?

 

[aaronb50]

Well that kind of work. I was able to link to the tables but I cant use the code to access them.

Dim rst As DAO.Recordset
Dim db As DAO.Database

Set db = OpenDatabase(BackEndDBPath)

On the OpenDatabase I get Not a valid password error 3031.

There has to be a way to hard code the password.

 

[aaronb50]

Ok figured it out.

Set db = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")

But I'm going to have to put this all over the place.

I cant seem to get it to work when I declare BackEndDBPath in my module.

Ideally I would like to just place it there one time.

 

[sneuberg]

Try something like

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.WorkSpace
 Dim rst As DAO.Recordset
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
 False, False, "MS Access;PWD=northwind")
 Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
 If rst.RecordCount > 0 Then
   rst.MoveLast
   MsgBox rst!CustomerID
 End If
 rst.Close
 db.Close
End Sub

Which is from this site

 

[sneuberg]

Ok figured it out.

Set db = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")

But I'm going to have to put this all over the place.

I cant seem to get it to work when I declare BackEndDBPath in my module.

Ideally I would like to just place it there one time.

I'm with you. You'd want to do this in one place. Please show me the complete module code. Are you returning the database object?

 

[sneuberg]

Why are you accesses the backend directly so much. Is it a performance issue?

 

[aaronb50]

This is the code I'm using to declare BackEndDBPath in the module.
And where I want to put the Password just the once.

 Public Const BackEndDBPath = "C:\Users\t598\Desktop\EAR\EARProgramDatabase1.0.accdb"

And I can access the passworded BE by using

 Dim rst As DAO.Recordset
    Dim db As DAO.Database
        
    Set db = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")
    Set rst = db.OpenRecordset("Users", dbOpenDynaset)

But that is all over the program.

This program in particular is used to create and track Engineering Action Request.

The front end allows:

Manufacturing Engineers to Create them

Production management to review them before they are sent to engineering review.

Engineering review looks them over and assigns them to an Engineer to take action.

They get disposition and signed off By engineering and production management and then when the drawing is updated they fall out of the system.

Every step of the way I have to access and modify the records on the backend.

 

[sneuberg]

Every step of the way I have to access and modify the records on the backend.

But this

Set db = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")
Set rst = db.OpenRecordset("Users", dbOpenDynaset)

could be

Set db = CurrentDb
Set rst = db.OpenRecordset("Users", dbOpenDynaset)

If "Users" is a linked table but that doesn't make much difference now. It looks like you are stuck with changing it all over the place. If you do a project wise search on OpenDatabase and with some copy and paste it probably won't take you too long.

 

[aaronb50]

No way to add the password in here?

 Public Const BackEndDBPath = "C:\Users\t598\Desktop\EAR\EARProgramDatabase1.0.accdb"

 

[sneuberg]

I'm not 100% sure of this but try putting

Public Function GetDb() As DAO.Database

GetDb = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")

End Function

in the module where you have

Public Const BackEndDBPath = "C:\Users\t598\Desktop\EAR\EARProgramDatabase1.0.accdb"

and then in place of

Set db = OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")

put

Set db = GetDb()

 

[sneuberg]

If that function in my last post results in an object not set error change it to;

Public Function GetDb() As DAO.Database

Dim db as DAO.Database
Set db =  OpenDatabase(BackEndDBPath, False, False, "MS Access;PWD=ps")
GetDb = db

End Function

 

[aaronb50]

I see where you are going with that and it looks like it should work but I would still have to go back and change that string all over the program.

I think I will just do a search and replace for the whole program.

This one will be easy but so are opening up 4 different tables at the same time. I will have to make sure I go through them carefully to get them all.

I have a way forward so I'm happy with that. Thank you so much!!!!!!

I never did figure out why the local hidden tables are still visible though but not the linked ones. I think that's really dumb!!!!