| Author |
Topic |
|
Robwhittaker
Yak Posting Veteran
85 Posts |
 Posted - 2003-09-29 : 07:05:00
|
| Sorry if this is in the wrong place, but I'm not sure where to ask.Ok, We have an application that is an Access front end with a sqlserver 7 database at the back. The whole application used to be access but was then converted at some point but I'm not sure how.The connection to sqlserver is done using linked tables, that link with an ODBC connection, which uses a userid and password. This all works fine.When I try to migrate the database to SqlServer 2000, I get lots of connection problems, the connection tends to work, but then if the user runs a query to search for a record, the connection drops out and the whole thing dies in a heap.I think that this is a compatibility issue, between sqlserver 2000 and Access 2000.Does anybody know of any such issues and how the can be resolved. Are there any patches that could fix my problem?ThanksRob |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-29 : 11:01:19
|
| Are you trying to convert from SQL Server 7.0 to SQL Server 2000 or from Access 2000 to SQL Server 2000? If it's the first, then how did you upgrade your database? Did you use DETACH/ATTACH, BACKUP/RESTORE, database upgrade wizard... Also, did you installl service pack 4 for SQL Server 7.0 yet?Tara |
 |
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-09-30 : 03:49:11
|
| We have multiple boxes, so I'm actually only copying the database from a 7 to a 2000 box, it doesn't matter if I use DTS or do a detach/attach. The Access front end is unchanged except linking to the tables on a different box, which works fine. I'm not sure what service pack the sqlserver 7 is at but it is version 7.00.1063, the sqlserver 2000 is SP3.Somebody has made comments to me that there maybe problems linking access 2000 to sqlserver 2000, caused by Access being written first, and that there might be a fix for this, but I've been unable to find anything concrete about this, although a few others on the internet hint of similar things. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-30 : 09:54:25
|
| Try installing the latest version of mdac on the client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-30 : 12:38:29
|
| Is the problem with the upgrade or is it after the upgrade has successfully completed?Tara |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-10-01 : 03:28:20
|
| The problem is only eith this specific application after I upgrade the database, The database and all the data is fine, if we point the application at the sql server 7 database it's fine. It's only a problem with the application /sql server 2000 combination. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2003-10-06 : 17:21:18
|
| Access isn't timing out is it? There is a setting in Access 97 that lets you set this (probably in Access 2k too)steveSteve no function beer well without |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-10-08 : 06:39:18
|
| Thanks for the info, it seems to be more complex that that, mainly due to our environment. It seems to be a clash of architectures, we are deploying access via a windows server farm, where the users log on using Citrix program neighbourhood. The application is set up so that all the users that run it point to a single application on the file system, which I know isn't a great idea, but I didn't design the system. The issue with this is that I think Access is writing a registary setting per user, but as the users are logged on to an app server and not a PC, it gets confused. I don't have any problems running from my PC.We've raised a call with microsoft, but if anybody has any bright ideas please can you let me know.Rob |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2003-10-08 : 10:36:42
|
| Have you tried relinking the tables once you convert over? |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-10-08 : 10:48:56
|
| Yep, tried all the obvious stuff, it's strange because it connects and then loses it connection when the user runs a particular form that searches the database. It then won't connect and access falls over in a heap.For a bit of history that might or might not be relavent, orginally this application was soley access 2000, then converted to sqlserver/access forms using the upsizing wizard (again nobody ask me what was best!) |
|
|
|
|
upgrading to sqlserver 2000 compatibility issues