dpokerbear
asked on
MS Access Database Linking at Runtime
I am using the code from the following link
http://support.microsoft.com/kb/291264
It links to a new path if I change the Back End DB location, BUT, if I try to open a form, which has a reference to a specific table, I get "Invalid Database Object Reference".
I have to then go into the design view of a particular form and re-select the table in the RecordSource Property for it to work.
How can I make the code refresh the form references so that I don't get this error?
http://support.microsoft.com/kb/291264
It links to a new path if I change the Back End DB location, BUT, if I try to open a form, which has a reference to a specific table, I get "Invalid Database Object Reference".
I have to then go into the design view of a particular form and re-select the table in the RecordSource Property for it to work.
How can I make the code refresh the form references so that I don't get this error?
ASKER
hi, i've seen this before but i used a more simpliefied module instead.
still, if you know the answer as to why this error is occurring, i would prefer to find that out then to change my entire module.
thanks.
still, if you know the answer as to why this error is occurring, i would prefer to find that out then to change my entire module.
thanks.
Are you opening the form manually or is it being opened automatically?
ASKER
it's opening automatically, but i have also tried opening it manually and same error comes up.
what seems to be happening is that when table links are re-connected and re-freshed via code, forms loose the connection to the tables.
even though table name is listed in the control source of the form and is "correct", it doesn't recognise the connection until you click on the drop down menu, "re-select" the same table and then save the form.
what seems to be happening is that when table links are re-connected and re-freshed via code, forms loose the connection to the tables.
even though table name is listed in the control source of the form and is "correct", it doesn't recognise the connection until you click on the drop down menu, "re-select" the same table and then save the form.
ASKER
further information...
ok, my code is working 100% on my old Win XP Machine on Access 2007 version.
When I re-used the same code on Win 7 machine and Access 2010, it doesn't work.
There is nothing wrong with the code. I checked it against other examples and code is 100%. I believe there is something else at play here...
Are there any patches that were released recently for Access 2010??
ok, my code is working 100% on my old Win XP Machine on Access 2007 version.
When I re-used the same code on Win 7 machine and Access 2010, it doesn't work.
There is nothing wrong with the code. I checked it against other examples and code is 100%. I believe there is something else at play here...
Are there any patches that were released recently for Access 2010??
Every chance you are moving the BE to a non trusted location on W7.
Try moving it to My Docs or other trusted location, and try there.
Cheers
JC
Try moving it to My Docs or other trusted location, and try there.
Cheers
JC
In A2010 you can add the existing loaction to the trusted locations.
Options|TrustCenter|, click Trust Center Setting button, select Trusted Locations, Add New Location...
Hope this helps.
Cheers
JC
Options|TrustCenter|, click Trust Center Setting button, select Trusted Locations, Add New Location...
Hope this helps.
Cheers
JC
Hmmn...
Just re-read this
>it doesn't recognise the connection until you click on the drop down menu, "re-select" the same table and then save the form.<
This suggest this BE is accessible...
Have you tried invoking the query builder in the Source for the form (design view) and save the query, so that the form is based on the query, not the table...
Just a thought!
JC
Just re-read this
>it doesn't recognise the connection until you click on the drop down menu, "re-select" the same table and then save the form.<
This suggest this BE is accessible...
Have you tried invoking the query builder in the Source for the form (design view) and save the query, so that the form is based on the query, not the table...
Just a thought!
JC
ASKER
JC, interesting thoughts. i tried the trusted location thing and that didn't make any difference.
However, one thing that makes it work is Compact and Repair. So, after i programatically re-link the tables, i can't access the forms until pressing compact and repair. Then it's all ok. Might have to incorporate Compact and Repair in my Back End Linking Module.
I'll try the query idea you suggested, although it wouldn't be very good, even if it worked, because what do i do with my other projects which are all using many forms? I would have to re-structure all my projects so that my forms are linked to queries and not tables. That's not good.
However, one thing that makes it work is Compact and Repair. So, after i programatically re-link the tables, i can't access the forms until pressing compact and repair. Then it's all ok. Might have to incorporate Compact and Repair in my Back End Linking Module.
I'll try the query idea you suggested, although it wouldn't be very good, even if it worked, because what do i do with my other projects which are all using many forms? I would have to re-structure all my projects so that my forms are linked to queries and not tables. That's not good.
ASKER
by the way, just tested queries and they loose connection to tables too. so can't open queries or forms until doing compact and repair.
this doesn't happen in 2007 version, i don't understand why it's happening in 2010?
this doesn't happen in 2007 version, i don't understand why it's happening in 2010?
Are you able to updaod a sanitised copy of your db here?
Have you checked the ADOX references. Perhaps they are different version???
Have you checked the ADOX references. Perhaps they are different version???
Another thought...
Have you tried using the Linked Table Manager to see if that works?
Cheers
JC
Have you tried using the Linked Table Manager to see if that works?
Cheers
JC
ASKER
i'll see. it will take me some time to make the sanitised version.
references seem ok.
references seem ok.
ASKER
ok dude,
i re-created a sanitised version. i copied somebody else's code for this purpose, but it's the same outcome.
please review and let me know.
thanks.
testaback.accdb
testfront.accdb
i re-created a sanitised version. i copied somebody else's code for this purpose, but it's the same outcome.
please review and let me know.
thanks.
testaback.accdb
testfront.accdb
If you can Compact and Repair, and your app then works, then I'd suggest you have some corruption issues with the database.
Have you moved the db to a new, blank database container? This generally solves those sort of issues, but you may wish to first decompile/recompile the database:
Decompile:
------------------
Build a new shortcut with a target like this:
"full path to msaccess.exe" "full path to your database" /decompile
Compile:
---------------
Open the VBA Editor, click Debug - Compile. Fix any errors, continue until the menuitem is disabled.
AFTER doing those things, then move the objects to a new database, rebuild your references, and see what happens. Note also that if you're using 2010, you should do this in 2010 (just use the "older" 2007 format, if that's available).
Have you moved the db to a new, blank database container? This generally solves those sort of issues, but you may wish to first decompile/recompile the database:
Decompile:
------------------
Build a new shortcut with a target like this:
"full path to msaccess.exe" "full path to your database" /decompile
Compile:
---------------
Open the VBA Editor, click Debug - Compile. Fix any errors, continue until the menuitem is disabled.
AFTER doing those things, then move the objects to a new database, rebuild your references, and see what happens. Note also that if you're using 2010, you should do this in 2010 (just use the "older" 2007 format, if that's available).
OK,
I saved the backend as MDB and problem seems to go away...
I'll do some more digging...
Cheers
JC
I saved the backend as MDB and problem seems to go away...
I'll do some more digging...
Cheers
JC
I see this in
Public Function CreateLinks(strBEPath) As String
On Error GoTo Err_CreateLinks
Dim dbsFE As dao.Database ...
yet I do not see a reference to DAO in references...
Sorry, i'm out of time right now, will get back tomorrow.
LSM:
Could this be ADO/DAO conflict?
Cheers
JC
Public Function CreateLinks(strBEPath) As String
On Error GoTo Err_CreateLinks
Dim dbsFE As dao.Database ...
yet I do not see a reference to DAO in references...
Sorry, i'm out of time right now, will get back tomorrow.
LSM:
Could this be ADO/DAO conflict?
Cheers
JC
ASKER
LSM, this is happening on all my Access 2010 databases where I am using this code. This uploaded example was something i created just to demonstrate the point. I doubt all my databases are corrupt...
JC, i added DAO.Database because for some reason it wouldn't compile with just Dim dbsFE As Database.
JC, i added DAO.Database because for some reason it wouldn't compile with just Dim dbsFE As Database.
ASKER
The reason why Compact and Repair makes it work is that it somehow refreshes the links not because DB is actually corrupt.
That's what I think anyway.
That's what I think anyway.
ASKER
Very interesting. It works only the first time i assign the back end to it.
but after that, if i change back end and try to re-assign it again, it behaves the same way as before. error: object not found...
What changes did you make? I noticed your comment "this was going through to error handler" but i couldn't re-create a scenario where it does that.
but after that, if i change back end and try to re-assign it again, it behaves the same way as before. error: object not found...
What changes did you make? I noticed your comment "this was going through to error handler" but i couldn't re-create a scenario where it does that.
ASKER
just out of curiosity. have any of you written similar code to do the same thing
"check if back end exists and if not - then allow user to re-connect to it"
and if yes, did you by any chance have similar problems using Acces ver 2010???
i am asking because i would like to know if this problem is exclusive to me or is it perhaps an Access issue?
"check if back end exists and if not - then allow user to re-connect to it"
and if yes, did you by any chance have similar problems using Acces ver 2010???
i am asking because i would like to know if this problem is exclusive to me or is it perhaps an Access issue?
I don't think i t could be a conflict between those two libraries, although it's always possible. I haven't had sufficient time lately to work through problems like this, so will leave it to you to manage.
Scott
Scott
<What changes did you make? I noticed your comment "this was going through to error handler" but i couldn't re-create a scenario where it does that.>
Exithere: '<<<<<Added This to Function Checklinks() before the error handler
MsgBox "this was going through to errhandler"
Exit Function
Also, I created a new DB in A2010 and imported all the objects.
That created more issues...
I then checked the references and noticed that a few were not selected.
I went through a trial and error process of selecting and adding references (including DAO) until I found a that combination that compiled and would run.
I then went back to your DB and tried the same combination of references by first removing and then replacing references to get back to the combination above. Afraid to say it did NOT work!
That's why I was curious to know if mine worked (or performed differently) on your machine.
JC
Exithere: '<<<<<Added This to Function Checklinks() before the error handler
MsgBox "this was going through to errhandler"
Exit Function
Also, I created a new DB in A2010 and imported all the objects.
That created more issues...
I then checked the references and noticed that a few were not selected.
I went through a trial and error process of selecting and adding references (including DAO) until I found a that combination that compiled and would run.
I then went back to your DB and tried the same combination of references by first removing and then replacing references to get back to the combination above. Afraid to say it did NOT work!
That's why I was curious to know if mine worked (or performed differently) on your machine.
JC
LSM,
I hear you...
:-)
JC
I hear you...
:-)
JC
<just out of curiosity. have any of you written similar code to do the same thing
"check if back end exists and if not - then allow user to re-connect to it">
Yes, I have an interesting one that connects to TWO backend MDBs, however it is specific to that FE and would be of little use here.
No, have not yet tried it on A2010 :-(
I'm in the process of rebuilding now...
For that reason I am keen to get to the bottom of your issue, it will assist me too...
"check if back end exists and if not - then allow user to re-connect to it">
Yes, I have an interesting one that connects to TWO backend MDBs, however it is specific to that FE and would be of little use here.
No, have not yet tried it on A2010 :-(
I'm in the process of rebuilding now...
For that reason I am keen to get to the bottom of your issue, it will assist me too...
ASKER
thanks JC, you have been very helpful. it's good to know that there are others out there who are concerned with Access issues :)
I've just tried somebody else's program, which had a similar module to mine (totally unrelated code) and exactly the same thing happened.
if you get a chance please trial your own module in 2010 and see if it does the same thing. if it does, then we know that it's a problem with Access and not our code.
I've just tried somebody else's program, which had a similar module to mine (totally unrelated code) and exactly the same thing happened.
if you get a chance please trial your own module in 2010 and see if it does the same thing. if it does, then we know that it's a problem with Access and not our code.
<but after that, if i change back end and try to re-assign it again, it behaves the same way as before. error: object not found...>
One other change I made.
Note the reference to Microsoft ADO Ext 2.8 etc in your DB
Your link in the Question says you are using this code.
http://support.microsoft.com/kb/291264
That link suggests you update Microsoft ADO 2.5 (or later)...
My machine has Microsoft ADO EXT 6 etc so I selected that reference in lnstead of 2.8
Not sure if that is the latest OR if it installs with W7 or Office. I have W7 64bit, Access 2007 AND Office 2010 (with A2010)
I also noted that I could NOT select DAO reference.
Out of time once again, but I'll keep digging.
Cheers
JC
One other change I made.
Note the reference to Microsoft ADO Ext 2.8 etc in your DB
Your link in the Question says you are using this code.
http://support.microsoft.com/kb/291264
That link suggests you update Microsoft ADO 2.5 (or later)...
My machine has Microsoft ADO EXT 6 etc so I selected that reference in lnstead of 2.8
Not sure if that is the latest OR if it installs with W7 or Office. I have W7 64bit, Access 2007 AND Office 2010 (with A2010)
I also noted that I could NOT select DAO reference.
Out of time once again, but I'll keep digging.
Cheers
JC
<if you get a chance please trial your own module in 2010 and see if it does the same thing. if it does, then we know that it's a problem with Access and not our code.>
OK, will do
But not tonight...
Cheers
JC
OK, will do
But not tonight...
Cheers
JC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi JC,
thanks for having a go. i had a look at that example you used, but i didn't test it.
however, i am still puzzled as why my example works perfectly in 2007 and not in 2010.
here is the code which I think may be causing the problem. if anyone else would like to comment or have a look at the example i provided few comments ago, please do so and let me know if you have any idea why this doesn't work.
Public Function CreateLinks(strBEPath) As String
On Error GoTo Err_CreateLinks
Dim dbsFE As DAO.Database
Dim strTableName As String
Dim strConnect As String
Dim tdfBE As DAO.TableDef
Dim tdfFE As DAO.TableDef
'If empty db path is passed that means that user pressed cancel button on the
'Open File dialog box or closed it without selecting the path
If strBEPath = "" Then
CreateLinks = "Cancel"
Exit Function
End If
CreateLinks = "False"
Set dbsFE = CurrentDb
For Each tdfFE In dbsFE.TableDefs
If Left$(tdfFE.Name, 4) <> "Msys" And _
Len(dbsFE.TableDefs(tdfFE. Name).Conn ect) > 0 Then
strConnect = ";DATABASE=" & strBEPath
tdfFE.Connect = strConnect
tdfFE.RefreshLink
End If
Next tdfFE
CreateLinks = "True"
Exit_CreateLinks:
On Error Resume Next
Set tdfFE = Nothing
Set dbsFE = Nothing
Exit Function
Err_CreateLinks:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_CreateLinks
End Function
thanks for having a go. i had a look at that example you used, but i didn't test it.
however, i am still puzzled as why my example works perfectly in 2007 and not in 2010.
here is the code which I think may be causing the problem. if anyone else would like to comment or have a look at the example i provided few comments ago, please do so and let me know if you have any idea why this doesn't work.
Public Function CreateLinks(strBEPath) As String
On Error GoTo Err_CreateLinks
Dim dbsFE As DAO.Database
Dim strTableName As String
Dim strConnect As String
Dim tdfBE As DAO.TableDef
Dim tdfFE As DAO.TableDef
'If empty db path is passed that means that user pressed cancel button on the
'Open File dialog box or closed it without selecting the path
If strBEPath = "" Then
CreateLinks = "Cancel"
Exit Function
End If
CreateLinks = "False"
Set dbsFE = CurrentDb
For Each tdfFE In dbsFE.TableDefs
If Left$(tdfFE.Name, 4) <> "Msys" And _
Len(dbsFE.TableDefs(tdfFE.
strConnect = ";DATABASE=" & strBEPath
tdfFE.Connect = strConnect
tdfFE.RefreshLink
End If
Next tdfFE
CreateLinks = "True"
Exit_CreateLinks:
On Error Resume Next
Set tdfFE = Nothing
Set dbsFE = Nothing
Exit Function
Err_CreateLinks:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_CreateLinks
End Function
ASKER
problem still remains, but thanks for your help. i will have to find a new method to do this. might try using some of the code suggested.
thanks again for your effort.
thanks again for your effort.
I keep it it's own module that we copy from db to db.