Specify mdw file in embedded connection string to another Jet mdb: possible?

Question

I can use the following syntax in a Jet (mdb) query to select data from another .mdb file:

SELECT * FROM [Database=C:\Tempo\AnotherDB.mdb;].MyTable

alternatively

SELECT * FROM MyTable IN 'C:\Tempo\Another.mdb'

I want to extend this to use workgroup security a.k.a. User Level Security (ULS). I know how to specify a userID and password e.g.

SELECT *
FROM
[Database=C:\Tempo\AnotherDB.mdb;UID=Admin;PWD=***;].MyTable

but this only works when both mdb's share the same mdw.

How would I specify the path of the mdw file used to secure the other mdb? Is it possible? If not, why not?

P.S. I asked this question many years ago in this Access newsgroup post but received no reply. I gave up after an Access MVP convinced me it was not possible; I don't recall the details but it was something to do with the underlying architecture (a workspace supports only one workgroup file and there is no mechanism for a Jet query to instantiate a new workspace? something like that anyway).

My renewed interest has been sparked by this SO comment.

Answer 1

Short answer is no. If you use the source db then you can't specify another workgroup and if leave it blank and specify a valid connection string then you will get an ISAM error. (Although that method works just fine for other databases such as SQL Server.) Example (does not work):

SELECT *
FROM Table1 IN '' [Provider=Microsoft.Jet.OLEDB.4.0;Password=foo;User ID=Oorang;Data Source=C:\Users\Oorang\Documents\db1.mdb;Persist Security Info=True;Jet OLEDB:System database=C:\Users\Oorang\Documents\Security.mdw];

However you can do it with ADO. I'm not sure what you are trying to accomplish once you have the query, but here is how to get it into a recordset:

Option Explicit
Sub ADODBExample()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Password=foo;User ID=Oorang;Data Source=C:\Users\Oorang\Documents\db1.mdb;Persist Security Info=True;Jet OLEDB:System database=C:\Users\Oorang\Documents\Security.mdw"
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Table1;", cn
    Debug.Print rs.GetString
    rs.Close
    cn.Close
End Sub

It is worth noting that both methods end up hard coding the account credentials. It might be better to either A.) Prompt the user to provide said info, or B.) Use a special account with read-only access limited to the minimum. You might also be well advised to take a few steps to obfuscate the info in your code as well as password protecting your project does not actually obscure the text stored in the file in any way.

Answer 2

Well... actually you can use the connection string. It works best with SQL Server. I was just showing how it would like if it worked with Access. The point I was trying to make is that there is a valid syntax... It's just that Access won't allow you to do that with Access. (It will let you do that if the connection is to another type of database.) Funny that the feature is compatible with other types of DBs and not Access... When it's Access, but true nevertheless.

Although if they are secured with the same MDW then all you have to do is this, and the query will run in the context of the currently logged in user:

SELECT *
FROM Table1 IN 'C:\Users\Oorang\Documents\db2.mdb';

Answer 3

@Oorang: I really do not think it is valid ACE/Jet syntax to put an OLE DB connection string into the IN database clause.

The below is what I consider to be a proof of my assertion. I'm keeping things simple and creating two new mdb's without introducing security or mdw's:

Sub TestIn()
  On Error Resume Next
  Kill Environ$("temp") & "\Referenced.mdb"
  Kill Environ$("temp") & "\Referencing.mdb"
  On Error GoTo 0
  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\Referenced.mdb"

    With .ActiveConnection

      Dim Sql As String

      Sql = _
      "CREATE TABLE Table1 (ID INTEGER NOT NULL);"
      .Execute Sql

    End With
    Set .ActiveConnection = Nothing
  End With

  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\Referencing.mdb"

    With .ActiveConnection

      Sql = "SELECT * FROM Table1 IN '' [" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\Referenced.mdb" & _
        "]"

      Dim rs
      On Error Resume Next
      Set rs = .Execute(Sql)
      If Err.Number <> 0 Then
        MsgBox Err.Description
      End If

    End With
  End With

End Sub

The error message I see is, "No database specified in connection string or IN clause."

Not so easy to post a proof using SQL Server. For me this ACE/Jet syntax works:

SELECT COUNT(*) FROM 
[ODBC;Driver={SQL Server};SERVER=JAMIEC;DATABASE=Airplanes;UID=sa;Pwd=***;].Calendar

But if I switch the connection in the SQL to a connection string I know works with OLE DB I get a "Could not find installable ISAM" error:

SELECT COUNT(*) FROM
[Provider=SQLNCLI.1;Database=Airplanes;User ID=sa;Password=***].Calendar;

I interpret these errors as the ACE/Jet parser complaining that it doesn't speak OLE DB and to get it to play nice you have to use its own syntax.

If you can post some code that actually does work for you then I'll try it out. Thanks in advance.

Answer 4

Hi ODW:) Maybe I should reconsolidate and clarify:) In the context of your original question, the answer is: You can't do it via SQL/Query Builder, if the databases are: both Access, and have a different mdw. However, if both databases are Access and have the same mdw, (as your later comment indicated) then you can do it via SQL/Query Builder. In that specific scenario you would not (can't) do it via a connection string you would do it following the steps 1-4 below, then replace step 5a with 5b.

If the databases are both Access and have a different mdw, the only way to specify a different workgroup is with the connection string. However the SQL/Query Builder will throw a ISAM error if you try to via the connection string property (in step 5a below). So you can't do it via the Query builder. You can do it with VBA using ADO as in my example posted previously.

Remember that the connection string property in the query builder is for non-Access databases, source database is for Access databases with the same (or no) mdw. For Access databases with different mdws the only solution I am aware of is the VBA one I posted previously.

Now as to using the connection string directly in SQL. You can do this. But there are some caveats: It won't work with other Access databases. If you do it with SQL Sever dbo tables, you will have to edit the SQL the query builder generates to remove the "dbo." prefix.

I think the simplest way is to just walk you through what I am trying to say.

1. Set up a working connection string to a non-access database. (I suggest to keep it easy, just use an Excel file.)
2. Once you have the string open the query builder in Access.
3. Close the select table dialog.
4. Press Alt-Enter to open Query Properties. 5a. In "Source Connect Str" paste the Connection String. *5b. Paste the path to the other database in "Source Database".
5. Now add your tables and build your query.
6. Go to the SQL view. See what was built.

But it's almost irrelevant as this approach, while valid for non-Access databases, does not work for Access. (It's a bit frustrating I know.)

Example of connection string in use:

SELECT [Sheet1$].F1
FROM [Sheet1$] IN '' [Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Users\Oorang\Documents\Book1.xls];

I really do not think it is valid ACE/Jet syntax to put an OLE DB connection string into the IN database clause.

Just as an FYI, you use the connection string, as shown above, but if you use the Source Db property, you will get what is below:) (I tested all my examples before I posted them:))

SELECT *
FROM Table1 IN 'C:\Users\Oorang\Documents\db2.mdb';

Answer 5

Simple answer: it's not possible.