Linked Tables

[RayMilhon]

I have a database with a number of linked tables. We have an issue with the SQL Server Database that kills response time. (It's a corporate Database and we have zero control or input as I've complained numerous times concerning response times from our queries.) What I've done is created a couple of queries that takes the 4 corporate tables we use and copy them locally based on Criteria pre-determined to fit our biggest need. I've set up an autoexec macro to run those 4 queries at 6:00 AM so the data is ready when I get in at 8:00 AM. I've created a function in VBA to refresh the links for all of those tables. Plus a couple of others in other databases just to speed up our queries. The problem is that as soon as the accdb file (Access 2010) opens it prompts for the SQL Server Login. The first step in the macro was supposed to run the function. So to test it I put the first step in the macro to display a message box. I still get prompted for the database password before the message box appears. Am I out of luck here? how do I get the autoexec macro to run first?

[ranman256]

youd have to remove all the sql tables, then relink them all in WITH password saved in the ODBC link.
then the macro will run without prompting.

[CJ_London]

I've complained numerous times concerning response times from our queries

are your queries optimised for sql server? no use of domain or udf functions, only return small recordsets, etc

[RayMilhon]

Ranman I will do that today thanks.

Ajax the database was designed by Finance at corporate and they're extremely protective over it. I've never even glimpsed the actual data structure of the tables but looking at the views and running reports over the last year I can make some educated guesses. First thing I've noticed that depending on the department I'm designing the report for the same person can have multiple primary address locations. This design was created to replace 6 different Commercial Databases. I've never been able to see the tables but I believe they just imported the six without merging any data. Since it looks like they didn't even meet the first level of normalization. I only have access to the views and only to read data. I've optimized what I can but I'm severely handicapped for this database.