Results 1 to 11 of 11
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Connect to different schema using ODBC connection

    Hi Guys,



    i am using Access FE and postgresql BE.

    on start up i am linking tables and queries using code below:


    Code:
    Dim username, pass As String
    
    username = Environ("username")
    pass = "PasswordAccess"
    
    
    '''You can use a pass-through query to list the table names from your SQL Server database. Open a recordset based on that query. Then loop through the recordset rows and link each table.
    
    
    
    
    strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;"
    
    
     DoCmd.Hourglass True
        Dim db As dao.Database
        Dim tdef As TableDef
        Dim qdef As QueryDef
        Dim missingTbl() As String
        Dim upper As Integer
        Dim strMsg As String
        
        ReDim missingTbl(0 To 0)
        
        Set db = CurrentDb
        For i = 0 To CurrentDb.TableDefs.Count - 1
            Set tdef = db.TableDefs(i)
            Debug.Print tdef.Name
      ''          If Len(tdef.Connect) > 0 And Left(tdef.Name, 1) = "t" Then
                If Not (tdef.Name Like "MSys*" Or tdef.Name Like "~*") Then
                    tdef.Connect = strConnect
                    tdef.RefreshLink
                End If
        Next i
    
    
        For Each qdef In CurrentDb.QueryDefs
        Debug.Print qdef.Name
            If qdef.Type = dbQSQLPassThrough Then
                qdef.Connect = strConnect
            End If
        Next qdef
        
        DoCmd.Hourglass False
    
    
    End Function
    IT is working ok but somehow is is always connected to public schema.
    i want to connect to accesslog2 schema and use it.

    How can i do this?

    I tried to do:

    ALTER ROLE "Administrator" SET search_path = "accesslog2" but still it is connected to public schema.
    (when i am queriyng from Access using "Select current_user") the result is "Administrator" so the user is ok.

    Please help,
    Jacek

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jacek,
    Not a heavy user of Postgresql, but try this (from https://www.postgresql.org/message-i...HIRO57887DE653)

    Code:
    strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;" & _
    ;A6=set search_path to accesslog2,public;"
    Cheers,
    Vlad

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Vlad,

    thank you.

    I found the problem.
    First of all i have to remove existing tables.
    And next i can loop through each of them and connect to different schema using
    "TABLE = schemasname.tablename"

    Best,
    Jacek

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I have to reopen topic...

    I do not why but this has stopped working...

    I tried to your method Vlad but not working.

    User is admin and has also set parameter to "search path" in pg Admin:

    Click image for larger version. 

Name:	Screenshot_22.png 
Views:	20 
Size:	176.1 KB 
ID:	34581

    Wooo it is difficult!!

    Best,

    Jacek

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok,

    problem is complex but i am almost solve it ;-)

    First of all,
    Search path is connected with user.

    So i have to user

    Code:
    "ALTER ROLE "admin" IN DATABASE "AccessLog"SET search_path TO public;"
    And now after Access Database restart (turn off and turn on)

    access is prompting for ODBC connection details when i am trying to open any of tables:

    Click image for larger version. 

Name:	Screenshot_24.jpg 
Views:	19 
Size:	83.4 KB 
ID:	34582

    How can i somehow "restart" access FE without closing the application?

    I tried to delete all tables and connect them to BE once again but only after restarting this is working fine.

    Very important thing.
    Best,
    Jacek

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Anyone?

    Why I have to restart access to see ODBC changes?

    Best,
    Jacek

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried to call the RefreshLinks method after updating the connection info?
    Cheers,
    Vlad

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Vlad,

    i was trying to use code:

    Code:
    Function RemoveAndConnectToOdbcTables()
    
    Dim username, pass, strConnect, strConnectQuery As String
    Dim i As Long
    
    
    username = Environ("username")
    pass = "PasswordAccess"
    
    
    '''You can use a pass-through query to list the table names from your SQL Server database. Open a recordset based on that query. Then loop through the recordset rows and link each table.
    On Error GoTo ErroHandler
    
    
    username = "admin"
    pass = "PasswordAccess"
    
    
    strConnect = "ODBC;DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;"
    
    
    strConnectQuery = "ODBC;DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;"
    
    
     DoCmd.Hourglass True
        Dim db As DAO.Database
        Dim tdef As TableDef
        Dim qdef As QueryDef
        Dim missingTbl() As String
        Dim upper As Integer
        Dim strMsg As String
        Dim tdfCurrent As TableDef
        Dim TableName As String
        
        Set db = CurrentDb
        For i = 0 To CurrentDb.TableDefs.Count - 1
            Set tdef = db.TableDefs(i)
            Debug.Print tdef.Name
            TableName = tdef.Name
      ''          If Len(tdef.Connect) > 0 And Left(tdef.Name, 1) = "t" Then
                If Not (tdef.Name Like "MSys*" Or tdef.Name Like "~*") Then
                    DoCmd.DeleteObject acTable, tdef.Name
                    Set tdfCurrent = db.CreateTableDef(tdef.Name)
                    tdfCurrent.Connect = strConnect
                    tdfCurrent.SourceTableName = tdef.Name
                    db.TableDefs.Append tdfCurrent
    
    
                    db.TableDefs(TableName).RefreshLink
                End If
        Next i
    
    
        For Each qdef In CurrentDb.QueryDefs
        Debug.Print qdef.Name
            If qdef.Type = dbQSQLPassThrough Then
                qdef.Connect = strConnect
            End If
        Next qdef
        
        DoCmd.Hourglass False
        
    Exit Function
    
    
    ErroHandler:
    MsgBox "Error number is: " & Err.Number & "; " & Err.Description
    DoCmd.Hourglass False
    
     End Function
    but it failed. Additionaly i tried to use tdfCurrent.refreshlink but nothing happen...

    I do not now why?!

    Best,
    Jacek

  9. #9
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I tried to use link from here:

    https://stackoverflow.com/questions/...n-odbc-changes

    but this is not working.
    Only restart and run once again access fe...

    Thanks for help,
    Jacek

  10. #10
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    This is working like that,

    I am running function in postgresql :

    "CREATE OR REPLACE FUNCTION public.alterrole( user_name text,
    schemaname text)
    RETURNS void
    LANGUAGE 'plpgsql'


    COST 100
    VOLATILE
    AS $BODY$


    BEGIN
    execute(format('alter role %I in database %I set search_path = %I', user_name, 'AccessLog', schemaname));
    END;


    $BODY$;


    ALTER FUNCTION public.alterrole(text, text)
    OWNER TO postgres;"

    And with function i am changing which schema should be taken.
    I have test and public for testing purposes.

    And refreshlink is not working.
    Even if i am creating new table from old one using link in post #9 this is not working.
    And even i am saving database as new still doesnt work...

    Best,
    Jacek

    edit:

    My newest code:

    Code:
    Sub test3()
    
    username = "admin"
    pass = "PasswordAccess"
    
    
    newConnectionString2 = "ODBC;DRIVER={PostgreSQL Unicode};" & _
    "DATABASE=AccessLog;" & _
    "SERVER=localhost;" & _
    "PORT=5432;" & _
    "UID=" & username & ";" & _
    "PWD=" & pass & ";" & _
    "Trusted_Connection=Yes;"
        
        Call RefreshODBCLinks(newConnectionString2)
    
    
    End Sub
    
    
    Public Sub RefreshODBCLinks(ByVal newConnectionString As String)
        Dim db As DAO.Database
        Dim tb As DAO.TableDef
        Dim originalname As String
        Dim tempname As String
        Dim sourcename As String
        Dim i As Integer
        Dim item As Variant
    
    
        Set db = CurrentDb
        ' Get a list of all ODBC tables '
        Dim tables As New Collection
        For Each tb In db.TableDefs
            If Not (tb.Name Like "MSys*" Or tb.Name Like "~*") Then
                tables.Add item:=tb.Name, Key:=tb.Name
                DoCmd.DeleteObject acTable, tb.Name
            End If
        Next tb
        
        db.TableDefs.Refresh
    
    
        ' Create new tables using the given DSN after moving the old ones '
    For Each item In tables
                    Set tdfCurrent = db.CreateTableDef(item)
                    tdfCurrent.Connect = newConnectionString
                    tdfCurrent.SourceTableName = item
                    db.TableDefs.Append tdfCurrent
                    tdfCurrent.RefreshLink
    Next item
    
    
        db.TableDefs.Refresh
    
    
        Set db = Nothing
    End Sub

  11. #11
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Anyone with this bug?

    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-04-2018, 03:44 AM
  2. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  3. Replies: 2
    Last Post: 01-21-2015, 06:03 AM
  4. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  5. Replies: 5
    Last Post: 03-29-2009, 07:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums