Copy data from Access to SQL Server overnight

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?
slimguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
0
pcelbaCommented:
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.
0
Dale FyeCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
@Dale Fye: Does the Linked server works for Access database placed on a different computer?
0
Dale FyeCommented:
@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.
0
pcelbaCommented:
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.
0
PatHartmanCommented:
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.
0
slimguyAuthor 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...
0
Dale FyeCommented:
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.
0
PatHartmanCommented:
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.
0
pcelbaCommented:
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"
0
slimguyAuthor 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.
0
slimguyAuthor 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?
0
pcelbaCommented:
@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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
slimguyAuthor 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...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
@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.
0
slimguyAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.