2

My goal is to import data into Microsoft Access to create a database which I can reference from an excel dashboard for analysis.

I can't find any information on how to connect access to allow me to query the database on phpPgAdmin.

Any advice, direction or solution is highly appreciated.

Please let me know if there more details are necessary.

Michael Lynch
  • 23
  • 3
  • @GordonLinoff What would you recommend? Is there another way to analyze the data from excel without hosting the data in excel? I'm open to your insight, please make me informed. - Checking out your book now in fact. – Michael Lynch Oct 26 '18 at 16:30
  • . . You should be able to connect from Excel directly to Postgres (https://support.office.com/en-us/article/connect-to-a-postgresql-database-power-query-bf941e52-066f-4911-a41f-2493c39e69e4). Or use pgadmin or another database tool and bring the results into Excel. – Gordon Linoff Oct 26 '18 at 17:13

1 Answers1

3

MS Access is a multifaceted thing as many tend to conflate and confuse its frontend GUI .exe application and the distinct backend database (JET/ACE SQL engine which are Windows .dll files). Most of the time we refer to its MS Office app. Technically, MS Access is really the same type of product as phppgadmin: a GUI console to a database, only its default database is the aforementioned engine but can also integrate other ODBC/OLEDB-connected backends including Postgres, Oracle, MySQL, SQL Server, etc.

Through various means, you can integrate MS Access as a medium between PostgreSQL and Excel without any single migration (export/import) of data.

  1. Linked Tables - Directly connect to Postgres tables using its ODBC Driver.

    Linked Tables icon

  2. Pass-through queries - Create saved queries using Postgres dialect within MS Access.

    Pass-Through Queries

  3. ADO Connections (see Importing data programmatically and by using functions) - Bypass MS Access and have Excel connect directly to Postgres also using OLEDB provider or ODBC driver. Below is the programmatic version showing two connection string examples, but you can save connection objects via the Excel ribbon UI.

    Dim strConnection
' REFERENCE Microsoft ActiveX Data Objects, #.# Library
Dim conn As ADODB.Connection, rst As ADODB.Recordset

' ODBC AND OLEDB CONNECTIONS (SELECT ONE)
strConnection = "Driver={PostgreSQL};Server=IPaddress;Port=5432;" _  
                 & "Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

strConnection = "Provider=PostgreSQL OLE DB Provider;Data Source=myServerAddress;" _
                 & "location=myDataBase;User ID=myUsername;password=myPassword;"

conn.Open strConnection
rst.Open "SELECT * FROM myPGTable", conn

    By the way, above is the VBA version to be run in an Excel macro but ADO is a COM object and hence can be integrated in COM-interfaced languages including PHP, Python, R, Java, etc.

Parfait
  • 104,375
  • 17
  • 94
  • 125