BUG: TFS 2010 & Access 2007 - problem with queries

Question

Hi,
we have a big problem with TFS 2010 in connection with Access 2007. We get the following error when we try to Create from Team Foundation in Access 2007. It happens on complex queries like:

SELECT qryRO_SR.PAR, qryRO_SR.POL, qryRO_SR.ORG, qryRO_SR.Txt, qryRO_SR.MADATI, 0 AS DAL, tRO_Kontrola.bChybaMADATI, 0 AS bChybaDAL, qryRO_SR.NejvyssiUrP, qryRO_SR.NejvyssiUrV, qryRO_SR.ID_Par3Uc11
FROM qryRO_SR INNER JOIN (SELECT tRO_SR_pod.ID_Par3Uc11, tRO_SR_pod.ID_ParentP, RO_SR.MADATI<tRO_SR_pod.MADATI AS bChybaMADATI
FROM RO_SR LEFT JOIN (SELECT RO_SR.ID_Par3Uc11, RO_SR.ID_ParentP, Sum(RO_SR.MADATI) AS MADATI
FROM RO_SR
WHERE RO_SR.NejvyssiUrP=False
GROUP BY RO_SR.ID_Par3Uc11, RO_SR.ID_ParentP
HAVING RO_SR.ID_ParentP Is Not Null)  AS tRO_SR_pod ON RO_SR.ID_RO_SR = tRO_SR_pod.ID_ParentP
WHERE RO_SR.MADATI<tRO_SR_pod.MADATI)  AS tRO_Kontrola ON (qryRO_SR.ID_Par3Uc11 = tRO_Kontrola.ID_Par3Uc11) AND (qryRO_SR.urParentP = tRO_Kontrola.ID_ParentP)
ORDER BY qryRO_SR.ID_Par3Uc11, qryRO_SR.RO_SR_like_DAO, qryRO_SR.ORG;

When we try to Create from Team Foundation the error message is:

The field is too small to accept the amount of data you attempted to add.

It only happens with complex queries and it appears randomly. When this happens we must go through the following procedure in Access 2007 (on a different computer) to fix the problem:
1. Rename the original query.
2. Create a new query and give it the original name.
3. Put a single SELECT SQL-string into the query, eg. SELECT 1;.
4. Save the query and check-in the changes into TFS.
5. Reserve the query.
6. Open the query in SQL view.
7. Copy & paste original SQL-string into the query.
8. Save the query and check-in the changes into TFS.

Then a colleague has to try to Create from Team Foundation in Access 2007 on his/her machine again. The procedure works just fine, but we might get into SERIOUS PROBLEMS in some situations.

Thank you very much for your time in advance.

---

Vladimir Cvajniga

Answer 1

Vladimir,

I'm not very familiar with how Access 2007 integrates with TFS version control. Would it be possible for you to capture a video of the entire scenario (including the problem)? You can use a free screen capture tool like CamStudio.

Thanks,
P. Kelley

Answer 2

I think this si actually unrelated to TFS and more related to a few known issue sin Access wrt Memo fields:

http://support.microsoft.com/kb/896950

---

My blog: blog.jessehouwing.nl

Answer 3

Hi, Jesse,

it seems as if it is related to the problem you mentioned. But:

1. It occurs at the moment of loading objects (queries) from TFS.

2. It occurs randomly.

3. It only occurs on complex queries, ie. it doesn't happen when loading a query with a simple "SELECT" query.

---

Vladimir Cvajniga

Answer 4

Vladimir,

I'm not very familiar with how Access 2007 integrates with TFS version control. Would it be possible for you to capture a video of the entire scenario (including the problem)? You can use a free screen capture tool like CamStudio.

Thanks,
P. Kelley

Thank you very much for your response.

I'll create the video as soon as the problem appears again. I use Microsoft Community Clips to create videos.

---

Vladimir Cvajniga

Answer 5

Hi Vladimir,

How about the issue, is it solved?

Best Regards,

---

Cathy Kong [MSFT]
MSDN Community Support | Feedback to us

Answer 6

No, it is not. I'm gonna ask my colleagues to create a few complex queries in the hope the problem appears again. Then I'll be able to create the video.

---

Vladimir Cvajniga

Answer 7

We cannot reproduce the problem at the moment. It happens randomly.

---

Vladimir Cvajniga

Answer 8

Hello, Kathy,

the same problem appeared a few days ago. Here are the videos:

1. Complex query problem 5,08 MB
2. Cannot get new version after Create from FT 708 kB
3. How to repair the project 11,3 MB
4. Re-create from TF to test whether the problem is solved 4,92 MB

Notes

1. Watch parts between 3:25 and 3:45 plus 5:50 and 6:05.

2. TF provider cannot get new versions of complex queries after Create from Team Foundation.

3. We experienced problems when we tried to import or copy & paste complex queries from a backup copy of the project. Following steps show you how to avoid similar problems. We use the same procedure for each query:
- Delete query in TF via Team Explorer.
- Create new query with the original name. Query holds a very simple SELECT in its SQL-string.
- Store the query into Team Foundation. Why is it taking so long to add a simple query to TF? See Access 2007 extremely slow with TFS 2010.
- Reserve the query.
- Open the qurey in desing view.
- Switch to SQL view.
- Paste & copy SQL-string from a backup copy of the project.
- Store the query in TF. I have noticed that it's better to stay in SQL-view when we store the query - it produces less errors afterwards when we Create from TF again. Weird, isn't it?

4. We need to re-Ccreate from TF to test whether all objects, incl. newly added complex queries, perform OK.

As you can see, it's quite complicated procedure and it takes ages to repair the project.

IMHO, one of the reason for query damage might be MS Access' "inteligence". Access adds brackets into SQL-strings but it doesn't do it correctly. See  Access 2007 bug: brackets in queries.

Here are SQL-strings of complex queries shown in the videos:
qryRO_SR_kontroly_SumP

SELECT qryRO_SR.PAR, qryRO_SR.POL, qryRO_SR.ORG, qryRO_SR.Txt, qryRO_SR.MADATI, 0 AS DAL, tRO_Kontrola.bChybaMADATI, 0 AS bChybaDAL, qryRO_SR.NejvyssiUrP, qryRO_SR.NejvyssiUrV, qryRO_SR.ID_Par3Uc11
FROM qryRO_SR INNER JOIN (SELECT tRO_SR_pod.ID_Par3Uc11, tRO_SR_pod.ID_ParentP, RO_SR.MADATI<tRO_SR_pod.MADATI AS bChybaMADATI
FROM RO_SR LEFT JOIN (SELECT RO_SR.ID_Par3Uc11, RO_SR.ID_ParentP, Sum(RO_SR.MADATI) AS MADATI
FROM RO_SR
WHERE RO_SR.NejvyssiUrP=False
GROUP BY RO_SR.ID_Par3Uc11, RO_SR.ID_ParentP
HAVING RO_SR.ID_ParentP Is Not Null)  AS tRO_SR_pod ON RO_SR.ID_RO_SR = tRO_SR_pod.ID_ParentP
WHERE RO_SR.MADATI<tRO_SR_pod.MADATI)  AS tRO_Kontrola ON (qryRO_SR.urParentP = tRO_Kontrola.ID_ParentP) AND (qryRO_SR.ID_Par3Uc11 = tRO_Kontrola.ID_Par3Uc11)
ORDER BY qryRO_SR.ID_Par3Uc11, qryRO_SR.ParPol_like_DAO, qryRO_SR.Org_like_DAO;

qryRO_SR_kontroly_SumV

SELECT qryRO_SR.PAR, qryRO_SR.POL, qryRO_SR.ORG, qryRO_SR.Txt, 0 AS MADATI, qryRO_SR.DAL, 0 AS bChybaMADATI, tRO_Kontrola.bChybaDAL, qryRO_SR.NejvyssiUrP, qryRO_SR.NejvyssiUrV, qryRO_SR.ID_Par3Uc11
FROM qryRO_SR INNER JOIN (SELECT tRO_SR_pod.ID_Par3Uc11, tRO_SR_pod.ID_ParentV, RO_SR.DAL<tRO_SR_pod.DAL AS bChybaDAL
FROM RO_SR LEFT JOIN (SELECT RO_SR.ID_Par3Uc11, RO_SR.ID_ParentV, Sum(RO_SR.DAL) AS DAL
FROM RO_SR
WHERE RO_SR.NejvyssiUrV=False
GROUP BY RO_SR.ID_Par3Uc11, RO_SR.ID_ParentV
HAVING RO_SR.ID_ParentV Is Not Null)  AS tRO_SR_pod ON RO_SR.ID_RO_SR = tRO_SR_pod.ID_ParentV
WHERE RO_SR.DAL<tRO_SR_pod.DAL)  AS tRO_Kontrola ON (qryRO_SR.ID_Par3Uc11 = tRO_Kontrola.ID_Par3Uc11) AND (qryRO_SR.urParentV = tRO_Kontrola.ID_ParentV)
ORDER BY qryRO_SR.ID_Par3Uc11, qryRO_SR.ParPol_like_DAO, qryRO_SR.Org_like_DAO;

---

Vladimir Cvajniga