January 27, 2003 at 4:07 pm
I upgraded from 7.0 to 2000 (SP3) and had a few permissions problems. For one, we use MS Access extensively to talk to SQL via ODBC (no choice in the matter). I prefer the trusted connection (Windows security as opposed to SQL usernames), but now it seems the tables that were ODBC-linked in that manner can't be accessed until being relinked. The error says "SQL Server does not exist or access denied."
When I relink, it seems OK, but there are a lot of tables to do. Questions:
1. Any way to avoid relinking all those tables?
2. Any way to avoid seeing this problem in the future?
January 29, 2003 at 5:57 pm
When MSAccess links tables with ODBC DSN, some info is extracted from the DSN and stored in the 'table'(link) properties. If anything changes on the DSN, the table properties can stay out of date, and you cannot update them. UID is there, that might be your problem.
I once wrote a module to refresh-relink tables, but that was Access95(?) with the DAO model, you have to muck around to get that to work in Access2000. I could post that if you are desparate.
Put it down to the joys - and costs - of maintaining MSAccess.
January 30, 2003 at 9:50 am
quote:
When MSAccess links tables with ODBC DSN, some info is extracted from the DSN and stored in the 'table'(link) properties. If anything changes on the DSN, the table properties can stay out of date, and you cannot update them. UID is there, that might be your problem.
Well, but NOTHING changed here except the upgrade to SQL Server 2000. Even if someone had changed a DSN, that wouldn't affect anything until the table was relinked using the new info in the DSN file, right?
And yes, the UID is in the DSN, but: (1) On a trusted connection, it uses whatever name you're logged in to, no matter how it was linked, right? and (2) On a non-trusted connection where you specify a username & password, we always use the same one everywhere, so that didn't change either.
quote:
I once wrote a module to refresh-relink tablesbut that was Access95(?) with the DAO model, you have to muck around to get that to work in Access2000.
Well, I once did too, and it ran on Access 2000. There was some problem or other, I don't remember what, that we couldn't really use it for the purpose needed at that time. I pulled out the code, and the "core" of it is as follows:
Set TblNew = db.CreateTableDef(NewName)
TblNew.Connect = MyNewConnectString
TblNew.SourceTableName = tbl.SourceTableName
db.TableDefs.Append TblNew
Then it goes through and deletes the old tables and renames the new ones.
Think this ought to work? I just followed the docs--now that I check further, this appears to be DAO, but I didn't start out do to DAO, ADO, or whatever--I just used the first thing I found in the docs to create tables. Any reason why it wouldn't work?
I can try running it, but the larger questions are still my original ones: Why did this happen, and how can we keep from being surprised again?
Just when I thought I had all the "problem" tables relinked, now there are problems with other tables (with a DIFFERENT error msg). And it VARIES BY WORKSTATION; even though it's all the same shared, networked Access database, I can't reproduce the problem on other desktops even if I log in under the same username (and they all have the new SQL 2000 management stuff loaded, which means they all have the newer MDAC too). There's lots of stuff going on here I don't understand, and I don't like being in that position.
February 4, 2003 at 11:22 am
More info: After relinking, I found that all Win2K machines worked fine, but most Win98 ones didn't (only one did). This, in conjunction with a new search on the MS web site, led me to SQL Server's Client Network Utility. I found if I moved Named Pipes in front of TCP/IP on the Win98 machines, it started working! (Even though SQL Server is set to listen on both).
Having now discovered the "Connections" area, I'll pose my questions over there instead of continuing here in the "General" area.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply