troubleshooting Question

Relinking access tables

Avatar of K-9
K-9 asked on
Programming.NET ProgrammingVisual Basic.NET
7 Comments1 Solution683 ViewsLast Modified:
I am in a bit of a bind here.. I have an access database split into a frontend/backend.  I have multiple customers using this database, where each customer has the backend located at a different location.

I often do updates to the frontend, so I created a vb.net application that downloads the updated front end, unzips it and then relinks to the backend database, in which the location of the backend database is in a textfile in their application directory.

The relinking part using DAO and has worked great for the past couple of years.

However, recently we noticed some machines, it's not relinking.  It just 'skips' over the 'for loop', where <for each table in database>

Anyway, what I need is vb.net code that will relink the front-end to a back-end database using ADO.Net  I am hoping this will fix my issue, which is a weird one where all of a sudden some machines won't relink (including my development machine), however others will work fine.

This ia a mission critical application, used by police departments.  I am willing to give as many points as possible.  I have done google searches but to know avail.  Below is my code for the relinking process done in DAO.

PS. I can't even compile my code anymore in VS 2010, If I try to add a reference to dao360.dll it tells me the reference cannot be added.

   Dim dao As New dao.DBEngine
        Dim db As dao.Database
        Dim dt As dao.TableDef
        Dim tblCount, tblCounter As Integer
        Dim CurrentBEPathFile As String = "BEPath.txt"
        Dim rs As dao.Recordset
        Dim tblNumber As Integer
        
        On Error Resume Next

        If System.IO.File.Exists(CurrentBEPathFile) = True Then
            Dim objReader As New System.IO.StreamReader(CurrentBEPathFile)
            CurrentBEPath = objReader.ReadToEnd
            objReader.Close()
        Else
            messageBoard.AppendText("BEPath.txt file does not exist.. Please contact ProNet." & Constants.vbCrLf)
            tmpErrorLog = "BEPath.txt file does not exist."
            StartSPISBecauseOfError()
            Exit Sub
        End If
        
        db = dao.OpenDatabase("spis_fe.mdb", True, False)

        For Each dt In db.TableDefs
            tblCounter = tblCounter + 1
            tblCount = db.TableDefs.Count
            dt = db.TableDefs(dt.Name)
            If dt.Name = "tblVersion" Then
                dt.Connect = CurrentBEPath
                dt.RefreshLink()
                rs = db.OpenRecordset("tblVersion")
            End If
        Next

        tblLinkCount = db.TableDefs.Count
        tblCount = db.TableDefs.Count
        tblCounter = 1
        tblNumber = 1

        For Each dt In db.TableDefs
            tblCounter = tblCounter + 1
            tblCount = db.TableDefs.Count
            tblName = dt.Name
            dt = db.TableDefs(dt.Name)
            If Mid(dt.Name, 1, 4) <> "MSys" And dt.Name <> "tblVersion" And Len(dt.Connect) > 0 Then
                dt.Connect = CurrentBEPath
                If DoesTableExist(dt.Name, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Microsoft.VisualBasic.Mid(CurrentBEPath, 20)) = True Then
                    dt.RefreshLink()
                End If
            End If

            If (tblCounter Mod 10) = 0 Then
                tmpPercentage = Microsoft.VisualBasic.Left((tblCounter / db.TableDefs.Count) * 100, 2)
                TestWorker.ReportProgress(tmpPercentage)
            End If
            'db = Nothing
        Next
        tmpPercentage = 100
        TestWorker.ReportProgress(tmpPercentage)
        rs.Close()
        db.Close()
        dt = Nothing
        db = Nothing

Public Function DoesTableExist(ByVal tblName As String, ByVal cnnStr As String) As Boolean
        ' For Access Connection String,
        ' use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
        ' accessFilePathAndName

        ' Open connection to the database
        Dim dbConn As New OleDbConnection(cnnStr)
        dbConn.Open()

        Dim restrictions(3) As String
        restrictions(2) = tblName
        Dim dbTbl As DataTable = dbConn.GetSchema("Tables", restrictions)

        If dbTbl.Rows.Count = 0 Then
            'Table does not exist
            DoesTableExist = False
        Else
            'Table exists
            DoesTableExist = True
        End If

        dbTbl.Dispose()
        dbConn.Close()
        dbConn.Dispose()
    End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros