ADOX Catalog

[gr]

Hi, in a database I am using a Catalog. First for
verifing/refreshing & relink tables and also for other
procedures.
I'm new in Access in books and in Knoledge base examples
sometimes they do Set cat = Nothing but other's not..

In my database sometimes and randomly I get this message
"The instruction at 0x650a40ed reference memory at
0x00666699. The memory could not be read"

So, someone tell me that I might not be closing all the
recordsets. I have all the recordsets closed and set to
nothing, but in the catalog that refreshs links I follow a
book example and there it is not set to nothing.

Must all catalogs be set to nothing? Is this the reason of
the previous error message?

Thank you all.

 

[Allen Browne]

Hi gr.

All object variables should be set to Nothing before you exit a procedure:
Catalog, Recordset (even after closing), Form, TextBox, Variant that was
pointed to an object, etc. Most times you can get away without doing it, but
the you run into some cryptic bug where the objects were not destroyed
successfully, and setting all objects to Nothing can help avoid that.

You do not mention your version of Access, but it would be a good idea to
visit support.microsoft.com and check out the latest service for your
version of Access and for JET 4. That can help these odd errors as well.

It may also be worthwhile running chkdsk over your drive to ensure there are
no logical errors. Faulty hardware could also give the error.

In general, we have found the ADOX library to be buggy and incomplete to the
point where is it not usable. Unless you have a reason for doing it that
way, the native DAO library would give you better results (assuming these
are Access tables). Refreshing links is done through the Connect property of
the TableDef.

 

[gr]

Thank you for your reply.
I'm using Access 2000, version 9.0.0.6620 and Jet version
4.0.7328.0
I will search the Knowledge Base to check out the Conect
Property you mention.
I will appreciate if you can have a look on the code I am
using for refreshing (I already add the Set xxx =
Nothing).
Thanks in advance.

Private Sub Form_Load()
'Link tables
On Error GoTo LinkTables_Err:
Dim strPath As String
Dim strDefaultPath As String
strDefaultPath = "I:\01\Drives\Data Bases"
DoCmd.Hourglass True
If Not VerifyLink Then
If Not ReLink(CurrentProject.FullName, True) Then
strPath = WinAPI.FileOpen(strDefaultPath)
If Not ReLink(strPath, False) Then
MsgBox "You cannot run this
Application without locating Data Tables"
DoCmd.Close acForm, "frmStartUp"
DoCmd.Quit
End If
End If
End If
DoCmd.Hourglass False
Salir_Sub:
Exit Sub
LinkTables_Err:
DoCmd.Hourglass False
MsgBox "Error " & Err.Number & ": " &
Err.Description
Resume Salir_Sub
End Sub


Function VerifyLink() As Boolean
On Error GoTo Err_Verify
'Verify connection information in linked tables
Dim cat As ADOX.Catalog
Dim tdf As ADOX.Table
Dim strTemp As String
Set cat = New ADOX.Catalog
With cat
Set .ActiveConnection = CurrentProject.Connection
'Continue if links are broken
On Error Resume Next
'open table to see if connection info is right
For Each tdf In .Tables
If tdf.Type = "LINK" Then
strTemp = tdf.Columns(0).Name
If Err.Number Then
Exit For
End If
End If
Next tdf
End With
VerifyLink = (Err.Number = 0)
Salir_Fun:
On Error Resume Next
Set cat = Nothing
Set tdf = Nothing
Exit Function
Err_Verify:
MsgBox "Error " & Err.Number & ": " &
Err.Description
Resume Salir_Fun
End Function


Function ReLink(strDir As String, DefaultData As Boolean)
As Boolean
On Error GoTo ReLink_Err
'Relink a broken linked table
Dim cat As ADOX.Catalog
Dim tdfReLink As ADOX.Table
Dim oDBInfo As DBInfo
Dim strPath As String
Dim strName As String
Dim intCounter As Integer
Dim vntStatus As Variant

vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

Set cat = New ADOX.Catalog
Set oDBInfo = New DBInfo

With cat
.ActiveConnection = CurrentProject.Connection
oDBInfo.FullName = strDir
strPath = oDBInfo.FilePathOnly
strName = Left(oDBInfo.FileName, InStr
(oDBInfo.FileName, ".") - 1)

On Error Resume Next
Call SysCmd(acSysCmdInitMeter, "Linking
DataTables", .Tables.Count)

For Each tdfReLink In .Tables
intCounter = intCounter + 1
Call SysCmd(acSysCmdUpdateMeter,
intCounter)
If .Tables(tdfReLink.Name).Type
= "LINK" Then
tdfReLink.Properties("Jet
OLEDB:Link Datasource") _
= strPath & strName & IIf
(DefaultData, "Data.Mdb", ".mdb")
End If
If Err.Number Then
Exit For
End If
Next tdfReLink
End With

Call SysCmd(acSysCmdRemoveMeter)
vntStatus = SysCmd(acSysCmdClearStatus)

ReLink = (Err = 0)

Salir_Sub:
On Error Resume Next
Set cat = Nothing
Set tdfReLink = Nothing
Set oDBInfo = Nothing
Exit Function
ReLink_Err:
MsgBox "Error " & Err.Number & ": " &
Err.Description
Resume Salir_Sub
End Function

 

[Allen Browne]

As stated previously, even if you ADOX code is perfect, I would not trust it
to run reliably on everyone's machines.

The basic appropach with DAO is this:

Dim db As DAO.Database
Dim tdx As TableDefs 'All tables
Dim tdf As TableDef 'Each table in this front end.
Dim sConnect As String 'Connect string, based on sDataFile.
Dim sName As String 'Name of table.

Set db = CurrentDb()
Set tdx = db.TableDefs
sConnect = ";DATABASE=C:\MyPath\MyFile.mdb"

'Skip: local tables, MSys* tables, deleted tables.
For Each tdf In tdx
With tdf
sName = .Name
If .Connect <> vbNullString Then
If Not (Left$(sName, 4) = "MSys" Or Left$(sName, 1) = "~")
Then
.Connect = sConnect
.RefreshLink
End If
End If
End With
Next

Set tdf = Nothing
Set tdx = Nothing
Set db = Nothing

 

[gr]

Great! Thank you=)

-----Original Message-----
As stated previously, even if you ADOX code is perfect, I would not trust it
to run reliably on everyone's machines.

The basic appropach with DAO is this:

Dim db As DAO.Database
Dim tdx As TableDefs 'All tables
Dim tdf As TableDef 'Each table in this front end.
Dim sConnect As String 'Connect string, based on sDataFile.
Dim sName As String 'Name of table.

Set db = CurrentDb()
Set tdx = db.TableDefs
sConnect = ";DATABASE=C:\MyPath\MyFile.mdb"

'Skip: local tables, MSys* tables, deleted tables.
For Each tdf In tdx
With tdf
sName = .Name
If .Connect <> vbNullString Then
If Not (Left$(sName, 4) = "MSys" Or Left$(sName, 1) = "~")
Then
.Connect = sConnect
.RefreshLink
End If
End If
End With
Next

Set tdf = Nothing
Set tdx = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.