 | |
A2010: difficulty linking tables
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
A2010: difficulty linking tables -
03-27-2011
, 01:12 PM
#2
| |||
| |||
|
|
I am having problems linking a front end to a back end in Access 2010. *For about a decade I have successfully linked tables in Access 2000 using code based on that published by Litwin and Getz. At Startup, I check that a sample table is connected to its backend table. *If it is not, the user is asked to select the backend data file. *Each table is then connected to this file. I then open two Forms: 1 * * * one (CuurrentUser) is bound to a local table and contains aCombo box with data from the backend; this is used for each user to log in, the combo box offering a list of authorised users and the local table storing the selected user ID. 2 * * * other Form (PermanentConnection) is bound to table in the backend to provide a permanent connection. *The table consists of a single record with a single field. *The Form has one text box linked to this field. Everything appears to work correctly up to the opening of the two Forms. *The CurrentUser form displays nothing in the combo box; and the PermanentConnection form gives rise to an error: *3420 “Object invalid or no longer set”. *If I then click OK on the message box all objects in the Navigation pane disappear. The only way forward from here is to close and then re-open the front end with the shift key down to bypass the startup routine. I can then place a Stop just before the forms are opened. If I thus stop the code after the linking but immediately before opening the forms, I can see all the objects in the Navigation Pane. Opening a linked table shows all the correct data and looking at the table’s properties shows that it is connected correctly. So far so good. However if I try to open a form PermanentConnection in Form view I get the error BUT if instead I open it in Design view, look at its Record source via the Properties and then show the query in DataSheet *view, I see the correct data. *If I now open the form in Form view, it works!! Similarly if I open the CurrentUser form in design mode, look at that Row source of the combo box I can see the correct data. *Closing the form and opening it in Form view shows the correct data in the combo box. In summary, the tables appear to be relinked correctly but the first form I try to open does not have its Record source connected. *Is there something that needs to done in Access 2010 different from in Access 2000? *I have searched this group to no avail, so I suspect that others have not experienced this. *I live in hope. Jim |
I find that it has nothing to do with my code! Even if I use the
Linked Table Manager I still get the same problem.
It is only resolved, whether I relink by code or by the table manager,
by Compacting and Repairing the database.
This I have to do from the ribbon because one cannot compact a
database by code from within itself!!
It still seems strange to me that the problem occurs and that I need
to repair the database.
#3
| |||
| |||
|
|
On Mar 27, 7:12*pm, Jim Devenish <internet.shopp... (AT) foobox (DOT) com> wrote: I am having problems linking a front end to a back end in Access 2010. *For about a decade I have successfully linked tables in Access 2000 using code based on that published by Litwin and Getz. At Startup, I check that a sample table is connected to its backend table. *If it is not, the user is asked to select the backend data file. *Each table is then connected to this file. I then open two Forms: 1 * * * one (CuurrentUser) is bound to a local table and containsa Combo box with data from the backend; this is used for each user to log in, the combo box offering a list of authorised users and the local table storing the selected user ID. 2 * * * other Form (PermanentConnection) is bound to table in thebackend to provide a permanent connection. *The table consists of a single record with a single field. *The Form has one text box linked to this field. Everything appears to work correctly up to the opening of the two Forms. *The CurrentUser form displays nothing in the combo box; and the PermanentConnection form gives rise to an error: *3420 “Object invalid or no longer set”. *If I then click OK on the message box all objects in the Navigation pane disappear. The only way forward from here is to close and then re-open the front end with the shift key down to bypass the startup routine. I can then place a Stop just before the forms are opened. If I thus stop the code after the linking but immediately before opening the forms, I can see all the objects in the Navigation Pane. Opening a linked table shows all the correct data and looking at the table’s properties shows that it is connected correctly. So far so good. However if I try to open a form PermanentConnection in Form view I get the error BUT if instead I open it in Design view, look at its Record source via the Properties and then show the query in DataSheet *view, I see the correct data. *If I now open the form in Form view, it works!! Similarly if I open the CurrentUser form in design mode, look at that Row source of the combo box I can see the correct data. *Closing the form and opening it in Form view shows the correct data in the combo box. In summary, the tables appear to be relinked correctly but the first form I try to open does not have its Record source connected. *Is there something that needs to done in Access 2010 different from in Access 2000? *I have searched this group to no avail, so I suspect that others have not experienced this. *I live in hope. Jim OK, so no-one has this problem. I find that it has nothing to do with my code! *Even if I use the Linked Table Manager I still get the same problem. It is only resolved, whether I relink by code or by the table manager, by Compacting and Repairing the database. This I have to do from the ribbon because one cannot compact a database by code from within itself!! It still seems strange to me that the problem occurs and that I need to repair the database. |
From what I've seen, the trouble involves network paths/connections.
So, some questions:
Are the linked tables on a local folder or a networked share?
Does the path for the linked tables contain a networked drive letter?
Are you running on Windows7 or XP? (or something else)
If you change any of these, does the problem go away?
I'll try to gather more details on my version of the problem next time
I come across it.
The problem isn't everywhere, and goes away upon relinking, so I've
been able to ignore for the time being.
Hope this helps,
Kevin
#4
| |||
| |||
|
|
On Mar 28, 9:30*am, Jim Devenish <internet.shopp... (AT) foobox (DOT) com> wrote: On Mar 27, 7:12*pm, Jim Devenish <internet.shopp... (AT) foobox (DOT) com> wrote: I am having problems linking a front end to a back end in Access 2010. *For about a decade I have successfully linked tables in Access 2000 using code based on that published by Litwin and Getz. At Startup, I check that a sample table is connected to its backend table. *If it is not, the user is asked to select the backend data file. *Each table is then connected to this file. I then open two Forms: 1 * * * one (CuurrentUser) is bound to a local table and contains a Combo box with data from the backend; this is used for each user to log in, the combo box offering a list of authorised users and the local table storing the selected user ID. 2 * * * other Form (PermanentConnection) is bound to table in the backend to provide a permanent connection. *The table consists of a single record with a single field. *The Form has one text box linked to this field. Everything appears to work correctly up to the opening of the two Forms. *The CurrentUser form displays nothing in the combo box; and the PermanentConnection form gives rise to an error: *3420 “Object invalid or no longer set”. *If I then click OK on the message boxall objects in the Navigation pane disappear. The only way forward from here is to close and then re-open the front end with the shift key down to bypass the startup routine. I can then place a Stop just before the forms are opened. If I thus stop the code after the linking but immediately before opening the forms, I can see all the objects in the Navigation Pane. Opening a linked table shows all the correct data and looking at the table’s properties shows that it is connected correctly. So far so good. However if I try to open a form PermanentConnection in Form view I get the error BUT if instead I open it in Design view, look at its Record source via the Properties and then show the query in DataSheet *view, I see the correct data. *If I now open the form in Form view, it works!! Similarly if I open the CurrentUser form in design mode, look at that Row source of the combo box I can see the correct data. *Closing the form and opening it in Form view shows the correct data in the combo box. In summary, the tables appear to be relinked correctly but the first form I try to open does not have its Record source connected. *Is there something that needs to done in Access 2010 different from in Access 2000? *I have searched this group to no avail, so I suspect that others have not experienced this. *I live in hope. Jim OK, so no-one has this problem. I find that it has nothing to do with my code! *Even if I use the Linked Table Manager I still get the same problem. It is only resolved, whether I relink by code or by the table manager, by Compacting and Repairing the database. This I have to do from the ribbon because one cannot compact a database by code from within itself!! It still seems strange to me that the problem occurs and that I need to repair the database. I've been seeing similar problems, but don't have details right now. From what I've seen, the trouble involves network paths/connections. So, some questions: Are the linked tables on a local folder or a networked share? Does the path for the linked tables contain a networked drive letter? Are you running on Windows7 or XP? (or something else) If you change any of these, does the problem go away? I'll try to gather more details on my version of the problem next time I come across it. The problem isn't everywhere, and goes away upon relinking, *so I've been able to ignore for the time being. Hope this helps, Kevin |
I have only recently converted a client's A2000 application to A2010
(the first one to do so). I sorted out various things on my laptop
but did not think to check about relinking. Only when I placed it on
their system did I need to relink and thus found the problem. Back
home I found that it occurred on mine as well.
On theirs it was a networked system but on mine it is not.
Both are running Windows XP
Another client is converting to A2010 soon and shortly afterwards
upgrading to Windows 7. I shall watch with care.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.