Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-07 : 10:17:31
|
| Peter M. Riggs writes "OS: Win2k sp1DB: MsSql7 sp3 (Jet4.0 sp5 also applied)I created a Linked server to an mdb file created with Access 97 (we're in transition to Microsoft SQL 7). I could access the tables just fine - for a while. Then suddenly, some time later during my tsql programming, it stopped working and returned the following error message:[SQL Server Enterprise Manager]Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.To make a long story short, it looks like each time the database services are restarted, it starts working again - for a while.Has anyone heard of this situation before, and do you know of a way to fix it other than re-installing the OS (and everything else) from scratch? (I've already applied all of the Service Packs I know to apply.)" |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-07 : 11:37:01
|
| I've not seen this or ever had trouble connecting Access to SQL via linked server. Make sure your MDAC versions are the same on both boxes.Why are you starting and stopping your database service? |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-07 : 18:12:12
|
| Might I also suggest upsizing your tables out of Access and into SQL, and then, if you still have forms/reports/queries in Access that need the data, create Linked Tables from Access to SQL Server instead of going the other way around.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-07 : 20:56:40
|
| Good point - SQL is better place to store the data. That's what we've done with a couple of apps. Moved the data to SQL where we've got good backup, etc and kept the forms and reports on Access. Works well. We did one project 6 months ago and I've only had one support call. (user forgot his passwoord) |
|
|
|
Peter M. Riggs
Starting Member
2 Posts |
Posted - 2002-01-07 : 22:18:28
|
| Thank you for your replies.We, unfortunately, can't go this direction even though we are in TRANSITION to SQL, because there are more than 15 Access databases and many of those have extensive VBA programming and Access-specific features used. To make matters even more complicated, 200+ users are using various combinations of these databases. To roll out and support the ODBC connections on the 250+ machines that these users use would be more difficult than Reinstalling the server (btw we have a smaller test/development server that performs these functions just fine).We are actually restructuring out data by entity rather than application at the same time.The MS SQL server is accessing the Access Mdb file via mapped drive. I don't see how this is effected by MDAC. Maybe you could help me understand MDAC better. Does it stand for Microsoft Data Access Components?The database services are restarted to regain access the MsAccess database files through their respective linked servers (or to try reinstalling a Service Pack etc.).Any other help or ideas you can provide would be useful. (I can't help wondering why it works for a while and then stops?)Maybe I just have a server reinstall in my near future.Edited by - Peter M. Riggs on 01/07/2002 22:21:46 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-08 : 12:31:39
|
quote:
The MS SQL server is accessing the Access Mdb file via mapped drive. I don't see how this is effected by MDAC. Maybe you could help me understand MDAC better. Does it stand for Microsoft Data Access Components?
Peter, yes you are correct, MDAC does stand ofr Microsoft Data Access Components. MDAC has been the apparent culprit of various inexplicable errors, and is compounded by the fact that Microsoft is not the only provider that alters the MDAC drivers on your machine. Other data-related software might also make "updates", so they can get messed up pretty easily.However, since things seem to work okay for a while and then stop, I would not initially think that MDAC is the culprit (but it might be a good idea to check versions just in case). Can you determine any correlated activity such as length of time since last reboot, volume of transactions, number of users, specific tables accessed, etc.?--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
Linked Server to Access97 MDB stops working after a while