• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Monthly SQL server table update from Access DB

I have a database in SQL Server 2008 R2.
I have a third-party data provider which is collecting data for insertion into my database.

The third-party company will provide me an Access database with all data once per month.

I need to create some utility or something that anyone behind a keyboard/mouse can use which will:

1) Delete all records from SQL server table
2) Take all records from Access table and insert them to SQL

Field names/data types will be the same between Access and SQL.

What is the easiest/most robust way to automate this process?
1 Solution
Nico BontenbalCommented:
You could create an Access database that has linked tables that connect to both the SQL server database and the Access database. In this Access database you can create delete and append queries that move the data.
David ToddSenior DBACommented:

I suggest an SSIS package.

Create a job to run package.

User jsut has to start job. This can be from SSMS GUI or a SQL script to start the job. Because the script to start the job is so simple, it could be embedded in a vbs script or a powershell script.

If you do it all from Access, then how to keep the code given that it is coming from a third party?

You can do this with a VB Script ... there is no need for the Access UI or linked tables.  But the first question is what is the format of the Access (Actually a Jet/ACE database) file -- MDB (Access 2003 and prior) or ACCDB (Access 2007 and later).

Here is an example VB Script that gives you the framework as to how to procede if you want to use a VB Script to do it.

Dim dbEngine
Dim wrk
Dim strSQL
Dim strSqlServerTable
Dim strSourceDb
Dim strSourceTable

'Define where 
strSqlServerTable = "[ODBC" & _
                   ";DRIVER=SQL Server" & _
                   ";SERVER=yourServerName\instanceIfNeeded" & _
                   ";Trusted_Connection=Yes" & _
                   ";DATABASE=yourSQLServerDatabaseName" & _

strSourceDb = "c:\yourPath\youAccessDatabaseName.mdb"
strSourceTable = "yourSourceTablename"

'Instantiate the Jet/ACE Engine
'Use: DAO.DBEngine.120 for the ACE 12.0 engine (MDB and ACCDB format)
'Use: DAO.DBEngine.36 for JET 4.0 engine (MDB format from A2000 and higher)
Set jetEngine = CreateObject("DAO.DBEngine.120")
Set workSpace = jetEngine.CreateWorkspace("#DEFAULT#", "Admin", "")
Set db = workSpace.OpenDatabase(strSourceDb)

'Build the DELECT command
strSQL = "DELETE FROM " & strSqlServerTable

'Execute the statement
db.Execute strSQL, 128 '(DAO.dbFailOnError)

'Build the SQL Command
strSQL = "INSERT INTO " & strSqlServerTable & _
         " SELECT * FROM " & strSourceTable

'Execute the statement
db.Execute strSQL, 128 '(DAO.dbFailOnError)

Open in new window

By using a VB Script (which is just a text file with the .VBS extension), you can set up a scheduled task to fire monthly, then the process would be very light weight and automatic.  But if it required for users to be able to initiate the process, then you can place the .VBS script in a network share that is accessible to all users who might perform the process, then they can just dbl-click on the .VBS file to execute the script.


Hope that helps!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now