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

    Adodb to Postgresql VBA

    Hi,

    I have linked tables in Access FE to BE postgresql.

    Now i want to open recordset in Access using adodb recordset (it is better for sql server/postgresql connection).

    Code:
    Sub Test
    
      Dim cnn As Object
        Dim rst As Object
    
     Set cnn = CreateObject("ADODB.Connection")
      Set cnn = CurrentProject.Connection
    
     Set rst = CreateObject("ADODB.recordset")
    
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    In last line there is an error.
    cnn is strange here:

    ?cnn
    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\Makro\Database Integration\Model Excel-Database 16 Back Up Solution.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\admin\AppData\Roaming\Microsoft\ Access\System.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\16.0\Access\Access Connectivity Engine;Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False
    How to get connection from linked table properties?
    Have i write postgresql credentials here or can i use already existed DSN linked tables and refer to it somehow?

    Best Wishes,
    thank you,


    Jacek

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Jacek,

    For the connection string of the connection (cnn.COnnectionString= sConnection) use the .Connect property of any linked table: sConnection=CurrentDb.TableDefs ("YourLinkedTable").Connect

    Cheers,
    Vlad

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Personally I'd just use DAO; I find it simpler to work with. You're not really connecting to postgresql here, you're connecting to the linked table. The DSN handles the connection to postgresql.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

    this is not working.

    Code:
    Sub AuditChanges(IDField As String, UserAction As String)
        On Error GoTo AuditChanges_Err
        Dim cnn As Object
        Dim Scnn As String
        Dim rst As Object
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String
        Set cnn = CreateObject("ADODB.Connection")
        Scnn = CurrentDb.TableDefs("t_audittrail").Connect
        cnn.ConnectionString = Scnn
        Set rst = CreateObject("ADODB.recordset")
        rst.Open "SELECT * FROM t_audittrail", cnn, adOpenDynamic, adLockOptimistic
    I have error while opening the recordset:

    Error 3709: The connection cannot be used to perform this operation...
    Can you help with this Vlad?

    pbaldy you are right!

    i can use:

    Code:
        Set rst = CreateObject("ADODB.recordset")
        Set rst = CurrentDb.OpenRecordset("t_audittrail")
    and this is working.

    best,
    Jacek

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Jacek,
    You need to open the connection first:
    Code:
    Sub AuditChanges(IDField As String, UserAction As String)
        On Error GoTo AuditChanges_Err
        Dim cnn As Object
        Dim Scnn As String
        Dim rst As Object
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String
        Set cnn = CreateObject("ADODB.Connection")
        Scnn = CurrentDb.TableDefs("t_audittrail").Connect
        cnn.ConnectionString = Scnn
        cnn.open
        Set rst = CreateObject("ADODB.recordset")
        rst.Open "SELECT * FROM t_audittrail", cnn, adOpenDynamic, adLockOptimistic
    https://www.sqlservercentral.com/For...7775-20-1.aspx

    But I agree with Paul, DAO would work just as well, my initial impression was that the t_audittrail table was in the back-end but not linked to the front-end.

    Cheers,
    Vlad

  7. #7
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Vlad!

    Best,
    Jacek

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

Similar Threads

  1. Passing login variable from Access FE into postgresql BE
    By jaryszek in forum Database Design
    Replies: 15
    Last Post: 06-13-2018, 11:16 PM
  2. Replies: 2
    Last Post: 03-23-2018, 07:54 AM
  3. Replies: 1
    Last Post: 10-29-2015, 07:47 AM
  4. ADODB Retrieve Value with SQL Help
    By kawi6rr in forum Programming
    Replies: 3
    Last Post: 05-07-2011, 02:03 PM
  5. Adodb
    By sassy in forum Programming
    Replies: 2
    Last Post: 10-26-2009, 06:40 PM

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