Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    Printscreens

    And here are the printscreens:



    With local tables:

    Click image for larger version. 

Name:	Form_Ok.PNG 
Views:	15 
Size:	35.3 KB 
ID:	32733


    With linked tables
    Click image for larger version. 

Name:	Form_NOT_Ok.PNG 
Views:	15 
Size:	18.1 KB 
ID:	32734

  2. #17
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  3. #18
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    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!

  4. #19
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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

  6. #21
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @ 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....

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  9. #24
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Fixed it!

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	23.0 KB 
ID:	32740

    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
    Attached Files Attached Files
    Last edited by isladogs; 02-24-2018 at 05:40 AM. Reason: Updates
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #25
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    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)
    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
    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!

  11. #26
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Seiquo View Post
    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!
    Hooray - I would definitely have hit my head against a wall if it had gone wrong for a second time!

    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

    Click image for larger version. 

Name:	RecordsetType.PNG 
Views:	12 
Size:	7.2 KB 
ID:	32746

    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
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #27
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    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 for
    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
    I 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 (?).
    Thank you again ridders52. This thread is finally solved!!!

    Seiquo.

  13. #28
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #29
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  2. Login to linked tables at beginning of code
    By nigelbloomy in forum Programming
    Replies: 3
    Last Post: 09-23-2015, 09:20 AM
  3. Linked Table Manager does not work in 2010 runtime
    By OnlyTrevor in forum Programming
    Replies: 2
    Last Post: 10-25-2012, 02:25 PM
  4. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  5. Using Linked Table Manager --Can't get it work
    By cccgsmith in forum Access
    Replies: 6
    Last Post: 03-11-2009, 06:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums