MS Access Crashing out when accessing MSSQL and MDB linked tables

[ShaneBrennan]

Hi Everyone

This is a really wiered problem I've having and I can not figure out why the problem is occuring.

basically, I have a front end DB with links to backend MDBs and a MSSQL server - works great as long as the form I am looking at only contains tables from the MDB or tables from the MSSQL server. If I try to display info from both MSSQL and MDB together - MSAccess Crashed spectacularly and wants to repair itself!

On my development PC - it works fine - no problems

On the live PC - that is where I get the errors.


My Configuration:
-----------------
MSAccess 2002
MSSQL Server: MSSQL sqlExpress 9.0.3054
ODBC: using MS SQL Native Client - 09.00.3042

On Live PC
----------
MSAccess: 2003
MSSQL Server: MSSQL 2000 Server
ODBC: SQL Server 2000.85.1128.00


Has anyone seen a similar problem?

Thank you in advance for any help given

Shane Brennan

 

[genomon]

On the live PC - that is where I get the errors

And what do the errors say?

Sure, the early bird gets the worm, but the second mouse gets the cheese in the trap.

 

[ShaneBrennan]

Basically no error at all, just the bog standard - sorry but microsoft Access has unexpetedly encountered a problem, has to close and would you like to repair it?

Since I posted this I have noticed another think.

On a table called tblProjectRoles (in an mdb) which has 3 fields:

Id (autonumber)
Staff ID (integer - which has a lookup to tblStaff (on mssql) - used to retrieve staff name)
Role: Text 255


When I view the table contents I get:

ID Staff ID Role
1

And that's it! - it sticks trying to retrieve the staff members name. Stick there for about 5 seconds then MSAccess Crashes magnificently without an error message

Hope this extra info helps

Thank you replying genomon

Shane Brennan

 

[KenReay]

Have you tried doing a compact and repair on the mdb containing tblProjectRoles (after ensurimg you have a back up of course) ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ShaneBrennan]

Yep. Even imported tables into new mdb, relinked tables, etc.



Shane Brennan