If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query problem with linked SQL tables when importing to new mdb file
I'm using Access 2k with linked tables from MS SQL Server 2k db. (everythign
is fully updated) This system has been running just fine for over a year. No problems. Just recently it appeared that perhaps the mdb might have become corrupt so I did what I used to do with my older Access apps. I created a brand new empty front end mdb file and imported all the local tables, queries, forms, etc. into the new one from the old copy. Everything imported just fine (no errors came up during the import). Here's the problem. All the queries that had 2 or more tables in them with links between fields, the links were blown away AND many fields now were showing things like "Expr1: CustName" in the top row instead of just "CustName". Man this really fouled things up. I've never seen this before. Whenever I've had to import a front end mdb into a new one where the backedn tables were jet tables, all was fine. Does anyone know why this would happen? I tested it several different ways and alwasy got the same result. One other note. I'm running Access 2003 but the system I'm referring to is all in Access 2000 format. When I create the new empty mdb file it's also showing Access 2000 file format so I wouldn't exepct that that could be the problem but I just figured I'd point this out. I'm more convinced that it has something to do with the fact that the linked tables are SQL and not Jet. Also, of course when I import the old front end ojbects into the new mdb file, I'm not importing the linked tables. they just get relinked in the new front end once it's got all the other objects imported. I run my linking code and all is fine except for teh fouled up queries. It's almost as if during the import, Access opens up each query and because it can't find the tables it changes the layout of each one as if the talbes were not there (like when you forget to link your tables and open up a query it's goofed up and then when you go to close the query, Access asks if you want to save the changes (of course you say no because you didn't make any changes, it's just because your tables are not linked). Again, I never have had this happen when the linked tables are Jet. Thanks, Keith |
#2
|
|||
|
|||
Here's something interesting. I just tried this. If you import everything
except the queries, then link the talbes, then import the queries, all seems to be ok. But I'd be very concerned about embeded queries in form or report recordsources or combo or list boxes. It will take some testing to see if those get fouled up. Keith |
#3
|
|||
|
|||
Well, I added a report to the original mdb file that has a complex select as
its source (2 tables with an outer join) rather than a stored query and also added a combo box to a new form where the combo box has the same source as the report (so it's not a stored query either). When I imported those objects along with the everything else and then rellinked the tables AFTER the import, their queries were NOT messed up. So from what I can see, it only seems to affect stored querys. So there seems to be a work around here, but I'd sure like to know what the problem is and if there are any other hidden issues to be considered that I might not be aware of. Thanks, Keith |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create a sum of records in an open recordset(Access97) | Reiner Harmgardt | General Discussion | 0 | March 7th, 2005 10:03 AM |
DSN for Linked SQL Tables | Jennifer | General Discussion | 1 | January 19th, 2005 10:36 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
MDb not working the same on SQL Server | JMorrell | General Discussion | 18 | August 18th, 2004 05:13 PM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |