Variable not Holding Value / Causing Error (1 Viewer)

JaedenRuiner · Jan 2, 2012

Okay,

So my system is one that has a MDB database file that is linked into the MDE application at run time. part of the system is to allow me to update the "app" side of things while leaving the data separated. However, at boot up I want to verify that all of the table links are refreshed and thus will not cause any other breaks in the code. This is also due to some tables changing between versions and other tables being added, so I have to be able to update the database on the fly to the most recent version, as well as maintain the table links, not to mention the location of the mdb could be different based upon where it was installed...
All this being said, I have two methods: RefreshLinks() and GetTableDef(). The latter of course, takes a Database and String parameters and attempts to get the TableDef from the Database. If that table def name doesn't exist, it traps the error and returns nothing. the RefreshLinks does two things. First it attaches to the external database and gets all the valid table names. It then iterates through those names in a collection and verifies that if the tabledef already exists in the CurrentDb, to refresh the connect string, and if not, to create the tabledef.
However, after I make the call to GetTableDef() my internal variable (tdf) retains the value returned by GetTableDef. the Next line is a if statement condition testing for Nothing. once I step past that line, the tdf throws the arror "Object invalid or no longer set". why is this? what is causing the tdf to lose it's value after a simple condition test?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

Code:

Public Sub RefreshLinks()
On Error GoTo RefreshLinks_Error
   Dim tdf As TableDef
   Dim ConnectStr As String
   Dim tables As Collection
   Dim item As Variant
   Dim table As String
   
   If Me.IsValid Then
      Set tables = New Collection
      For Each tdf In Me.DB.TableDefs
         If Left$(tdf.Name, 4) <> "MSys" And Left$(tdf.Name, 1) <> "~" Then
            tables.Add tdf.Name
         End If
      Next
      
      ConnectStr = ";DATABASE=" & db_Prefs("Path") & db_Prefs("DB")
      For Each item In tables
         table = CStr(item)
         Set tdf = GetTableDef(CurrentDb, table)
         If tdf Is Nothing Then 'value good here
            Set tdf = CurrentDb.CreateTableDef(table, dbAttachedTable, table, ConnectStr)
         [b]Else: tdf.Connect = ConnectStr 'value invalid or not set here[/b]
         End If
         tdf.RefreshLink
      Next
   End If
On Error GoTo 0
RefreshLinks_Exit:
   Set tdf = Nothing
   Exit Sub
RefreshLinks_Error:
   MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure RefreshLinks of Module MainModule"
   Resume RefreshLinks_Exit
End Sub

Public Function GetTableDef(DB As Database, Name As String) As TableDef
On Error GoTo GetTableDef_Error
   Dim tdf As TableDef
   Set tdf = DB.TableDefs(Name)
GetTableDef_Exit:
   Set GetTableDef = tdf
   Exit Function
GetTableDef_Error:
   Set tdf = Nothing
   Resume GetTableDef_Exit
End Function

RainLover · Jan 2, 2012

Code:

RefreshLinks_Exit:
   Set tdf = Nothing
Exit Sub

You have Set tdf to Nothing in the First Function.

Even if you did not set it to Nothing it would still close with the "Exit Sub" statement.

spikepl · Jan 2, 2012

I dont see anything obvious. I assume that you did check right after the call to GetTableDEf what you got ?

I notice that you use variable names like Name and Table - those are reserved words and may cause trouble at some stage.

Some straws to grasp: Did you try to compact & repair? Decompile?

RailwayKen · Jan 2, 2012

Another straw to grasp, are you sure ConnectStr is correct? If Access is telling you 'value invalid or not set' it could be referring to either tdf.Connect or to ConnectStr.

Code:

      [COLOR="Red"]ConnectStr = ";DATABASE=" & db_Prefs("Path") & db_Prefs("DB")[/COLOR]
      For Each item In tables
         table = CStr(item)
         Set tdf = GetTableDef(CurrentDb, table)
         If tdf Is Nothing Then 'value good here
            Set tdf = CurrentDb.CreateTableDef(table, dbAttachedTable, table, ConnectStr)
         Else: tdf.Connect = ConnectStr 'value invalid or not set here

I'm not sure how 'db_Prefs("Path")' and 'db_Prefs("DB")' work, but perhaps a character is needed between them??

JaedenRuiner · Jan 3, 2012

Ah.

I will check on the usage of reserved words as variable names. but to answer questions:

dp_Prefs is a Collection that retains some preferences loaded at the start. it is a global variable contained in my MainModule and is used to contain certain system information, including the Path and name of the .mdb used by the application, and it is pre-checked for validity long before RefreshLinks() is called.

I placed a breakpoint at the assignment of the ConnectStr variable. After executing the line "Set tdf = GetTableDef(CurrentDb, table)", I inspect tdf in the Locals Window and all property values are valid. I step over the "If tdf is Nothing Then" condition, and the next execution line is the "Else: tdf.Connect = ConnectStr" statement, at which point the tdf variable in my Locals Window still appears to have a value, but all properties contained within read: "<Object invalid or no longer set>" where the value should be. I step over the Else statement and the error is thrown.

Finally, the "Set tdf = Nothing" occurs at the end of RefreshLinks() when I am leaving that procedure. The error is occurring before that point.

A) I will rename variables to double check that isn't the fault.
B) I will try a compact/repair on the application mdb.

As for decompile, I am working with two MDB files, one of which contains only application functionality. before release I compile it to an MDE so i'm not sure what is meant by trying a decompile.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

JaedenRuiner · Jan 3, 2012

Update:

Updated all references in my code to parameter, variable, or property names: Name, Table, Value.

Compacted & Repaired both application and database MDB files.

tried a few other code variations, like not using "tdf" for my first for each loop and the local variable in the second loop. STill after Execution of GetTableDef() the "tdf" variable has all properties in the Locals & Watch windows as "Object invalid or no longer set".

Stepping into the GetTableDef() function the value is valid and usable.

Things to Note:
A) using the "OpenDatabase()" command to open the data MDB, I have passed in a Database variable to the GetTableDef() and never have this problem. This only comes up when I am using the CurrentDb reserved property.

B) RefreshLinks is a method on a Class Module that calls out to GetTableDef() which is in a standard module. The Class Module is my DBUpdaterClass which handles updates of the back end data mdb between iterations of the Applicaiton mdb.

Don't understand why any of these factors could or should be a problem, but this is really slowing me down. I MUST be able to achieve this simple functionality of checking to see if a "linked" table is present and if so, update the COnnect string for the link, if not, create a new tabledef link reference to the table.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

Galaxiom · Jan 3, 2012

Code:

Set tdf = GetTableDef(CurrentDb, table)

I think this is your problem. CurrentDb is not an object but a Method of Application.

Set a variable to CurrentDb and use that variable in place of CurrentDb when you call the function.

RainLover · Jan 3, 2012

Which value is invalid or not set?

tdf.Connect = ConnectStr

JaedenRuiner · Jan 15, 2012

Galaxiom said:
Code:

Set tdf = GetTableDef(CurrentDb, table)

I think this is your problem. CurrentDb is not an object but a Method of Application.

Set a variable to CurrentDb and use that variable in place of CurrentDb when you call the function.

Yea, I discovered that when looking at other code i had written similarly that apparently the CurrentDb moniker is not treated the same. once I set a local variable to the currentdb all worked fine.
THanks
J"SD"a'RR

Galaxiom · Jan 19, 2012

As a Method of the Application Object, CurrentDb is essentially a function that returns a pointer to the current database.

http://msdn.microsoft.com/en-us/library/bb237861(v=office.12).aspx

You would not be the first developer to have stumbled on this one because the reference sounds like it is an object itself.

In fact it is really Application.CurrentDb but the Application is the default object.

Variable not Holding Value / Causing Error (1 Viewer)

JaedenRuiner

Registered User.

RainLover

VIP From a land downunder

spikepl

Eledittingent Beliped

RailwayKen

New member

JaedenRuiner

Registered User.

JaedenRuiner

Registered User.

Galaxiom

Super Moderator

RainLover

VIP From a land downunder

JaedenRuiner

Registered User.

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread