Linking data from Access to SQL Server

Question

We have an Access database and UI that's running reliably. The accdb file is stored on a network drive visible to my SQL Server. I'd like to link that data to my SQL Server. CRUD that happens in the Access DB (via the Access UI), I would like to show up in the SQL database. In this situation, the Access DB is the master, and SQL Server is the slave. Is this possible?

Thanks

Answer 1

I think it is usually done the other way around; Access FE and SQL Server BE, but yeah, you can do it the way you described.

You should probably bone up on some basic things first.

https://www.mssqltips.com/sqlservertip/2484/import-data-from-microsoft-access-to-sql-server/

https://support.office.com/en-ie/article/Import-or-link-to-data-in-an-SQL-Server-database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

Now, to push, or pull, the data, create a Stored Procedure and suck in whatever you need from Access.

CREATE PROCEDURE dbo.uspCopyTable
AS

INSERT INTO
dbo.mytable (myfield)

SELECT
myfield
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\mypath\mydb.mdb';'admin';'mypwd', mytable)
GO

fire it off by something like this

dim cn as adodb.connection
set cn = new adodb.connection
cn.open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
cn.execute "dbo.uspCopyTable"

OR, use VBA to push the data from Access into Excel.

Make new table:
Dim strsql As string
strSql = "SELECT * Into YourNewAccessTableName FROM ExistingSqlTableName"
Currentdb.execute(strSql)
Insert into existing Access table (assume fieldnames are the same:

Dim strsql As string
strSql = "INSERT Into YourAccessTableName SELECT * FROM ExistingSqlTableName"
Currentdb.execute(strSql) 

Answer 2

You could link the SQL Server tables via ODBC to your Access application.

Then, in this, create queries and/or code that append/update the data from the Access tables to the SQL Server tables.