Hi Tyven,
If you are manually linking, you should be prompted for the database
password. I can only assume that you are asking how to programmatically link
to a password protected database, since manual linking is a piece of cake.
Here is a function that I found on the internet several years ago, which I
just modified tonight to allow for passing in a database password. I have
only tested this code in Access 2003 (not Access 2007). Copy and paste the
functions shown below into a new stand-alone module. Save the module, and
then do a Debug / Compile to ensure that you do not have any compile errors.
Public Function LinkTable2(strDB As String, strPassword As String, _
strTBL As String, bolOverWrite As Boolean) As Boolean
On Error GoTo ErrHandle
'//======================
'// Creates a link in CurrentDB to table strTBL in strDB
'// If bolOverWrite = TRUE then strTBL will be over written
'// if it already exists.
'// Returns TRUE if successul, FALSE otherwise.
'//=======================
'// Written By: Steve Huff
'// Date: 1/11/04
'// Modified 10/19/2009 by Tom Wickerath to allow linking
'// to a password protected .mdb file
'
'// From Immediate Window:
'// ?LinkTable2 ("c:\temp\ReadTextFile.mdb", "STUFF", "tblDefault", True)
Dim db As Database
Dim tdf As TableDef
Set db = CurrentDb
strPassword = ";PWD=" & strPassword & ""
If TableExists("", strTBL) And bolOverWrite = True Then
db.TableDefs.Delete strTBL
Debug.Print "Bang!"
End If
Set tdf = db.CreateTableDef(strTBL)
tdf.Connect = ";DATABASE=" & strDB & strPassword
tdf.SourceTableName = strTBL
db.TableDefs.Append tdf
LinkTable2 = True
RefreshDatabaseWindow
Exit_Function:
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
LinkTable2 = False
Resume Exit_Function
Resume
End Function
Public Function TableExists _
(strDatabase As String, _
strTable As String) As Boolean
On Error GoTo ProcError
'//Written By: Steve Huff
'//Written: 7/18/02
' Comments : Determines if the named table exists in the named database
' Parameters: strDatabase - path and name of the database to look in
' or "" (blank string) for the current database.
' strTable - name of the table to check
' Returns : True - table exists, False - table does not exist
Dim db As Database
Dim intTableCount As Integer, x As Integer
If IsBlank(strDatabase) Then
Set db = CurrentDb()
Else
Set db = OpenDatabase(strDatabase)
End If
intTableCount = db.TableDefs.Count
TableExists = False '//Default to table not found
For x = 0 To intTableCount - 1
If db.TableDefs(x).Name = strTable Then
TableExists = True
Exit For '//If table is found, get out of loop and return True
End If
Next x
ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure TableExists..."
Resume ExitProc
End Function
Public Function IsBlank(anyValue As Variant) As Boolean
'Returns true if a value is Null or an empty string
IsBlank = False
If IsNull(anyValue) Then
IsBlank = True
Else
If Trim(anyValue) = "" Then
IsBlank = True
End If
End If
End Function
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
