November 9, 2004 at 7:53 am
What I'm trying to do is the following:
SQL server name : SQL
SQL table named : Sales
Using Access as a front-end I'm trying to link the sales table such that my users can run queries and what-not against the sales table via Access (we haven't graduated up to fully integrating all the queries into sprocs as yet.
The problem I'm running into is that I've created a file DSN on each machine that is going to be trying to use the sales table (the access database is a centralized stored database right now).
Everything will run fine for a day or so, and then just sporadically the people begin losing access to the database behind the scenes. Once I go in and simply refresh the tables via access everything works perfectly (for awhile).
Anyone else run into this and have some solutions? I've been researching DSN-less connections and such, but have yet to really find a good in-depth "how-to" when it comes to setting up the linked tables and making them stay linked.
I appreciate any and all help.
November 10, 2004 at 12:15 pm
When you say 'losing access to the database' do you mean they are getting connection errors? If so, what are they? When you 'refresh the tables' in Access do you mean re-link them? A little more detailed info would be helpful.
November 11, 2004 at 7:26 pm
They are receiving the error of basically "ODBC call failed"
And when I say refreh the links, I mean exactly that. Go into linked table manager / highlight the tables and say "Always choose new location" / choose the same DSN as was used to initially set the tables up, and hit Okay. When that is done, then I'm able to once again gain access to the tables,etc inside of MS Access.
November 12, 2004 at 6:19 am
I've never seen linked tables lose their connections, but why don't you try a few things. Create a user or system DSN instead of a file DSN. Change authentication from NT to standard or vice versa. Change from TCP/IP to Named Pipes or vice versa. There are many "ODBC call failed" errors... the exact message might help point you in the right direction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply