I have a database that was in Access 2007 format (front-end and back-end)

I create a new database in Access 2010 for a back-end db and imported all tables from the access 2007 back-end

I created a new database in Access 2010 for a front-end db and imported all tables, queries, forms, reports, macros, and modules from the access 2007 front-end.

I compiled code everywhere.

I manually relinked the back-end tables in the front-end database.

When I run the Access 2010 front-end and the access2010 back-end is in the same directory it works fine (all forms function correctly etc....)

If I:
- close the databasd
- rename the back-end to a new name
- open the front-end (it runs my linking table code and prompts for db, which I select and tables are relinked however now I get "invalid database object reference" errors and "object invalid or no longer set" errors)
- not I can use the table linking manager and see the new path on every table and click on every table and it opens (for the linked back-end tables)

If I then:
- close the database
- rename the back-end back to the original name
- open the front-end (it runs my linking table code and prompts for db, which I select and now everything works fine again)

Please help!!!!!!!!
I am very confused,
Mark

This doesn't have anything to do with either 2007 or 2010, it sounds more like you are losing an object reference somewhere in your code by setting it to nothing or somehow changing the variable type. If you are using multiple subs or functions, make sure that you are not accidentally resetting a variable and passing a Null or Nothing back, if it is all one then you might want to check all of your If statements to see if you have a reset and then return to code from a conditional or error.

Hope that helps!

See my next post where I did everything manually in Access 2010, no code and I get same issue.

Takes 5 minutes to build my test databases.

Please help,
Mark

Can you post your relinking code?

It breaks on the manual database just using the table relinker built into the product.

I walk through how I can break Access 2010 with a one table and one form example.

I uploaded my very small and simple example if that helps.

Mark

I don't have 2010, only 03. However the relinking hasn't changed (pretty sure!). Can you back convert it real quick?

The database works fine in earlier versions. It's a 2010 issue or something about my computer and 2001 and 2007 both being installed.

I built the simple database with 1 table to see if I could duplicate the issue and take out some variables (such as relinking code not working in 2010)..

@mandrews I downloaded your file and tried it out. I got the same error you did. I also recreated this scenario from scratch using 2010 and experienced no errors (based on directions from your other post).

Don't know if this means anything, but if you go into design view first, then enter the form, you do not get the object reference error and it appears to work fine. So, as previously mentioned it seems like this is caused by your code.

I was able to reproduce this as well, and like kofing could open it if I opened via design form first.

After some further tests, it seems to be fairly specific:

This will not occur if we create the form using:

Blank Form / Design Form
Multiple Form
Split Form

Only "Form" (e.g. single non-continuous form) will behave badly.

Thanks to everyone for trying it out. I'm still confused on how to fix the issue.

On my big application that was originally access 2007 I have a bunch of split forms that launch from a custom ribbon and they were not working
(except if the database was the exact name/location before ever relinking, then they work fine). I tried not using code to relink but just using linked table manager and aslo trying delting the linked tables and relinking them all vai get external data/access etc... All ways have forms that do not work.
perhaps I need to refresh tabledefs or forms or something to get around this bug?

I did the small test (that had no code to try and rule out my relinking code, which I haved used for many years).
On that database there is no code and it was created 100% in Access 2010. I'll have to try some more tests as well, I'm not sure what the pattern is that causes it to work or not work?

Blank Form / Design Form
Multiple Form
Split Form
or is it just some random type behavior.

In general every database I created will have to have the backend change location because when I give it to the client they choose where the files are located.

I do have Access 2007 and Access 2010 on this machine, I'm not sure if that is an issue?

If anyone has a clue how to fix it would be appreciated.
Thanks in advance,
Mark

BTW, I forgot to mention:

If you compact & repair, it seems to go away.

Just to be clear - are you saying none of other autocreate buttons such as split form or multiple forms work correctly? They did - only "Form" did not work correctly on my machine.

It seems to be kinda random to me. I created a few forms using different methods "form", "ds" "ust design form" and did them while linked to different named backends. In general they seem to work when the back-end is named and in the same location as when the forms were created, but not always.

and yes compact and repair did seem to fix the problems even in my larger application with relinking code and about 50 forms and 20 tables.
The larger app has mostly split forms where I saw the error message.

Of course now I am at:
- here ya go client here is your db, but make sure you always hit compact and repair after you change the location of the back-end (which is the first time they use it because I have the files in a different location).

If you can figure out anything more about ways to fix the issue (other than doing a compact and repair) let me know.

I also see the behavior that if you open the form in design view and then jump to form view it gets around the problem.

So still thinking it is a bug in Access 2010.

Thanks in advance,
Mark

FYI: Dirk Goldgar (MVP) tells me this bug is listed in the MVP forum for Access 2010 bugs.

It's a big one for me. So much I would encourage all my customers not to switch to Access 2010.

Would love to see that bug list? not sure how to get access to the list (without becoming an MVP),
Mark

This issue broke my favorite database (i.e. the one from which I bill my clients!) and even after reading the above messages, I don't get it... and there's nothing in the KB about it except some erroneous (or at least not applicable) articles about using the wrong language version or some such nonsense. (IMG:style_emoticons/default/smile.gif) Unless the VBA system changed dramatically, I'm not doing anything exotic. There's one button on the form with the following code:

===========================================
Option Compare Database

Private Sub cmdInvoice_Click()

On Error GoTo Err_cmdInvoice_Click

Dim stDocName As String
stDocName = "rptInvoice"
RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrySetInvoiceDate"
DoCmd.OpenQuery "qrySetInvoiceNumber"
DoCmd.OpenReport stDocName, acPreview
DoCmd.SetWarnings True

Exit_cmdInvoice_Click:
Exit Sub

Err_cmdInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdInvoice_Click

End Sub
===========================================

Is there something in there that's syntactically incorrect? I stepped through the code and it errored on the bold line above...

What's the error messge?

"Object invalid or no longer set."

Whenever you refresh table links you'll get the above until a compact & repair command is applied to the database.

If anyone found a solution plz reply.

The only way that I can find around this problem is this:

In the Options: Current Database: Set Compact on Close (Compact somehow fixes the link and prevents the error)
In an autoexec macro check whether current links are valid.
If not then run code to relink tables and exit database.
Then rerun database (should then find links and run normally)

An alternative to the autoexec macro is to open a starter form. A load or open event triggers code such as the following:
'------------------------------------------------------------
' AutoLinker
'
'------------------------------------------------------------
Public Function AutoLinker()
On Error GoTo AutoLinker_Err

If (CheckLinks() = False) Then
fRefreshLinks
' From Code Courtesy of Dev Ashish
DoCmd.RunCommand acCmdCloseDatabase
End If
DoCmd.OpenForm "Name of main form to Open", acNormal, "", "", , acNormal


AutoLinker_Exit:
Exit Function

AutoLinker_Err:
MsgBox Error$
Resume AutoLinker_Exit

End Function
'********************************************************
Public Function CheckLinks() As Boolean
' Check links to the database; returns True if links are OK.

Dim DBS As DAO.Database, rst As DAO.Recordset

Set DBS = CurrentDb
' Open linked table to see if connection information is correct.
On Error Resume Next
Set rst = DBS.OpenRecordset("One of the tables that contains a record")
' If there's no error, return True.
If err = 0 Then
CheckLinks = True
Else
CheckLinks = False
End If

End Function

This post has been edited by greimers: Mar 10 2011, 04:19 PM

This might work but I have not tried it.

- start your front-end with NO linked tables
- at startup relink all tables via code

I usally start with linked tables and then just change the link table locations if needed which always gets that error and user has to compact/repair

or
wait until SP1 which hopefully fixes the issue (I heard this bug is on the list from one of the MVPs).

My two cents,
Mark

I found a solution to this problem. Just paste the following code after refreshing all the links of your tables: