Solved: Error On Tbl.properties When Running International

[JBSTx]

I’ve created a database that is linked to two other Access data databases and I have one of the common relink procedures set up to automatically test the links when the database is first opened and relink if necessary. I have the process in a number of other databases and have never had an issue with it.

My new database is being rolled out internationally to our data centers in London and Hong Kong and it works great through Citrix but, for various reasons, some of our companies will initially need to run it locally. When they (two test countries so far) do this they get an “Unspecified error” Run-time error -2147467259 (80004005).

Basics of the code are:

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
....

cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
....

The error occurs on lines within the For loop where I use tbl.Properties such as:
blnIsTemp = tbl.Properties("Temporary Table") Or Left(tbl.Name, 1) = "~"
blnIsImex = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strImex, vbTextCompare) > 0)
blnIsMapi = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strMapi, vbTextCompare) > 0)
tbl.Properties("Jet OLEDB:Link Datasource") = strPath

Everything else seems to work ok and they can manually relink but that is not a good option. I wrote this in 32 bit Office 2007 but it works fine on Office 2010. Our Citrix environment is 32 bit Office 2010. All the libraries in VBA seem to be set up in their local copy of Access.

Any help is greatly appreciated.

Burt

[HiTechCoach]

Burt,

Relinking is a known issue in Access 2010. Microsoft is aware of it and working on a fix.

Do all the Citrix users have their own copy of the front end? One that is not shared.

If you wrote app in Access 2007 then I would try using the Access 2007 runtime.

[JBSTx]

Thanks Boyd.

Each country will have its own set of files whether they run on Citrix or locally to keep the data secure. This makes the relink routine pretty important since I can't dictate a local network path and, even if it all sat on the data centers, manually relinking 20+ copies on every revision would be a head ache.

I just find it strange that it works for me locally and through Citrix but they get an error when they try to run it locally. Our guys in Korea have tried it with both 2010 and 2007 (I'm double checking that the last test was 2007 but they are pretty sharp so feel pretty sure it was 2007). Hope to have a 3rd country test in the next day or two.

I’m a bit of a self-taught hack so do not fully understand what is happening with tbl.properties but could it be missing on their version or could it or the parameters it uses be something machine dependent?

I’ll look into runtime or maybe an ACCDE.

Thanks again. All ideas are appreciated by my one man shop.

Burt

[HiTechCoach]

It would help is you would post all the VBA code used for relinking. This will allows us to test the code. Is that possible?

[JBSTx]

Here is the basic code to link to a single database:

Public Sub SubRefreshLinks()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim blnIsMapi As Boolean
Dim blnIsImex As Boolean
Dim blnIsTemp As Boolean
Dim strPath As String

Const strImex = "IMEX"
Const strMapi = "MAPILEVEL="
strPath = CurrentProject.Path & "\GPSProData.accdb"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection

'Cycle through all tables.
For Each tbl In cat.Tables
' Check to make sure each table is a linked table.
If tbl.Type = "LINK" Then
blnIsTemp = tbl.Properties("Temporary Table") Or Left(tbl.Name, 1) = "~"
blnIsImex = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strImex, vbTextCompare) > 0)
blnIsMapi = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strMapi, vbTextCompare) > 0)

'Verify that the table is a Jet table.
If Not blnIsTemp And Not blnIsImex And Not blnIsMapi Then
'Set the path apporpriately
tbl.Properties("Jet OLEDB:Link Datasource") = strPath
End If
End If
Next

End Sub


My actual code is a little more tricked out in that I have a few different options to control the path of the files, which file to link to, and I have some On Error logic (see below). The extra code does not affect the error I am getting. In stepping through the function in debug the error occurs on every line with tbl.Properties("xxxx") in it.

A couple of thoughts I had:
- Is the "xxxx" in the local language on an international PC? I put a nonsense string in "xxxx" and tried it locally but got a different error "Item cannot be found in the collection ..."

- I've seen some references to tbl.Properties("xxxx") with a ".value" on the end and wondered if this could be an issue. I can make it tbl.Properties("xxxx").value on my local PC and it still works and it also works on Citrix (actually seems to make the relink process faster on Citrix but that could be due to other factors).

- I'm going to try creating a function to list all of the table properties names and values on the international PC's to see if that reveals anything. Hopefully I can connect with Korea tonight or Europe early in the morning to test this. I have run the program in Korean Access through Citrix and it does work so not sure if this is the issue.



Here is the full code:

Public Sub subDefaultLinks(Optional inFlag As Integer = 0)
On Error GoTo Err_subDefaultLinks_Click
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim blnIsMapi As Boolean
Dim blnIsImex As Boolean
Dim blnIsTemp As Boolean
Dim dbCurrent As Database
Dim RSDetails As Recordset
Dim strSql As String
Dim strPath As String
Dim dateSaveTime As Date
Dim varResult As Variant

Dim strCurrPath As String
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Const strImex = "IMEX"
Const strMapi = "MAPILEVEL="
varResult = SysCmd(acSysCmdSetStatus, "Relinking Tables, please wait...")
DoCmd.Hourglass True

If inFlag = 1 Then
strCurrPath = fncConvertToUNC(Forms!frmUtl_UpdateSetup.varLinkDir)
Else
strCurrPath = fncConvertToUNC(CurrentProject.Path)
End If

'Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
'Cycle through all tables.
For Each tbl In cat.Tables
' Check to make sure each table is a linked table.
If tbl.Type = "LINK" Then
blnIsTemp = tbl.Properties("Temporary Table") Or Left(tbl.Name, 1) = "~"
blnIsImex = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strImex, vbTextCompare) > 0)
blnIsMapi = (InStr(1, tbl.Properties("Jet OLEDB:Link Provider String"), strMapi, vbTextCompare) > 0)

'Verify that the table is a Jet table.
If Not blnIsTemp And Not blnIsImex And Not blnIsMapi Then

strSql = "SELECT * FROM tblUtil_LinkTableInfo where [LnkTblName] = '" & tbl.Name & "';"
Set RSDetails = dbCurrent.OpenRecordset(strSql)

If Left(RSDetails!LnkPath, 9) = "<current>" Then
strPath = strCurrPath & "\" & RSDetails!LnkDbName
Else
strPath = RSDetails!LnkPath & "\" & RSDetails!LnkDbName
End If

'Debug.Print tbl.Name & " -- " & RSDetails!LnkPath & " -- " & strPath

'Set the path apporpriately
tbl.Properties("Jet OLEDB:Link Datasource") = strPath
End If
End If
Next

DoCmd.SetWarnings False

strSql = "UPDATE tblMst_ControlBlockLocal SET tblMst_ControlBlockLocal.CtlBlkVl = '" & strCurrPath & _
"' WHERE (((tblMst_ControlBlockLocal.CtlBlkCd) = 'CBDataFileLoc'));"
DoCmd.RunSQL strSql

strSql = "UPDATE tblMst_ControlBlock SET tblMst_ControlBlock.CtlBlkVl = '" & strCurrPath & _
"' WHERE (((tblMst_ControlBlock.CtlBlkCd) = 'CBDataFileLoc'));"
DoCmd.RunSQL strSql

DoCmd.SetWarnings True

Exit_subDefaultLinks_Click:
Set dbCurrent = Nothing
Set RSDetails = Nothing
varResult = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
Exit Sub

Err_subDefaultLinks_Click:
DoCmd.Hourglass False
MsgBox Err.Description & vbCrLf & "subDefaultLinks - " & Now()
Resume Exit_subDefaultLinks_Click

End Sub

Thanks again for your help and suggestions.

[JBSTx]

The problem is that ADO doesn't work on their PC's. I have the "Microsoft ADO Ext. 2.8 for DDL and Security" library turned on but they just don't like it. Guess I'll try a DAO routine.

Thanks for the help.

[HiTechCoach]

I have always used DAO for relinking tables.

It is my understanding that JET/ACE (the actual database engine) has to translate the ADO back to DAO before executing.

[Tabitha30]

Hello!Welcome to the site! I hope to be friend with you in the future!