Connect to external .mdb

[bcobb2350]

I need to connect to an external .MDB (version 2.0) and run queries against it from within a form. The database requires that I enter a password. Which connection type should I use?

[dcrake]

The external mdb should already be linked to the front end in the first splace. Therefore you would neot need to provide the pasword each time. Once you have created the link with password verification you are not asked for it again.

David

[bcobb2350]

Hi David,

Thank you for your response. My situation is different than what your post anticipates. I work in an environment where the end-user can export Access tables to an external database. The file will be exported multiple times and, depending on the end-user, the file can be located in any folder.

So the end-user is, in essence, re-linking to a different database each time. The password necessary to open the database is an administrative password and I don't want to publish the password. I want to hard-code it into my application, which they will never see.

Can you help me with this scenario?

Thanks so much,

BCOBB

[dcrake]

There is a way to link to an existing mdb that has password protection using vba.

The only issue is, do all the mdb's share the same password? Or does each different mdb have their own unique password? If the latter then you will need to maintain a list of passwords somewhere or hardcode them into your vba.

Another issue may be if you then go on and create a new mdb, and you have the code hardcoded, you will need to modify your vba script to take this new mdb into account.

Also do the existing mdb's all reside in a known location or can they reside in any location not known to the application until that location is selected.

As you can see I am going down the pessamistic route, which is the best route to take, as it attempts to cover all eventualities.

David

[bcobb2350]

Hi David,

Thanks for the response and being so thorough.

1 - Yes, all the .mdb's will have the same password (it's an admin password - which is why I don't want to publish it.)

2 - You are correct, I do not know where the end-user will place the .mdb file. My program uses the filedialog code to let the end-user locate and select the .mdb file.

3 - The end user may export multiple .mdb files and give them different names. The table structure will always be the same - they will just have different data.

Question - would it be better to connect to the external database and access the data using SELECT statements? Or, perhaps import the data using an INSERT statement?

Again, thank you for your help.

BCOBB

[dcrake]

Ok
Lets asume you have used you dialog box to get the location of the mdb and the name of the mdb. You next step is to open it


First create some public variables in a standard module

Public obDAO As DAO.Workspace, obDB As DAO.Database
Public DBPass As String, strDBPath As String

strDBPath will be the full path and name of the mdb to open
DBPass will be your administrator pasword

strDBPath = "C:\Temp\Test.mdb"
DBPass = "LETMEIN"


Then as a double check perform a Dir() to check for its existance

 
If Dir(strDBPath) <>> "" Then
   Call OpenConnection
Else
   Exit Sub
End If

The following code will create an open connection to the mdb without the need to link the tables.

Sub OpenConnection()
    Set obDAO = DAO.DBEngine.Workspaces(0)
    Set obDB = obDAO.OpenDatabase(strDBPath, False, False, ";pwd=" & DBPass & "")
End Sub

If you need to actully need to link the tables you will have to first ensure that the current linked tables are not in the selected mdb. If so there is no need to link them, else you need to rebuild the links.

David

[bcobb2350]

Hi Dave,

I'm making progress, I think. I created a blank form and put a command button on it. Below is the code
==================================
Option Compare Database
Public obDAO As DAO.Workspace, obDB As DAO.Database
Public DBPass As String, strDBPath As String
strDBPath = "C:\data\dsg\covenantcare\frxexp.tdb"
DBPass = "MyPassWord"

Option Explicit
Private Sub Command0_Click()
If Dir(strDBPath) <> "" Then
Call OpenConnection
Else
Exit Sub
End If

End Sub
Sub OpenConnection()
Set obDAO = DAO.DBEngine.Workspaces(0)
Set obDB = obDAO.OpenDatabase(strDBPath, False, False, ";pwd=" & DBPass & "")
End Sub


When I click on the button I get the following error message:

Compile Error: Invalid Outside Procedure - it highlights the strDBPath line

Help?

Thanks,

BCOBB

[dcrake]

You need to move the following lines

strDBPath = "C:\data\dsg\covenantcare\frxexp.tdb"
DBPass = "MyPassWord"

to the first line after the

If Dir()

David

[bcobb2350]

David,

It worked perfectly - You Are The Best!

Thank you very, very much.

BCobb