Create linked tables in Access to SQL server via application role

0
votes

I'm trying to create linked tables to SQL Server tables in MS Access via a VBA script. Currently, I have the following, which works perfectly: 

strConnect = "ODBC;DSN=xxx;Trusted_Connection=Yes;" & _
                        "APP=Microsoft Office;DATABASE=myDB;ApplicationIntent=READONLY;"
    aSourceTable = Array("dmy.Tab1", "dmy.Tab2", "dmy.Tab3", "dmy.Tab4")

    For X = 0 To UBound(aSourceTable)
        Set t = New TableDef
        t.Connect = strConnect
        t.NAME = Replace(aSourceTable(X), "dmy.", "") ' remove schema
        t.SourceTableName = aSourceTable(X)
        CurrentDb.TableDefs.Append t
    Next X

Where xxx is the data server name and myDB is the database I want to connect to.

Now I'm trying to recreate the same linked tables, but the ODBC connection to the SQL server has to go through an application role (which already has been created in SQL). Any ideas on how to go about this? I've tried populating the UID/Password parameter in the connection string, but that doesn't seem to work.

I've already created the application role:

Name - tempapprole

Pwd - temppassword

Many thanks,

Nick

2
sql-serverms-accessvba
To use an application role you need to run sp_setapprole after you connect. Do you know how to run stored procedure in your code? docs.microsoft.com/en-us/sql/relational-databases/… - Nick.McDermaid
@Nick.McDermaid thanks for the reply; yes I do. The only bit I'm unsure of is where to fit this in if I'm creating the link via VBA. If I run an execute statement after the "t.Connect" statement, is that what you had in mind? I can't seem to get it to work... - Nick de Jonge
This says it might not be possible: sqlteam.com/forums/topic.asp?TOPIC_ID=15249 This describes how to do it in an ADP. But you'd need to reqrite for an ADP (they are excellent but depracated in MS Access) support.microsoft.com/en-au/help/308312/…. You many need to negotiate with your admin. sp_setapprole is really not that secure given that it's in clear text in your code. - Nick.McDermaid
@Nick.McDermaid thanks, that's very helpful. Even if the code/database is securely locked down? - Nick de Jonge
What do you mean by locked down? Nothing in MS Access or MS Access VBS is really locked down. You can download VBA password crackers. - Nick.McDermaid

2 Answers

0
votes

this works for me to connect to MS SQL-Server:

Dim Conn As New ADODB.Connection
Conn.ConnectionString = "DSN=" & myDB & ";UID=" & tempapprole & ";PWD=" & temppassword
Conn.Provider = "msdasql"
Conn.Open
-1
votes

You can try as follows-

Dim strConnect As New OleDb.OleDbConnection
strConnect.ConnectionString = "provider=microsoft.jet.oledb.4.0;data 
source=TABELS.mdb"
strConnect.Open()
aSourceTable = Array("dmy.Tab1", "dmy.Tab2", "dmy.Tab3", "dmy.Tab4")

For X = 0 To UBound(aSourceTable)
    Set t = New TableDef
    t.Connect = strConnect
    t.NAME = Replace(aSourceTable(X), "dmy.", "") ' remove schema
    t.SourceTableName = aSourceTable(X)
    CurrentDb.TableDefs.Append t
Next X