Monthly SQL server table update from Access DB

Posted on 2012-08-13
Last Modified: 2012-08-22
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?
Question by:dimmergeek
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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.
    LVL 35

    Accepted Solution


    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?

    LVL 11

    Expert Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    A short article about a problem I had getting the GPS LocationListener working.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now