linking table in a password protected database

[bdaniel]

Trying to reconnect a link to a password protected database.



Here is a portion of the the code from my sub procedure that I am using to make the connection:

Public Sub CheckRegCode()

Dim spath As String
spath = CurrentProject.Path

DoCmd.TransferDatabase acLink, "Microsoft Access", spath & "\Database1.accdb", acTable, "Copy of RegCodeImp", "Copy of RegCodeImp"

This works fine except it asks for the password to open the database and I don't want the users to have the password, and there is no way in the TransferDatabase command to include the password for Access databases.

After searching the internet for solutions, it appears that the only way to open the password protected database is using the opendatabase method.

Below is my attempt at adding the necessary code to open the database so it can link. It requires using DAO, which I haven't used before. Was wondering if this looks correct?

Public Sub CheckRegCode()

Dim spath As String
spath = CurrentProject.Path

Dim db As DAO.Database
Dim ws As DAO.WorkSpace
Set ws = DBEngine.WorkSpaces(0)
Set db = ws.OpenDatabase _ (spath & "Database1.accdb", _ True, True, "MS Access;PWD=MYPASSWORD")

DoCmd.TransferDatabase acLink, "Microsoft Access", spath & "\Database1.accdb", acTable, "Copy of RegCodeImp", "Copy of RegCodeImp"

[bdaniel]

The code below worked. I had to add the DeleteObject command because the TransferDatabase was adding a new table to the database instead of just linking the existing table.

Public Sub CheckRegCode()

DoCmd.DeleteObject acTable, "Copy of RegCodeImp"

Dim spath As String
Dim spathdb As String
spath = CurrentProject.Path
spathdb = spath & "\database1.accdb"

Dim db As DAO.Database
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(spathdb, True, True, "MS Access;PWD=MYPASSWORD")

DoCmd.TransferDatabase acLink, "Microsoft Access", spathdb, acTable, "Copy of RegCodeImp", "Copy of RegCodeImp"