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?
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?
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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!
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" & _
";Network=DBMSSOCN].[yourDestinationTable]"
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)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!