Copy data from Access to SQL Server overnight

slimguy
slimguy used Ask the Experts™
on
I need to be able to schedule an overnight process to copy data from an Access database to a SQL Server database.

This is currently done manually by a user clicking a button that runs append queries and linked tables in the Access DB. I need to automate the process. The windows scheduler will not allow an Access DB to be run 'unattended'. I have tried all combinations of using .bat, .vbs files, auto-exec macros, and different users (SYSTEM etc), and nothing will execute the Access DB. It needs a window to open in.

Any suggestions please? Would a VBA application work?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
The windows scheduler will not allow an Access DB to be run 'unattended'.
That's not true. People do that all the time. Whether it's wise to do so is another matter, but that's beside the point.

You can run your access database by creating a simple Batch file with a single line like this:

"Full path to msaccess.exe" "full path to your database" /x macroname

Where "macroname" is the name of a Macro in your database that would trigger the copy. Obviously you'd have to change the "full path" statements to point to the relevant files on the machine.

Or you could include that in the AutoExec macro - but then it would fire everytime someone opened the database.

I'm curious however - if you have linked tables, what's the point of copying over data from SQL Server? Linked tables show the most current data, so unless you're copying data from OTHER databases (and cannot link to those databases as well) there seems to be little point in doing this.

Would a VBA application work?
there's no such beast as a "VBA Application". VBA is Visual Basic for Applications, which means there must be an Application in which your VB will run (like Access, or Excel). So you run your Access (or Excel) application, and in turn that application would fire off your VB(A).
Download MS Access Data Engine (if you don't have it already - it should be installed together with Access) and then you may access your Access database from whatever program you write.

The program should do following steps:
1) Define an ODBC or other connection to your Access database
2) Define another connection to the SQL Server (this would need SQL Server Native client - also available for a free but you should have it for Access linked tables)
3) Execute query to retrieve data from Access database
4) Insert data to MS SQL database.
5) Close the program

This is great exercise to start with .NET programming. Visual FoxPro can do this on a few lines of code etc.

Of course, to leave some user logged-in over night and execute the existing MS Access code at given time is much easier solution for you at this point.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Personally, I simply use SQL Server to do this.

In SSMS, you can create a linked server to your Access database by expanding the "Server Objects" in the Object Explorer.  
- Click on Linked Servers to open the New Linked Server dialog box.  
- Provide the name you want to use to refer to the server.
- Click the Other data Source radio button
- Select the Microsoft Office XX.0 Access Database Engine as the Provider
- Enter "Access" as the ProductName
- Enter the full path (UNC) to the database as the Data Source

Once you have the Access database recognized as a Linked Server, you can refer to the tables as:

[ServerName]...[TableName]

Recreate the processes you have running in Access within one or more SQL Stored Procedures.  Then create a SQL Server Agent job to run that/those SPs at a specific time.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

@Dale Fye: Does the Linked server works for Access database placed on a different computer?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
@pcelba,

As long as it is on a network server, which is where you would normally put an Access back-end anyway, I have not had a problem with this method.  Have never tried, but seriously doubt you could point it to an Access db sitting on a users hard drive.
Thanks. The most problematic seems to be access rights for linked servers. SQL Server running under an account restricted to the SQL databases does not allow linked data connections obviously.
Distinguished Expert 2017

Commented:
You could also look at the scheduler tool offered by www.fmsinc.com
I haven't used it but I use several of their other tools and find them to be quite useful.

Author

Commented:
Yes, a .bat file will open the database and run a macro, but if you run it from the Windows Scheduler (either as yourself or SYSTEM), it hangs in a 'running' state. Alternative schedulers have the same problem.

Also, if Access encounters a problem, or puts up a dialog box, there is no one to respond to it.

The Access DB is on a server with multiple users logging in to use it. The SQL Server DB is on another hosted server supporting a website. I'm not sure if or how the SQL server could 'see' the Access database to pull the data.

I thought there might be an alternative to Access that would copy the data over that would not have these limitations...
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As I indicated above, the SQL Server will "see" the Access database when you create a Linked Server in SQL Server Management Studio.

linked serverRight click on Linked Servers and select new.  Then follow my instructions in my previous post.
Distinguished Expert 2017

Commented:
Yes, a .bat file will open the database and run a macro, but if you run it from the Windows Scheduler (either as yourself or SYSTEM), it hangs in a 'running' state. Alternative schedulers have the same problem.
I have a process that runs every night.  It's only failed a couple of times in two years and both times it was because there was some network problem.  Of course, your app can't be popping up messages.  There won't be anyone to respond.  You need to log errors to a table or write them to a text file.

The optimal solution is having SQL Server run the job but if you don't have permissions and you already have the batch file set up, we can probably help you with the settings or you can get the product from FMS and not use Windows scheduler.
I would try to use PSEXEC from PsTools (https://technet.microsoft.com/en-us/sysinternals/bb896649.aspx).

PsExec allows to start a batch or other program on a remote computer in an interactive session. It should also work on the local computer under the Task Scheduler. Just test it with different switches.

Some discussion about this approach is e.g. here.

PsExec also needs to push some character when executing interactive program remotely sometimes. Following batch is called to execute PowerShell script remotely (not the echo.| at the beginning):

echo.| C:\Windows\Sysnative\WindowsPowerShell\v1.0\powershell.exe -executionpolicy unrestricted -File "%~3\Scripts\CreateIISapp.ps1" "%~1" "%~2" "%~3" "%~4"

Author

Commented:
Dale, two problems:
1. When creating a linked server is SSMS, there is no provider for Access. Does this need installing?
2. How do I create a 'UNC path to the data source' when the Access db is on another remote server? Access does not 'listen' for incoming requests like SQL Server does.

Author

Commented:
PatHartman,

The bat file I use is:
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "E:\Oceanus\Energycentric\Temp\TestUpload.accdb" /x macIsrt

Running from the desktop opens a command window, then the database opens on the desktop and the macro runs (which also quits the database). Running this from the scheduler (as SYSTEM) hangs, I suspect because there is no desktop. How do you do it?
@slimguy: "there is no provider for Access"  - My first post stated "Download MS Access Data Engine (if you don't have it already)"  The Data Engine must be installed at the server in this case.

UNC path = \\ServerName\ShareName\path\filename.ext  and remember this path must be accessible by the user under which is the SQL Server running.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Also, if Access encounters a problem, or puts up a dialog box, there is no one to respond to it.
Which is the very reason I wrote "Whether it's wise to do so is another matter, but that's beside the point.". You must be very, very careful to write the application so that it can successfully shut down/close/complete in the case of an error, as Pat mentioned earlier. If you're trying to schedule a task for a database created for direct user use (i.e. with Forms, Reports, etc) then you're often just asking for troubles. The better solution for this is to create a new database and include only the functionality that you need, without forms, reports or any other UI conventions. Include robust error handling, and don't use messageboxes for any reason (since they'll hang the process).

Also, in many cases it's better to do this from a workstation instead of a server. Servers rarely have the necessary components to run an Access app, while a workstation typically has a full install of Office.

The SQL Server DB is on another hosted server supporting a website. I'm not sure if or how the SQL server could 'see' the Access database to pull the data.
If the two are not on the same physical network (or somehow "mapped"), then you won't be able to create a linked server (and you very likely wouldn't have the permissions to do so on a hosted server anyway). Access is a fileserver type of database, and in order to use it, the "resource" (in this case, SQL Server) must be able to "see" that file. Unless you've mapped the drive hosting that SQL Server (on your webhost) to your local network, you won't be able to do this.

Author

Commented:
The Access server and the SQL Server are separate, remote, hosted servers, with no network connection between them. I think this excludes the 'linked server' solution.

If someone can explain how to run the bat file from the scheduler, and without the need for a currently logged-on user (ie as SYSTEM), I could make that work.

Alternatively, some application other than Access that can be scheduled to run on the Access server with connections to both Access and SQL Server...
Most Valuable Expert 2012
Top Expert 2014
Commented:
For any task you can define which User Account to use when running the task, and whether to run whether the user is logged on or off. It's been a while since I've set one up for Access, but basically you should use an Admin-level account (or equivalent), and set the task to run whether the user is logged on or off.

On my Windows 10 machine, I can change that account on the General tab of the task.

"Some other application" could be any number of things, but those would require quite a bit of work. If you can get the Access method to work, you would be better off.
Distinguished Expert 2017

Commented:
@slimguy,
I am not at the client where I run the batch job.  I'll look at the setup on Friday and post it if your question is still unresolved.

Author

Commented:
I think your comments confirm that I need to schedule a job as a logged-in user - not an ideal solution, but no one has suggested any other obvious solution that I have missed.
Thanks for everyone's contributions anyway.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial