Link fe to PWD protected be via VBA!

[TheAceMan1]

Howdy All! . . .

How can I link an access frontend to a password protected access backend [blue]thru VBA?[/blue]

The be data can come from four different be DB's (four copies of the be in different locations, just different data! . . .

I can do this thru the [blue]Linked Table Manager[/blue], however a need to switch backends thru VBA has brought me to this issue . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[boblarson]

Check out the

OpenDatabase Method

in the VBA Help file. You can pass the password in that.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com

 

[CaptainD]

I have not done this for a while so I thought I would play with it.

I created a simple password protected DB

I created another DB with a form and command button to run through the module that I added.
(The text from the module is a modified version from a VB6 module)

Here is the module information

Option Compare Database


Function GetRS(strSql As String) As Recordset
    'Get the database name from the .ini file

    Dim db As Database
    Dim rs As Recordset
    Dim dbName As String
    
        dbName = CurrentProject.Path & "\PasswordProtectedDB.mdb"
    
    'Check to make sure the database is there
    'If Len(Dir(DBName)) < 1 Then
    '    CreateDB
    'End If
    'Debug.Print strsql
    'Stop
    
    Set db = OpenDatabase(dbName, False, False, ";pwd=password;")

    Set GetRS = db.OpenRecordset(strSql, dbOpenSnapshot)
    
End Function

Here is how I call the recordset from the click event of the command button

Option Compare Database

Private Sub cmdGo_Click()
Dim rs As Recordset
Dim strSql As String

strSql = "SELECT LastName, FirstName FROM tNames"

Set rs = GetRS(strSql)

While Not rs.EOF
    MsgBox "This is: " & rs.Fields("LastName").Value & ", " & rs.Fields("FirstName").Value
    rs.MoveNext
Wend
Set rs = Nothing
End Sub

 

[TheAceMan1]

Howdy CaptainD . . .

I don't want to open the backend. I just want to link the backend tables to the frontend! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

To All . . .

Problem solved (I knew I had the syntax somewhere in my library). The following is just the idea of the syntax:

[blue]   Dim db As DAO.Database, tdf As DAO.TableDef
       
   Set db = CurrentDb
   Set tdf = db.TableDefs("[purple][b]TableName[/b][/purple]")
      
   With tdf
      [green]'Use line below for non-password protected backend[/green]
      .Connect = "MS Access;DATABASE=" & NewPath
      [green]'Use line below for password protected backend[/green]
      .Connect = "MS Access;DATABASE=" & NewPath & ";PWD=[purple][b]yourpassword[/b][/purple]"
      .RefreshLink
   End With
   
   Set tdf = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[CaptainD]

If I recall, all this does is pull in the records requested and does not leave it "open" when done.

You could pass the name of the database you want along with the SQL string and would not need to link.

Note: This came from a VB 6 program so there was no "Link" capability.

 

[CaptainD]

Is there an advantage to "Linking" the databases?

 

[TheAceMan1]

CaptainD . . .

In my case it has to do with [blue]split Db[/blue], which is normally done when the Db is to reside on a network.

Have a look here Why Split a Database?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[stevefutcher]

To Ace-Man

I have a similar problem, and was wondering where you need to put the code. Can it run once on startup, up do you need to include it anytime you open a form or run a query using the linked tables?

Thanks

Steve