Reasons that queries has no result

[ChoonBoy]

I moved all my tables to SQLexpress to take advantage of some of its features.

Now most of my queries have no result. How can this be, I thought the
queries will not be affected if I move just the tables. I did not convert to
ADP as there will be too much changes required and I am not familiar with
most.

Scenario:
Create a one table query (qry1) , I cannot see the result.

Create a group by query using "qry1", I cannot see any result.

This is very strange, please help.

Thanks

 

[Sylvain Lafontaine]

Well, this newsgroup is about ADP, so if you're not using ADP then you
should post in a more appropriate newsgroup such as m.p.access.externaldata
or m.p.access.odbcclientsvr.

However, in your case, the first thing to check would be to make sure that
you have transferred the data as well and not only the schema of your
tables. The second thing would be to make sure that each of the upsized
tables still have a primary key defined because the Upsizing Wizard will
often forget to set the primary key on the newly created sql-server table.

There is also the possibility of some incompatibility types between Access
and SQL-Server (such as BIGINT) or a permission issue.

In all cases, if you want to repost, make sure to provide more details; such
as the versions used and the way you have done the upsizing process.

 

[ChoonBoy]

Thanks Sylvain for you quick reply, I will look into your suggestions before
reposting it to the correct group.

Regards

 

[ChoonBoy]

Although I managed to track the problem, I am not comfortable with this
behaviour and appreciate any advice or comment.

Scenario:

Create a one table query (qry1) , I cannot see the result.

Create a group by query using "qry1", I cannot see any result.

Reason:
My qry1 is actuall joined to a union query. All results were shown correctly
(although the union query does not have an equivalent record).

My union query behave in an unpredictable way. It does not show the result
correctly for one particular line of code.

"UNION SELECT [statecode] as [statecodes], [statedesc] as [statedescs1],
[statedesc] as [statedescs2]
FROM [tbl_state];"

The above did not display the [statecode] expected but display [statedesc]
in the first column. Why is this happening?

Regards

 

[Sylvain Lafontaine]

Would you care to show the full query and is tbl_state a real table or the
result of something else?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Although I managed to track the problem, I am not comfortable with this
behaviour and appreciate any advice or comment.

Scenario:

Create a one table query (qry1) , I cannot see the result.

Create a group by query using "qry1", I cannot see any result.

Reason:
My qry1 is actuall joined to a union query. All results were shown
correctly
(although the union query does not have an equivalent record).

My union query behave in an unpredictable way. It does not show the result
correctly for one particular line of code.

"UNION SELECT [statecode] as [statecodes], [statedesc] as [statedescs1],
[statedesc] as [statedescs2]
FROM [tbl_state];"

The above did not display the [statecode] expected but display [statedesc]
in the first column. Why is this happening?

Regards

I moved all my tables to SQLexpress to take advantage of some of its
features.

Now most of my queries have no result. How can this be, I thought the
queries will not be affected if I move just the tables. I did not convert
to
ADP as there will be too much changes required and I am not familiar with
most.

Scenario:
Create a one table query (qry1) , I cannot see the result.

Create a group by query using "qry1", I cannot see any result.

This is very strange, please help.

Thanks