Link to home
Start Free TrialLog in
Avatar of dpokerbear
dpokerbearFlag for Australia

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?
Avatar of GerardChiasson
GerardChiasson
Flag of Canada image

I use this code http://www.mvps.org/access/tables/tbl0010.htm

I keep it it's own module that we copy from db to db.



Avatar of dpokerbear

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.
Are you opening the form manually or is it being opened automatically?
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.
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??


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
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
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
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.
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?
Are you able to updaod a sanitised copy of your db here?

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
i'll see. it will take me some time to make the sanitised version.

references seem ok.
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
Avatar of Scott McDaniel (EE MVE )
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).

OK,
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
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.
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.
Give this a go..
Let me know if it works for you...

Cheers
JC
Database5.zip
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.
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?
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
<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
LSM,
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...




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.
<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
<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
ASKER CERTIFIED SOLUTION
Avatar of JVWC
JVWC
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You mignt also find this interesting
http://msdn.microsoft.com/en-us/office/cc907897.aspx

JC
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).Connect) > 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
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.