0

I want to insert data in a remoteTable (in a remote SQL Server instance) with data from a localTable (in a local MS Access).

In Access, I have a button in a Form that will execute this update, so I need to hide from the users the credentials for SQL Server accessing.

Since many users would execute this update, I would like to create a Function or Procedure that creates a DSN-less connection to SQL Server for linked table in Access, avoiding users have to create ODBC connection, making it more transparent and simple to the user.

Private Sub SendDataToSQLServerWithoutCreatingODBC_Click()

    'call a function or procedure to connect to remote SQL Server
    'update remoteTable with retrieved data from localTable
    'in T-SQL, it would be: INSERT INTO remoteTable SELECT * FROM localTable

End Sub

Thanks in advance.

jMarcel
  • 958
  • 5
  • 24
  • 54
  • You are going to have to establish a connection one way or the other. ODBC would be my suggestion. Somewhere along the way you can't avoid creating a connection to sql server, there is no other option. But not sure why you think that makes it more difficult for the user. Create your connection in your code and all should be fine. – Sean Lange Nov 27 '17 at 17:38
  • Sorry if it's not clear enough Sean Lange , but I said that I just want to spare the user to create the ODBC connection, hide my SQL Server credentials and, for that happens, create the connection hard coding it. How can I create the connection in my code? Do you have any link or example to show me? – jMarcel Nov 27 '17 at 18:02
  • You would create a database connection in your code. Of course the user would never enter this information. – Sean Lange Nov 27 '17 at 19:17
  • I strongly disagree with this dupe. Programatically creating a DSN and moving data from local tables to SQL server in a DSN-less way are just not the same, which is reflected in both the pseudocode and my answer. – Erik A Nov 28 '17 at 07:28

1 Answers1

2

You can use a temporary linked table using tabledefs and querydefs:

(This code inserts all records from the local table Table1 into the remote table Table1)

Public Sub InsertExternal()
    Dim db As DAO.Database
    Dim tempTDF As DAO.TableDef
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set tempTDF = db.CreateTableDef("#Temp")
    tempTDF.Connect = "ODBC;Driver={SQL Server Native Client 11.0};Server=localhost;Database=TestDB;Trusted_Connection=yes;" 'Credentials here
    tempTDF.SourceTableName = "Table1" 
    db.TableDefs.Append tempTDF
    Set qdf = db.CreateQueryDef("", "INSERT INTO [#Temp](Field1) SELECT Table1.Field1 FROM Table1")
    qdf.Execute
    db.TableDefs.Delete "#Temp"
End Sub

Add error handling where needed, and fill in your connection string, field and tablenames.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I would not recommend using DAO. That was replaced by RDO which was replaced by ADO more than 20 years ago. But the basic premise here is definitely the right approach. – Sean Lange Nov 27 '17 at 19:20
  • 4
    @SeanLange This is a Microsoft Access question. Microsoft Access uses ACEDAO for it's internal database engine, up to this date (updated last mid 2017, support for BigInt). This makes DAO the go-to technology when working with the Access database engine. See [this question](https://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007) for more information. Outside of MS Access, DAO is indeed not advisable, but when working with data stored in MS Access, DAO is the obvious choice in my opinion. – Erik A Nov 27 '17 at 20:16
  • And this is inserting data into sql server. :) – Sean Lange Nov 27 '17 at 20:18
  • 3
    @SeanLange locally stored data from Microsoft Access. DAO can natively work with the Access data, allowing you to query both SQL server and Access in a single query. You can't do that with ADO. – Erik A Nov 27 '17 at 20:20