MS Access Macro Scheduling

[rajatsingh78]

I have created a SQL Server Database on one machine on my network, on another machine on the same network I have linked the database using MS Access. I have now created a macro to extract the databases on this machine, now the problem is that whenever I schedule this macro (by creating a batch file), the SQL Server login screen pops up and the macro doesnt advance.
How can I put the username/password automatically so that the macro can run in my absence.

Thanks in Advance

P.S following is my batch file to run the macro
Batch file: DailySalesExpor t.bat
REM This runs the macro that exports the reports to the J drive
PATH = "C:\Program Files\Microsoft Office\Office\; C:\Windows\Comm and"
START /WAIT Msaccess.exe "D:\GD SAM BACKUP\GDSAM.md b" /x backup


EXIT

[Jim Doherty]

I have created a SQL Server Database on one machine on my network, on another machine on the same network I have linked the database using MS Access. I have now created a macro to extract the databases on this machine, now the problem is that whenever I schedule this macro (by creating a batch file), the SQL Server login screen pops up and the macro doesnt advance.
How can I put the username/password automatically so that the macro can run in my absence.

Thanks in Advance

P.S following is my batch file to run the macro
Batch file: DailySalesExpor t.bat
REM This runs the macro that exports the reports to the J drive
PATH = "C:\Program Files\Microsoft Office\Office\; C:\Windows\Comm and"
START /WAIT Msaccess.exe "D:\GD SAM BACKUP\GDSAM.md b" /x backup


EXIT

Are you happy that you have an ODBC connection? set up on the network machine from which you are connecting from? I'm guessing here that you created the MDB file on the same machine as the SQL server installation or at least a machine where you did have an ODBC connection in order to create the link originally but that won't exist by default on the other machine if you get me

[rajatsingh78]

Are you happy that you have an ODBC connection? set up on the network machine from which you are connecting from? I'm guessing here that you created the MDB file on the same machine as the SQL server installation or at least a machine where you did have an ODBC connection in order to create the link originally but that won't exist by default on the other machine if you get me

the SQL Server machine is a separate machine, and MDB file is on a separate one (however both are on the same network)

[Jim Doherty]

the SQL Server machine is a separate machine, and MDB file is on a separate one (however both are on the same network)

Is the ODBC connection OK?

[rajatsingh78]

Is the ODBC connection OK?

yes everything is working fine..its just that when the macro runs the screen stops where i require to put in the username & pwd for the SQL server...which i want to automate...coz this macro has to be scheduled to run at a time when no one is near the machine (beyond office hours)..TIA

[Jim Doherty]

yes everything is working fine..its just that when the macro runs the screen stops where i require to put in the username & pwd for the SQL server...which i want to automate...coz this macro has to be scheduled to run at a time when no one is near the machine (beyond office hours)..TIA

Are you using Windows integerated security or SQL server standard security in the ODBC connection.

Does it remain a problem if you set the ODBC connection string to the SQL server main admin login account.

This seems to be an issue that has been discussed elsewhere on the net and comes into play when using task scheduler and at the following posting remains seemingly unresolved

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=46&Number=1479712&Zf=&Zw=macro%20connect%20to%20sql%20server&Zg=0&Zl=a&Main=1360041&Search=true&where=&Zu=&Zd=g&Zn=&Zt=2&Zs=a&Zy=#Post1479712&Zp=

workarounds elsewhere suggest creating connection other than the ODBC driver and using Datashape to open a recordset to establish a connection in ADO to see if the connection succeed in which case you still have a username and password to provide but you can set this in the DSN connection (merely a different method of connection if indeed it is ODBC essentially creating the problem.) The issue obviously is one of authentication not passed over and any usual command line access switches for usr (%profile%) and pwd relate to the MDW file access own security settings which I am assuming you haven't invoked anyway. I am discounting those as they were not in your command line syntax.

I can't replicate the problem instantly on my machines right this minute to follow it through (having a server re-build) but for my info I absolutely want to track this one. I' ll get back to you.

Jim