I think I have figured out what's causing this; I just don't understand why at the moment. It's not a matter of compact/repair (tried that first).
I was able to create a local copy of the local table via a make table query and all of a sudden it works (at least the query behind the non working form works). When I looked at the new table, I see one property that the make table query didn't apply. When I manually apply that property, it goes back to not working, so I suspect there may be a difference between your linked and non linked tables. I'd like to see if I can figure out why the difference in the characteristics of the 2 tables produces the problem before I say what the problem might be, because no doubt, you'll want to know why and I don't know yet. The language might slow me down. The query behind the blank form is Rq_SelectProjet? This is the one I'm playing with.
If I remember well (I’m not home at the moment with a cell phone that autocorrects m’y English for French so I am suffering enough ) yes Rq_Select is behind the form. For any help with the language, don’t hesitate to ask away!
Thanks for this - I won't have time to look at it tonight
Currently completing an update to my own UK Postal Address app ready to upload to website.
By all means do carry on with this database if you have time / inclination to do so
Seems to not matter if the local table is local or not. This is what I've come up with so far:
What matters is that at least that one table doesn't have a PK. For this post, assume it's removed from only the local table.
Remove the PK and it works. Put it back on, it fails.
With it on, change the join to left and include both fields, you get data from one side. Reverse join, you get data from the other side.
Put equal join back on and it fails.
Delete all the records from local table and copy the PK field values from the linked. Paste append into the local table. PK on - no records. PK off, works.
Compared all properties of the 2 fields (data type, size limit, all the same).
Used make table query to create a local table from the linked. Of course, a simple MT query doesn't automatically create a PK field, so query then works. Put PK on, doesn't work. The only other difference between them is that the MT table has Unicode Compression = False and the linked = True, but changing local to yes makes no difference.
It is as if the field data is equal only if one doesn't have a PK, in spite of using MT query and copy/paste append to have the exact same data in each table.
I have no idea why and get no Google results on the subject, but there's so many search terms you could use, so no results match so far. The short version is, if you can remove the PK from one of the linked fields from one of the tables then maybe you'd be OK.
Last edited by Micron; 02-23-2018 at 05:42 PM. Reason: spelin and gramur
@ Micron
Nice job of troubleshooting/ analysis.
I've been following this thread because I have linked tables..... but I've never had any problem with VBA execution.
I had/have Access FE and Access BE - no problems with VBA execution (logic - yes.... sometimes )
I have Access FE and SQL Express BE on a server - no problems with VBA execution.
Every one of my tables has an autonumber PK field.
I NEVER use a MT query in production dBs. I use the MT query once to create the basic table, then delete/append records to the table.
As an aside - not one module has "Option Explicit" at the top of the module.
Amazingly, there is only one undeclared variable....
I only did that in an attempt to ensure the data was the same from one table to the other, and once I had the table, didn't use it again so I'm not sure how to interpret what you're saying. Seeing as how all indications on the query side of things were that the fields were not equal even though they appeared to be, I was attempting to ensure the data in both tables was exactly the same, thinking that there could be hidden characters in one or the other.
UPDATE
I created a new db with 2 tables, both with Short Text Pk's and added the same values to each. Created a new fe and linked to those tables, used the same query design and it works. OK, so they are both linked. Imported one table and removed the same version that was linked. Now 1 is local, the other linked. Query runs fine. Both table PK fields are the same as the originals, right down to the size limit (which I also left at 255 at first, then when it worked, thought could it be the 10 size limit - NO). For the moment, I can only conclude that there is something wrong with the originally provided linked table itself (tb7_Projet).
Next, I might
- recreate the posted be by importing all but leave out the "problem" table, tb7_Projet
- manually duplicate tb7 and enter same data via keyboard, then try the query again.
- if still a problem, then since even creating my own table earlier didn't work when tb7 was involved in the query....
BANG HEAD ON TABLE UNTIL PROBLEM GOES AWAY.
Fixed it!
I was suspicious when I opened the file and went to relink the tables
I found that the linked table list was empty which shouldn't happen even if the links are broken
After checking the issue was as described, I decompiled both databases
Somewhere or other there was corrupt code which decompiling fixed
That's all I needed to do!
Thanks to micron & ssanfu for guiding me in things that didn't need to be tested as you had already done so
UPDATE Fri night late
Just reopened the file here after sending it - the form & query are empty again!
Glad I posted an image whilst it was working
UPDATE 2 a few minute later still
Got it back again
I didn't do this last time but if you change the form recordset to snapshot, it works. Similarly with the query
If you use dynamet or dynaset inconsistent it doesn't work
I'ts too late here to think about why this is but I would suspect the yes/no field is somehow the issue
UPDATE 3 the next morning
I haven't looked at it again...just in case it stops working again...
However I do question the whole point of this exercise.
Whenever data is duplicated, there needs to be a very good reason to justify doing so.
What is the reason in this case?
Why cant you just add the relevant fields to the source table in the linked database
Then run the query and form based on that?
In which case the function used to import data is superfluous and could be scrapped
Last edited by isladogs; 02-24-2018 at 05:40 AM. Reason: Updates
Good morning Ridders52 and Micron,
To Micron:
I appreciate so much the effort you put in helping me and all the testing, trial and error! Reading you I was feeling almost embarrassed to put you through this but you managed to make me laugh out loud with the "bang head on table until problem goes away"
Thank you very much!
To Ridders:
I'm bowing low...
I just opened your file and yes, I can confirm that the thing is finally working!
I "owe you one", and I am so thankful for your help and the time you spent on my problem.
May I ask you two last questions, though, since there are two points in your solution that I don't understand very well:
1) did you just compile the code? What do you mean by "Decompiled"? I must admit I didn't know one could DEcompile a code. How do you do that?
2)Can you please explain what you did and where? I assume I have to do the same to my original DB but I have absolutely no idea of what you are talking about here. Might be a problem of technical language but I rather suspect my knowledge of Access doesn't cover this part!I didn't do this last time but if you change the form recordset to snapshot, it works. Similarly with the query
If you use dynamet or dynaset inconsistent it doesn't work
I promise we are on the last stretch
Now, explanation on the purpose of this form: I picked the whole code of the selection form on internet here. (you will have to allow the translation though, it's all in French).
I am not trying to duplicate records. This form is just meant to allow users to pick the projects they want in order to issue a Financial report on the said projects.
Originally, I tried with the multiselect listbox but couldn't fathom how to write the code or apply the methods I found on internet to my case (and I spent a lot of time trying!). Therefore I went with this solution that responds very well to the users' need. I just hadn't foreseen the whole local/linked table thing... (I even asked the question to the author of the code on the website but I got no response).
Have a very good day all, and THANK YOU!
Hooray - I would definitely have hit my head against a wall if it had gone wrong for a second time!1) did you just compile the code? What do you mean by "Decompiled"? I must admit I didn't know one could DEcompile a code. How do you do that?
2) Can you please explain what you did and where? I assume I have to do the same to my original DB but I have absolutely no idea of what you are talking about here. Might be a problem of technical language but I rather suspect my knowledge of Access doesn't cover this part!
I promise we are on the last stretch
Now, explanation on the purpose of this form: I picked the whole code of the selection form on internet here. (you will have to allow the translation though, it's all in French).
I am not trying to duplicate records. This form is just meant to allow users to pick the projects they want in order to issue a Financial report on the said projects.
Originally, I tried with the multiselect listbox but couldn't fathom how to write the code or apply the methods I found on internet to my case (and I spent a lot of time trying!). Therefore I went with this solution that responds very well to the users' need. I just hadn't foreseen the whole local/linked table thing... (I even asked the question to the author of the code on the website but I got no response).
Have a very good day all, and THANK YOU!
1. Decompiling is the OPPOSITE of compiling & is a VERY useful tool in a developer's armoury.
For details on what it does, see this link: http://www.fmsinc.com/microsoftacces.../decompile.asp
2. To change the recordset type, go to the property sheet - applies to forms/reports & queries
I've had a very quick look at the site and used Google translate which worked very well - I'll read it properly later
For your purposes, I still don't see the point of this approach
All I think you need to do is add the extra fields to the original linked table & then use that for your form
Perhaps I'm missing something obvious
BTW
a) The issue with the linked table manager was very odd - I've never seen a blank list despite tables being linked before
b) It would have helped even more if you had just supplied the form & query concerned together with the 2 tables from each database
From memory, nothing else was needed to make this work
First I am sorry I left too many elements in the file I provided. I thought you may need to have a picture of the whole process to understand the problem. Because I read numerous times in the threads I visited "You should have mentioned that..." or "Why didn't you tell us that...". I thought I was doing the right thing.
Of course I will read the explanation about decompiling and also about the Recordset type and its role in the Access universe. Time spent learning is never wasted...
As forI think you are missing something but I am planning to explore your suggestion since you managed to get me out of my misery. Maybe I, missed something along the way (?).For your purposes, I still don't see the point of this approach
All I think you need to do is add the extra fields to the original linked table & then use that for your form
Perhaps I'm missing something obvious
Thank you again ridders52. This thread is finally solved!!!
Seiquo.
You're welcome and it wasn't a big problem having everything else.
Just thought I'd mention it as I ignored the rest.
It was certainly an unusual problem that I've never seen before
Similarly the linked table manager blank window issue.
You should add Option Explicit to the header of all your modules.
Edit: Oops, didnt even see page 2 before posting. I see Steve already mentioned this.