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

    Connect to postgresql odbc connection string - pass thrugh query

    Hi Guys,

    I am trying to run pass through code below:

    Code:
    Public Sub InitConnect()
    
    
    On Error GoTo ErrHandler
    
    
        Dim dbCurrent As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim cnn As Object
        
        Set cnn = CreateObject("ADODB.Connection")
    
    
      sConnString = "DRIVER={PostgreSQL Unicode};DATABASE=AccessLog;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
      
      cnn.Open sConnString
        
        Set dbCurrent = DBEngine(0)(0)
        Set qdf = dbCurrent.CreateQueryDef("")
        
        With qdf
            .Connect = sConnString
            .SQL = "SELECT CURRENT_USER;"
            Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
        End With
       '' InitConnect = True
    
    
    ExitProcedure:
        On Error Resume Next
            Set rst = Nothing
            Set qdf = Nothing
            Set dbCurrent = Nothing
        Exit Sub
    ErrHandler:
        ''InitConnect = False
        MsgBox Err.Description & " (" & Err.Number & ") encountered", _
            vbOKOnly + vbCritical, "InitConnect"
        Resume ExitProcedure
        Resume
    End Sub
    And i have error with " .SQL = "SELECT CURRENT_USER;"

    Click image for larger version. 

Name:	Screenshot_15.png 
Views:	5 
Size:	7.9 KB 
ID:	34283

    When i am creating query like here manually:


    Click image for larger version. 

Name:	Screenshot_16.jpg 
Views:	5 
Size:	69.2 KB 
ID:	34284

    This is working like a charm.

    I am working on windows 10, 64 bit system.

    Access is:

    Click image for larger version. 

Name:	Screenshot_17.png 
Views:	5 
Size:	2.4 KB 
ID:	34285

    And postgresql is PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit.

    When i am creating ODBC in 64 bit settings :
    Click image for larger version. 

Name:	Screenshot_18.png 
Views:	5 
Size:	94.7 KB 
ID:	34286

    it doesn't work.

    Only Odbc 32 bit settings works in Access while linking tables (i think that this is caused by having Access 32 bit)

    Click image for larger version. 

Name:	Screenshot_19.png 
Views:	5 
Size:	106.9 KB 
ID:	34287

    And other strange thing, changing line of code to :

    Code:
    sConnString = CurrentDb.TableDefs("t_versions").Connect
    The string is the same like in this manually set up pass through query.



    causes error like here:

    https://stackoverflow.com/questions/...-default-drive

    Why this is not working?

    Please help,
    Jacek Antek

  2. #2
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    How your pass through queries are configured with other databases like MS SQL SErvel or MySQL?
    What about ODBC for them ?

    Best,
    Jacek

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

    i created manually database source connection string witgin pass thrugh query:

    ODBC;DRIVER={PostgreSQL Unicode};DATABASE=AccessTest;SERVER=localhost;PORT =5432;UID=postgres;PWD=1234

    Click image for larger version. 

Name:	Screenshot_20.png 
Views:	6 
Size:	54.5 KB 
ID:	34290

    This is working.
    Now when i am in code i have to delete "ODBC;" from connection string and this is ok until:

    Code:
     With qdf
            .Connect = sConnString
            .SQL = "select * from t_versions"
            Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
        End With
    Error I described above.


    After ".Connect = sConnString" this is my screenshot
    :
    Click image for larger version. 

Name:	Screenshot_21.png 
Views:	6 
Size:	209.1 KB 
ID:	34291

    Worth to mention is that ConnectionString property is "Provider=MSDASQL.1;" without the last part of string...

    Please help,
    Best,
    Jacek

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Ok topic closed.

    Problem was with opening connection.

    For :

    ".Connect = sConnString" should with ODBC
    But for open cnn = connection string should be without ODBC...

    Best,
    Jacek

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

Similar Threads

  1. Strange connection string to postgresql VBA
    By jaryszek in forum Programming
    Replies: 2
    Last Post: 06-01-2018, 11:10 AM
  2. Replies: 2
    Last Post: 01-21-2015, 06:03 AM
  3. Connect System DSN using connection string
    By jbailey4545 in forum Access
    Replies: 0
    Last Post: 11-08-2013, 01:29 PM
  4. Replies: 0
    Last Post: 04-25-2012, 07:59 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