Query problem

[Bee]

Hi,

I moved my computer to another machine; however, one of my queries is returning a cartesian product because the tables in it are not linked any more.

I don't understand how this happened and why? Any help will be very much appreciated.

B

 

[KenHigg]

If you understand what 'cartesian product' means, seems you should be able to check if you need to (re)link tables in a query -

 

[Bee]

If you understand what 'cartesian product' means, seems you should be able to check if you need to (re)link tables in a query -

Do I need to relink tables in a query each time I move it to a new machine? I actually don't know how to do that.

Why they did not stay linked?
Thank you,
B

 

[gemma-the-husky]

moving it to another machine shouldn't have produced a cartesian product. If your tables are still linked your queries should be ok - if they are not linked you would not have got any results.

Does the query design show connections between your tables in the design pane? What tables are you pointing to? Were the linked tables on the first machine on a shared back end, or on the machine itself?

 

[Bee]

moving it to another machine shouldn't have produced a cartesian product. If your tables are still linked your queries should be ok - if they are not linked you would not have got any results.

Does the query design show connections between your tables in the design pane? What tables are you pointing to? Were the linked tables on the first machine on a shared back end, or on the machine itself?

The query does not show any connections between the tables.
My DB was divided into FE/BE in my machine; when I copied it to this machine, I relinked the FE with the BE.

All of my other queries are still working perfectly apart from this one that has lost it's tables' relationships and is returning a cartesian product. It has also converted all fields' names to Expre n: fieldName !!!

 

[gemma-the-husky]

Obviously it has connected to something, and it seems that the field names in the new connection are different from what was in the previous connection. Because of this it has lost the link between the tables in your query, and cannot find the fields for the query design, so has converted them all to expressions. Maybe it has actually connected to a DIFFERENT table from what you expected. Did you have an old version of the be database somewhere - are the mapped drives the same on this computer as on the first. See what the connection details are for the table

you have currentdb.tabledefs("tablename").connect for the connect string
and currentdb.tabledefs("tablename").sourcetablename to see which table it REALLY connected to. - this is probably the problem.

You could just reconnect the table manually, and reimport the query from the first database, but it is important that you get to the bottom of it, I think, or it may reappear unexpectedly.

 

[Bee]

gemma-the-husky,

I don't know how to check it. What did you mean by "you have currentdb.tabledefs("tablename").connect for the connect string
and currentdb.tabledefs("tablename").sourcetablename to see which table it REALLY connected to. - this is probably the problem.
"

I opened the query and tried to fiddle about with the properties...etc, but I can't get to the bottom line of this.

You are right about the drive, I had C at home. Here I have a different drive name.
Thanks,
B

 

[gemma-the-husky]

its the table in the query that's the problem i think, not the query itself. If you try to open the table in the list of tables, to see what it contains, does this show you anything? [Edit] NOTE: if its a linked table it should show with a black arrow

in a module try a sub, change mytable below to your tablename generating the problem. It has to be in the quotes

sub showconnect

msgbox("connect:= " & currentdb.tabledefs("mytable").connect)msgboxmsgbox("backend table:= " & currentdb.tabledefs("mytable").sourcetablename)

end sub

set your cursor to the start of the sub, and click the run icon at the top - this will run the sub, which will show you firstly, the table connection details, ie the path it is using, and secondly, the name of the backend table.

 

[jkl0]

Are your linked tables pointing to a drive letter "D:\..., E:\..., etc."
If so you might try using the UNC path "\\Servername\..." to link to your tables. Mapped drives don't always follow to well from computer to computer.

Hope this helps?

 

[Bee]

Are your linked tables pointing to a drive letter "D:\..., E:\..., etc."
If so you might try using the UNC path "\\Servername\..." to link to your tables. Mapped drives don't always follow to well from computer to computer.

Hope this helps?

can you explain further please? if I write "\\Servername\...", I don't have to change my path whenever I move the table or what?

Thanks,
B

 

[Bee]

its the table in the query that's the problem i think, not the query itself. If you try to open the table in the list of tables, to see what it contains, does this show you anything? [Edit] NOTE: if its a linked table it should show with a black arrow

in a module try a sub, change mytable below to your tablename generating the problem. It has to be in the quotes

sub showconnect

msgbox("connect:= " & currentdb.tabledefs("mytable").connect)msgboxmsgbox("backend table:= " & currentdb.tabledefs("mytable").sourcetablename)

end sub

set your cursor to the start of the sub, and click the run icon at the top - this will run the sub, which will show you firstly, the table connection details, ie the path it is using, and secondly, the name of the backend table.

I don't know what was wrong, but i deleted that copy of the DB that I made. Then, pasted a new copy, linked the BE to the FE and now it seems to work fine.

Your code seems interesting though. I will try it and see what it does.
Thank you for your help,
B

 

[grnzbra]

She has a cartesian product in the one query because within the query, the tables are no longer joined so for each record in one table she is getting a set of every other record in the other table for a record count of table1.recordcount x table2.recordcount. The two tables need to be rejoined.

To rejoin them, in the upper part of the query design view, click on the join field in one of the tables and drag it to its corresponding field in the other table. To do this, of course, you need to know what fields were joined in the first place. Then you need to determine what kind of a join you want. Do you want only those records where you can find the same value in both tables, or do you want all the fields from one table and any matching fields in the other table. To do this, right-click on the join line between the two tables (it helps to have the join line at an angle or verticle rather than horizontal) and selec Join-Type. You will be presented with a choice of three types of joins. Select the one you want.

Or you could do what you did; copy it again. But this doesn't show you how to rejoin the tables.

I have no idea why this type of thing happens, but I don't remember having it happen before I started using Access 2003