SQL Server Access using MS Access

[RayMilhon]

I have some Microsoft Access 2010 Databases that are used as a front end to SQL Server as the backend. We used Integrated Access to SQL server up until a short time ago. The SQL Server Database is maintained by our Corporate office and the Integrated access no longer works. So we're now using SQL Server logins to access the data. The problem is I have 2 databases that run an update routine on a daily basis that we require. Since the primary database is at corporate some of the queries we run locally take a good deal of time. I created a couple of make table queries that run automatically so the data for those reports is moved to the local Access database every morning and the reports are generated in seconds instead of hours. Now that the Integrated Access no longer functions these automated routines stop at a login prompt and require me to enter the password before the queries run wasting a couple of hours. Does anybody have an idea on how I can provide the password programmatically so it doesn't wait until I'm at my desk to run?

[pbaldy]

Are the tables linked? You can save the password when you link the tables.

[aytee111]

Put it in the ODBC Connect Str property of the query
ODBC;DSN=dsn;UID=username;PWD=password;DATABASE=db name

[JoeM]

When dealing with a linked SQL database like this, for better query performance, consider using Pass Through Queries, where it actually runs the query on the SQL Server and only passes back the results (instead of retrieving ALL table data and then querying on the Access side).

See:
https://support.office.com/en-us/art...2-6DAC62532A42
https://support.microsoft.com/en-us/...uery-in-access