Solved

MS Access Database Linking at Runtime

Posted on 2010-08-30
33
623 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:dpokerbear
  • 15
  • 14
  • 2
  • +1
33 Comments
 
LVL 3

Expert Comment

by:GerardChiasson
ID: 33564329
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.



0
 

Author Comment

by:dpokerbear
ID: 33564516
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.
0
 
LVL 3

Expert Comment

by:GerardChiasson
ID: 33564535
Are you opening the form manually or is it being opened automatically?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dpokerbear
ID: 33564586
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.
0
 

Author Comment

by:dpokerbear
ID: 33564755
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??


0
 
LVL 6

Expert Comment

by:JVWC
ID: 33564890
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
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33564915
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
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33564948
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
0
 

Author Comment

by:dpokerbear
ID: 33565059
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.
0
 

Author Comment

by:dpokerbear
ID: 33565077
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?
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33565394
Are you able to updaod a sanitised copy of your db here?

Have you checked the ADOX references. Perhaps they are different version???
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33565475
Another thought...
Have you tried using the Linked Table Manager to see if that works?

Cheers
JC
0
 

Author Comment

by:dpokerbear
ID: 33565518
i'll see. it will take me some time to make the sanitised version.

references seem ok.
0
 

Author Comment

by:dpokerbear
ID: 33565742
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
0
 
LVL 84
ID: 33566171
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).

0
 
LVL 6

Expert Comment

by:JVWC
ID: 33566239
OK,
I saved the backend as MDB and problem seems to go away...
I'll do some more digging...

Cheers
JC
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33566855
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
0
 

Author Comment

by:dpokerbear
ID: 33573601
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.
0
 

Author Comment

by:dpokerbear
ID: 33573608
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.
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33575096
Give this a go..
Let me know if it works for you...

Cheers
JC
Database5.zip
0
 

Author Comment

by:dpokerbear
ID: 33575535
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.
0
 

Author Comment

by:dpokerbear
ID: 33575579
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?
0
 
LVL 84
ID: 33575584
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
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33575858
<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
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33575891
LSM,
I hear you...
:-)
JC
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33576034
<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...




0
 

Author Comment

by:dpokerbear
ID: 33576140
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.
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33576151
<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
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33576175
<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
0
 
LVL 6

Accepted Solution

by:
JVWC earned 500 total points
ID: 33602608
dpokerbear
Well I managed to convert mine and it works OK. As I said above mine is very specific for my App but it is a modification of this one.
http://support.microsoft.com/kb/181076

It is quite old (written for A97) so my next mod would be to convert for API FileDialogue...
Surprised I could still find this!

Hope this helps.
JC
0
 
LVL 6

Expert Comment

by:JVWC
ID: 33602666
You mignt also find this interesting
http://msdn.microsoft.com/en-us/office/cc907897.aspx

JC
0
 

Author Comment

by:dpokerbear
ID: 33605969
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
0
 

Author Closing Comment

by:dpokerbear
ID: 33775670
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question