Hi,

I have setup a new database using Access 2003 that accesses an ODBC database which requires a username and password. Because the database is for basic level users, we do not want them to know the password.

The database can be programmed to use the password in the background, but having very very basic indeed VBA skills, I do not know where to place the code. I have discovered from the Help files that you can use the DefaultUser and DefaultPassword, but this does not mention a server (on which the database is stored) and whereabouts in the form's code that I should write the script (for example, General, Declarations). I did try anyway, but it did not work - I don't really know if this is the correct property anyway!!

Can anybody help me on this one, please? I will be really grateful if you can.

All the best,


Roly

Which ODBC database are you connecting to? Each will use its own ODBC driver, so the connection string syntax (which can include the user name and passsword) will vary. You connect to an ODBC database using either:

a) an ODBC DSN-Less connection string (by setting the ConnectionString property of an ADO Connection object), or

b) an ODBC DSN (Data Source Name) set up using the ODBC Data Source Administrator in Windows at Start -> Programs -> Administrative Tools -> Data Sources (ODBC). (on Windows XP PRO)

Neither of these approaches is exactly "very, very basic indeed", however. ;)

HTH,

Bob

Should mention that in the latter case (ODBC DSN) the DSN will still need to be included in an ADO connection string. So, either way both VBA and ADO a likely to be involved.

Bob

Hi Bob,

Thanks for your help on this one!

The second option (b) is the one I am trying to connect automatically to.

I will be very grateful for your response.


Roly

Hi Roly,

You didn't mention what type of database you are trying to connect to?? Also, were you able to find the ODBC Data Source Administrator on your machine? Happy to help if I can.

Bob

Hi Roly,

Once we figure out what database sever you are using, I'm thinking the easiest thing for you to do (no coding involved) would be to store a user name and password in the DSN itself, and then link to the databse server through the Access interface. To do that, you would select:

File -> Get External Data ->

Then in the Link dialog box, click the "Files of Type" drop down list and select "ODBC Databases". This will launch the "Select Data Source" dialog where you will see listed all the Machine(user and system) and File DSNs that you have defined on your system using the ODBC Data Source Administrator. That way anyone who has permissions to open the Access application will automatically have permission to access the database server back-end.

How many client machines are involved?

HTH,

Bob

Hi Bob,

Thanks for your messages.

The database is runs on sql server - using an ODBC DSN set up through ODBC Data Source Adminstrator (I set it up on computer). We cannot store the username and password in the DSN becassue it is our main MIS here and needs to be protected, hence the reason you must log on.

I have heard about this ADO connection string, in my research into this issue, but cannot code it myself, nor do I know where to put the code.

Any help will be really appreciated.

Kind regards,


Roly

Hi Roly,

I've been playing with a possible solution: programatically relinking the tables at startup. I've been trying to get an AutoExec macro to call a VBA function that supplies a DSN, Password, and User ID when the new table link gets appended to the tables collection. I've tried both ADO and DAO, but still with no success: I still get the SQL Server login prompt. I'll keep playing with it a bit tomorrow, and if I can get it to work, I'll post again. Interesting problem.

Bob

Got a couple of functions that you may be able to use. The first (AttachTable_MSJet) is used for dynamically attaching MS Access/Jet tables to your Access database. The second (AttachTable_ClientServer) is used for attaching Client-Server tables (including SQL Server) tables to your database.

And they both work - tried 'em out myself first!

Here's the MS-Jet Attach function:

Public Function AttachTable_MSJet(strTableNameParm As String, _
                                  strDataSourcePathFilenameParm As String) As Boolean

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table

    Dim db As DAO.Database

    Dim strWhereCond As String

    strWhereCond = ""
    strWhereCond = strWhereCond & "Name = '" & strTableNameParm & "'"
    strWhereCond = strWhereCond & " AND Type = 6"

    If Not IsNull(DLookup("Name", "MSysObjects", strWhereCond)) Then
       Set db = CurrentDb
       db.TableDefs.Delete strTableNameParm
       db.Close
    End If

    cat.ActiveConnection = CurrentProject.Connection

    tbl.Name = strTableNameParm
    Set tbl.ParentCatalog = cat

    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strDataSourcePathFilenameParm
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableNameParm

    ' Append the table to the collection
    cat.Tables.Append tbl

    Set cat = Nothing

End Function


And here's the Client-Server Attach Function:

Public Function AttachTable_ClientServer(strDBDriverParm As String, _
                                         strDBServerNameParm As String, _
                                         strDBNameParm As String, _
                                         strTableNameParm As String, _
                                         strUserIDParm As String, _
                                         strPasswordParm As String)

    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    Dim strWhereCond As String

    Dim db As DAO.Database

    strWhereCond = ""
    strWhereCond = strWhereCond & "Name = '" & strTableNameParm & "'"
    strWhereCond = strWhereCond & " AND Type = 4"

    If Not IsNull(DLookup("Name", "MSysObjects", strWhereCond)) Then
       Set db = CurrentDb
       db.TableDefs.Delete strTableNameParm
       db.Close
    End If

    cat.ActiveConnection = CurrentProject.Connection

    tbl.Name = strTableNameParm
    Set tbl.ParentCatalog = cat

    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Provider String") = _
                   "ODBC" & _
                   ";Driver=" & strDBDriverParm & _
                   ";Server=" & strDBServerNameParm & _
                   ";Database=" & strDBNameParm & _
                   ";UID=" & strUserIDParm & _
                   ";pwd=" & strPasswordParm
    tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableNameParm

    ' Append the table to the collection
    cat.Tables.Append tbl

    Set cat = Nothing

End Function



Neither of these require you to set up an ODBC data source via Control Panel. Here's a few notes on the functions:

 - While I used ADOX to do the attach, I can't find the ADOX code to do a detach, thus
   the "Dim db As DAO.Database", "Set db = ..." commands.
 - In order to run these BE SURE that you have the 3 following references in your Tools | References
   checked:
   - Microsoft DAO 3.6 Object library
   - Microsoft ADO Ext. 2.7 for DDL and Security
   - Microsoft ActiveX Data Objects 2.1 Library
   - There could also be different versions of any of these libraries
 - The "Cache Link Name/Password" found in the Client-Server function will prevent any
   additional prompts for user-id/passwords from the user when a screen opens, etc.
 - The DLookup() function along with the "db.TableDefs.Delete" is used to
   delete the link if the table is already linked. Type 1 in MSysObjects is used for
   native MS-Access tables, Type 4 is used for ODBC-linked tables, and Type 6 is used
   for MS-Jet/Access linked tables.



Here's a couple of sample calls to these functions:

Call AttachTable_MSJet("c:\My Documents\Sampledb.mdb", "tblCustomers")

Call AttachTable_ClientServer("SQL Server", "CORP_SERVER", "CustomerDB", "CUSTOMER_TABLE", "sa", "")

By the way, if anyone knows how to detach a linked table using
ADO or ADOX, I'm VERY welcome to ideas, too! :)

Hope you may find some of this useful. I use this code in TONS of my MS-Access applications. Plus, after forgetting to set up ODBC DSN connections on user workstations during an initial deployment, this helps perpetuate my forgetfulness. ;)

Good luck and happy programming!

Warren

:D

Hi Roly and Warren,

With the help of Warren's Client-Server Attach Function (cool code Warren) I believe we've done it :).

I made 3 modifications to the code that should get you what you need Roly. No DSN involved.

Change One:

Warren, I got rid of DAO to simplify things for Roly, and added the following which is one way to "detach linked table(s) using ADOX" (caveat: no duplicate table names allowed!!)

       ' If link exists, delete it
        On Error Resume Next
        Set tbl = cat.Tables(strTableNameParm)
        If Err.Number = 0 Then
            cat.Tables.Delete strTableNameParm
        Else
            Err.Clear
        End If

Change Two:

I created a new sub procedure that calls Function AttachTable_ClientServer, passing an ADO recordset of all the user tables in the SQL Server db, instead of just the name of a single table. That way, Roly can link to multiple tables at start-up. So there is a new loop in the AttachTable_ClientServer Function and the parameter list has been changed.

Change Three:

I dealt with the issue of needing to refresh the Database Window (so the new links are visible) after the code runs at start-up by adding:

Application.RefreshDatabaseWindow

OK Roly. You now have the ADO connection you've been asking for. Here's how you can use it.

1. Create a new standard module in your app and paste the code below into it. Name the module basStartUp, or something.

2. Set references to the Microsoft ADO Ext. 2.x for DDL and Security and the Microsoft ActiveX Data Objects 2.x Library (whichever versions of these two dlls are installed on your system)

3. Create a new Marco and name it AutoExec (an AutoExec macro will always run when the database loads.)

4. Set the macro's properties as follows:

Action: RunCode
Function Name: RetrieveSQLServerUserTables()

I created RetrieveSQLServerUserTables as a fucntion, even though it doesn't return any values, simply because Macros can't call Sub Procedures.

You could also call RetrieveSQLServerUserTables() from the Load event of a startup form if you have one.

That's it. Now when a user opens the database, the code in the module will run and establish links to all the your user tables on SQL Server. Since the user name and password a stored in the "Jet OLEDB:Link Provider String" property of the table link requested, users won't be prompted to log on.

Thanks again Warren, and post with further questions Roly if you have any.

Here's the code module:

Function RetrieveSQLServerUserTables()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strConnectionString As String
    Dim strSQL As String

    Set cnn = New ADODB.Connection
    strConnectionString = "Provider=SQLOLEDB.1;" & _
              "Data Source=yourServerName;" & _
              "Initial Catalog=yourDatabaseName;" & _
              "User ID=yourUserName" & _
              ";Password=yourPassword"
    cnn.Open (strConnectionString)

    ' Retrieve names of all SQL Server user tables
    strSQL = "SELECT name FROM sysobjects WHERE xtype = 'U'"
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

    'Debug.Print rst.GetString(adClipString, , ";")

    Call AttachTable_ClientServer("SQL Server", "CORP_SERVER", "CustomerDB", rst, "yourUserName", "yourPassword")

    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing

End Function

Public Function AttachTable_ClientServer(strDBDriverParm As String, _
                                         strDBServerNameParm As String, _
                                         strDBNameParm As String, _
                                         objRst As ADODB.Recordset, _
                                         strUserIDParm As String, _
                                         strPasswordParm As String)
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    Dim strTableNameParm As String

    Do Until objRst.EOF

        strTableNameParm = objRst!Name
        'Debug.Print strTableNameParm

        cat.ActiveConnection = CurrentProject.Connection

        ' If link exists, delete it
        On Error Resume Next
        Set tbl = cat.Tables(strTableNameParm)
        If Err.Number = 0 Then
            cat.Tables.Delete strTableNameParm
        Else
            Err.Clear
        End If

        tbl.Name = strTableNameParm
        Set tbl.ParentCatalog = cat

        tbl.Properties("Jet OLEDB:Create Link") = True
        tbl.Properties("Jet OLEDB:Link Provider String") = _
                       "ODBC" & _
                       ";Driver=" & strDBDriverParm & _
                       ";Server=" & strDBServerNameParm & _
                       ";Database=" & strDBNameParm & _
                       ";UID=" & strUserIDParm & _
                       ";pwd=" & strPasswordParm
        tbl.Properties("Jet OLEDB:Cache Link Name/Password") = True
        tbl.Properties("Jet OLEDB:Remote Table Name") = strTableNameParm

        cat.Tables.Append tbl

        objRst.MoveNext

    Loop

    Set cat = Nothing

    Application.RefreshDatabaseWindow

End Function

HTH,

Bob