Back end password issue

[mcdhappy80]

I was thinking about this.
I have a split database.
If i add password to back end what code must I use in front end to open the back end database sucessfully?

Thank You

 

[MarkK]

There are a couple of ways you might 'Open' a back-end database. You can ...
1) use the Application.OpenDatabase() method, which returns a DAO.Database object, and
2) link back-end tables to the front end.

With a password protected back end I have never successfully used the first method. Regardless of what I try I get a 'Not a valid password' error.
In my experience if the back-end has a database password, you need to link tables to the front end.

If you find a way around this let me know.

 

[MarkK]

and you can use the following function to create a linked table.

[FONT="Verdana"][SIZE="1"]Function CreateLinked(dbsDestination As DAO.Database, TableName As String, MDBSourcePath As String, Optional Password As String) As Boolean
[COLOR="Green"]'  creates a link to TableName in database MDBSourcePath[/COLOR]
On Error GoTo handler
   Dim tdf As DAO.TableDef
      
[COLOR="Green"]   'create the new tabledef object[/COLOR]
   Set tdf = dbsDestination.CreateTableDef(TableName)
[COLOR="Green"]   'connect to the .mdb file on disk[/COLOR]
   tdf.Connect = "MS Access;PWD=" & Password & ";DATABASE=" & MDBSourcePath
[COLOR="Green"]   'specify the table name in the .mdb[/COLOR]
   tdf.SourceTableName = TableName
[COLOR="Green"]   'append the table[/COLOR]
   dbsDestination.TableDefs.Append tdf
[COLOR="Green"]   'assign to function[/COLOR]
   CreateLinked = True
      
final:
   Exit Function
   
handler:
   err.Raise err, err.Source & " in CreateTableLink()"
   Resume final
   
End Function[/SIZE][/FONT]

 

[mcdhappy80]

and you can use the following function to create a linked table.

[FONT=Verdana][SIZE=1]Function CreateLinked(dbsDestination As DAO.Database, TableName As String, MDBSourcePath As String, Optional Password As String) As Boolean
[COLOR=Green]'  creates a link to TableName in database MDBSourcePath[/COLOR]
On Error GoTo handler
   Dim tdf As DAO.TableDef
      
[COLOR=Green]   'create the new tabledef object[/COLOR]
   Set tdf = dbsDestination.CreateTableDef(TableName)
[COLOR=Green]   'connect to the .mdb file on disk[/COLOR]
   tdf.Connect = "MS Access;PWD=" & Password & ";DATABASE=" & MDBSourcePath
[COLOR=Green]   'specify the table name in the .mdb[/COLOR]
   tdf.SourceTableName = TableName
[COLOR=Green]   'append the table[/COLOR]
   dbsDestination.TableDefs.Append tdf
[COLOR=Green]   'assign to function[/COLOR]
   CreateLinked = True
      
final:
   Exit Function
   
handler:
   err.Raise err, err.Source & " in CreateTableLink()"
   Resume final
   
End Function[/SIZE][/FONT]

If I understand this code correctly, if I would want to link more than one table from the back end I would need to call this function that many times and each time provide table name for different table?
Where In my front end database should I put this code? Maybe in On Load Event of the form that starts when the database opens?

Thank You

 

[MarkK]

Yeah, or if you have a macro named 'autoexec' it'll run first. In some cases I'll only link a table for the time it's required and then remove it when I'm done. No end of possibilities there.

 

[mcdhappy80]

Yeah, or if you have a macro named 'autoexec' it'll run first. In some cases I'll only link a table for the time it's required and then remove it when I'm done. No end of possibilities there.

I've tried this code, and it connects the BE that is password protected and that was what I was looking for.
What I noticed is that when I open it the second time I'm getting error message, that Table already exists.
That is true, but I want to know what would be the right thing to do about that.
Should I run this code only once and the disable it?
Or create a Select Case statement in error handler of the function which will ignore 3012 error?
To ignore the error I should put Resume Next right?

I tested it some more, and as I can see that once created connection works even after I disable the function call, but nevertheless I'm asking just to hear Your opinion too.

Thank You

 

[MarkK]

- There is no 'right' way. Any one of the ways you mention will work.
- Except: Generally it's better form to write code that performs logic without relying on error handlers, ie, check if the table exists before adding it rather than try to add it and handle the error.
- Consider that if your BE is password protected, yet persistently linked to your FE, you will have effectively defeated your password.
- My 2c is that a database password is not worth the hassle. Trust the people you work with, and make backup copies regularly.

 

[mcdhappy80]

- There is no 'right' way. Any one of the ways you mention will work.
- Except: Generally it's better form to write code that performs logic without relying on error handlers, ie, check if the table exists before adding it rather than try to add it and handle the error.
- Consider that if your BE is password protected, yet persistently linked to your FE, you will have effectively defeated your password.
- My 2c is that a database password is not worth the hassle. Trust the people you work with, and make backup copies regularly.

Thanks again for the advices. Cheers.