Solved

MS Access Database Linking at Runtime

Posted on 2010-08-30
33
619 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
Comment Utility
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
Comment Utility
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
Comment Utility
Are you opening the form manually or is it being opened automatically?
0
 

Author Comment

by:dpokerbear
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Another thought...
Have you tried using the Linked Table Manager to see if that works?

Cheers
JC
0
 

Author Comment

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

references seem ok.
0
 

Author Comment

by:dpokerbear
Comment Utility
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
Comment Utility
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
Comment Utility
OK,
I saved the backend as MDB and problem seems to go away...
I'll do some more digging...

Cheers
JC
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Expert Comment

by:JVWC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Give this a go..
Let me know if it works for you...

Cheers
JC
Database5.zip
0
 

Author Comment

by:dpokerbear
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
<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
Comment Utility
LSM,
I hear you...
:-)
JC
0
 
LVL 6

Expert Comment

by:JVWC
Comment Utility
<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
Comment Utility
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
Comment Utility
<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
Comment Utility
<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
Comment Utility
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
Comment Utility
You mignt also find this interesting
http://msdn.microsoft.com/en-us/office/cc907897.aspx

JC
0
 

Author Comment

by:dpokerbear
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now