Is there a version of PostgreSQL returning in Access DB?

Question

I have an access database for which I need to run a query that is available in postgres DBs, I was wondering if there is a possible that this can be accomplished:

Insert into Table (Col1,Col2...) values(Val1,Val2,...) returning * (ore even just an id defining that specific set of data that was just inserted)? 

I'm using c# to communicate to the DB.Anything would help, thank you.

The code I basically use is the following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace Testquery1
{
    class Program
    {

        static void Main(string[] args)
        {

            string query = "INSERT INTO Table ( Val1, Val2, Val3 ) values(14,2,1)";
            Test1 queryselect = new Test1();
            queryselect.dataconnection(query);
        }


    }
    class Test1
    {

        public OleDbConnection connection = new OleDbConnection();
        string path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        string fileloc = @"DataBase.accdb";
        string provider = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
        public void dataconnection(string query)
        {

            connection.ConnectionString = provider + path + fileloc;
            Console.WriteLine(connection.ConnectionString);
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = query;
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}

Answer 1

Unfortunately with .net you cannot do a append or make table query between two different connections.

However, you CAN in Access.

If you have a working PostgreSQL SQL query that returns records, then you can simple save that query in Access (as a pass-through query.

You can now use that query in Access and send it to a NEW local table in Access. (Access supports this concept, .net does not)

You can thus either append or “make table” of that data.

And more interesting is you can even append between different connections in Access. So you can append from a PostgreSQL table to say a MySQL, or Oracle or FoxPro table inside of access.

Again, you can’t do this in .net.

So, assume a working raw SQL (PostgreSQL format) query that works in PostgreSQL? Take that SAME working query and save it in Access as a pass-through query.

Then in Access you can append to a table in Access (assuming same table structure with:

VBA (Access code)

Dim strSQL as string
strSQL = "INSERT INTO LocalTable SELECT * from QryPassR"
Currentdb.Execute strSQL

And if you want to MAKE a new table in Access with the SAME structure, so make table (not append), you can go:

Dim strSQL as string
strSQL = " SELECT * INTO LocalTable FROM qryPassR"
Currentdb.Execute strSQL

You can also in VBA code change the PostgreSQL to include criteria for that select.

(air code - does not take into account SQL injection issue).

Dim strCity as string.

strCity = inputbox("What city to pull from PostGres?")

dim strSQL as string

strSQL = "select * from tbleHotels where City = '" = strCity & "'"

With currentdb.QueryDefs("QryPassR"
  .SQL = strSQL
End with
strSQL = "INSERT INTO LocalTable SELECT * from QryPassR"
Currentdb.Execute strSQL

‘ above will copy all the records from PostGreSQL of city = Edmonton into the Access table (called local table in this example).

And as noted, you not limited to “LocalTable” being a access table, it could be a FoxPro table, MySQL, SQL server etc. So you not limited to JUST using Access tables in the above with your given SQL. So any linked table you have in Access can be used – including ones that point to other database systems.

If you must use .net, then you have to:

Connect to first database. Execute query to pull and fill a datatable.

Connect to second database. Create (open) a data table based on second database.

Loop (iterate) each row from first connection datatable and copy the row into the second datatable (based on 2nd connection).

You have to do a row by row copy. (but there is ImportRow method of the .net datatable, so you don’t have to loop by a column by column copy. (but you have to loop row by row).

In Access this looping is not required and in fact you can use SQL commands that operate on both tables, including as per above the make table and append, and you can even do relation joins between such tables - even those based on different connections (data sources).

Edit

Based on comments, it looks like the simple question is:

After I insert a row into Access, how can I get the last ID (identity) of that insert?

The follow vb.net code will do this: Imports System.Data.OleDb

Dim MyCon As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test2\test44.accdb")
MyCon.Open()
Dim strSQL As String = "insert into tblHotels2 (City) VAlues('Edmonton')"
Dim cmd As New OleDb.OleDbCommand(strSQL, MyCon)
Dim r As Integer
cmd.ExecuteNonQuery()
cmd.CommandText = "select @@IDENTITY"
r = cmd.ExecuteScalar
Debug.Print(r)

Output = last PK id (autonumber)