0

I have created an Access 2010 database with linked tables on SQL-server. Buttons on the main form call stored procedures that enable users to add data from their respective source files, mainly Excel tables. The development of stored procedures and triggers has been outsourced.

The database has been rolled out in different locations/countries. To improve performance and to prevent users from getting access to data from locations other than their own management has decided for an update of the database. The developing company has now come up with the following: The database will be split into different schemas, each location will get his own schema. The selection of the schema will be based on the location of the user’s region as defined in properties on ActiveDirectory. I can store this location in a public variable.

To create the links to tables on SQL-server I have used the Access built-in wizard. A connection to a table is at the moment now described as something like: ODBC;Description=NETWPROD;Driver=SQL Server;Server=CCMDB11,cc.acme.corp;Trusted connection=Yes;app=Microsoft Office 2010;Database=1PRD;table=dbo.1PRD_Compliances

My questions to you is the following:

Is it possible to add a property like ‘SCHEMA=no’ in the tables description. And if yes, how do I add this using Access VBA.

Or

How do redirect the user to the correct location based schema at log-in using Access VBA?

Or How do I redirect the user to the correct views on SQL-server, the view having filtered the data on relevant location.

Any help is greatly appreciated.

Erik A
  • 31,639
  • 12
  • 42
  • 67
sybolt
  • 1
  • 1
  • >>>How do redirect the user to the correct location based schema at log-in using Access VBA?<<< It should be done not using VBA but by giving the right permissions. The users have to be granted the appropriate permissions on schema, not on the database. This way they will "see" only their schema and that is more important, they will be able to USE only the objects of their schema – sepupic Jul 12 '17 at 07:59
  • `;table=dbo.1PRD_Compliances` means you are using the schema `dbo` (the default schema). Just link to the tables from the appropriate schema instead. – Andre Jul 12 '17 at 08:57
  • See also https://stackoverflow.com/questions/3282665/possible-to-set-default-schema-from-connection-string – Andre Jul 12 '17 at 09:25
  • Andre, Thank you for your reply. The problem however is that some users have access to different schema's. When a users logs in with only access to one schema the users should be directed to the relevant schema, with multiple regions the user should be able to select the region and thereby directed to the corresponding schema. – sybolt Jul 14 '17 at 08:01

1 Answers1

0

I will only answer your first question, although you can use the same technique to achieve an answer to the others.

You can alter the connection string through the TableDef.Connect property.

Public Sub AddNoSchema()
    Dim db As DAO.Database
    Set db = CurrentDb()
    Dim tdf As Variant 'DAO.TableDef
    For Each tdf In db.TableDefs
        If tdf.Connect LIKE "*SQL Server*" Then
            tdf.Connect = tdf.Connect & ";SCHEMA=no"
            tdf.RefreshLink
        End If
    Next tdf
End Sub
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Erik, thank you for your reply. the way you present the answer makes it seem quite easy to solve the problem. I will certainly have ago at this one! – sybolt Jul 14 '17 at 08:07
  • If it works, please select my answer as the correct one, to show this problem has been solved – Erik A Jul 14 '17 at 18:44