none
Attach a table to an Access database RRS feed

  • Question

  • I currently use the following code to attach or reattach tables.  The code relies on an knowing if the table is already attached (using the error number).  Since the attached table included the drive letter, it also checks if the path is valid (in case someone is using a different mapped drive letter).  Is there a way of doing this without including Microsoft.VisualBasic to get the error number?

    CODE:

    Public Function ADOAttachTable(ByRef TableName As String, _
                              ByVal AttachToMDB As String, _
                              ByVal AttachFromMDB As String, _
                              Optional ByVal AsTableName As String = "", _
                              Optional ByVal AttachToPWD As String = "", _
                              Optional ByVal AttachFromPWD As String = "") As Boolean

          Dim DBCon As New System.Data.OleDb.OleDbConnection
          Dim MySet As New clsRecordSet
          Dim r As Integer
          Dim Result As Boolean = False

          On Error Resume Next
          '// Get database passwords
          If String.IsNullOrEmpty(AttachFromPWD) Then AttachFromPWD = DB_PWD
          If String.IsNullOrEmpty(AttachToPWD) Then AttachToPWD = DB_PWD
          '// Get table name
          If String.IsNullOrEmpty(AsTableName) Then AsTableName = TableName

          '// try and attach table
          r = AttachTable(TableName, AttachToMDB, AttachFromMDB, AsTableName, AttachToPWD, AttachFromPWD)

          If r = -2147217857 Then '// If Table already exists
             '// TestLink for valid path, If path is not valid then delete table and reattach.
             OpenDB(DBCon, AttachToMDB, AttachToPWD)'// open database (build connection string)
             If Not MySet.Open("Select * From " & AsTableName, DBCon) Then '// Invalid Source Path
                MySet.Close()
                CloseDB(DBCon)
                Call ADODeleteTable(AttachToMDB, AsTableName, AttachToPWD)
                r = AttachTable(TableName, AttachToMDB, AttachFromMDB, AsTableName, AttachToPWD, AttachFromPWD)
                If r = 0 Then
                   Result = True
                Else
                   Err_HandlerLog(True, Information.Err().Description, "ADOAttachTable")
                End If
             Else
                MySet.Close()
                CloseDB(DBCon)
                Result = True
             End If
          Else
             Result = True
          End If

    Exit_Proc:
          On Error GoTo 0
          Return Result
       End Function

       Private Function AttachTable(ByRef TableName As String, _
                                    ByVal AttachToMDB As String, _
                                    ByVal AttachFromMDB As String, _
                                    Optional ByVal AsTableName As String = "", _
                                    Optional ByVal AttachToPWD As String = "", _
                                    Optional ByVal AttachFromPWD As String = "") As Integer

          Dim result As Integer = 0
          Dim dbCAT As New ADOX.Catalog
          Dim TBL As New ADOX.Table

          On Error GoTo Err_Proc

          If String.IsNullOrEmpty(AsTableName) Then AsTableName = TableName
          If String.IsNullOrEmpty(AttachToPWD) Then AttachToPWD = DB_PWD

          '// Open the catalog
          dbCAT.let_ActiveConnection(GetConnectString(AttachToMDB, AttachToPWD))

          '// Set the name and target catalog for the table
          TBL.Name = AsTableName.ToUpper
          TBL.ParentCatalog = dbCAT

          '// Set the properties to create the link
          TBL.Properties("Jet OLEDB:Create Link").Value = True
          TBL.Properties("Jet OLEDB:Link Datasource").Value = AttachFromMDB
          TBL.Properties("Jet OLEDB:Link Provider String").Value = ";Pwd=" & AttachFromPWD
          TBL.Properties("Jet OLEDB:Remote Table Name").Value = TableName

          '// Append the table to the collection
          dbCAT.Tables.Append(TBL)

    Exit_Proc:
          On Error Resume Next
          dbCAT = Nothing
          TBL = Nothing
          On Error GoTo 0
          '// return "No Error"
          Return result

    Err_Proc:
          Dim sMsg As String = Err.Description
          '// return error number
          result = Information.Err().Number
          Information.Err().Clear()
          Resume Exit_Proc
       End Function

     

    Wednesday, July 13, 2011 2:29 PM

Answers

  • I'm not sure why you didn't want to use Microsoft.VisualBasic assembly in your project. After I checked your need, unfortunately, I didn't find any assemblies can replace it. But I found some logics in Reflector. Perhaps you can consider re-writing this logic in your project.

    Information.Err().Number was like the following code snippet in the bottom frame.

    public int Number
    {
      get
      {
        if (this.m_NumberIsSet)
        {
          return this.m_curNumber;
        }
        if (this.m_curException != null)
        {
          this.Number = this.MapExceptionToNumber(this.m_curException);
          return this.m_curNumber;
        }
        return 0;
      }
      set
      {
        this.m_curNumber = this.MapErrorNumber(value);
        this.m_NumberIsSet = true;
      }
    }
    
    
    In MapExceptionToNumber function, you will find it just got the exception type and enumerate some general exception (IndexOutOfRangeException, RankException, etc.) in it (using If...Else block) like the following code snippet.
    private int MapExceptionToNumber(Exception e)
    {
      Type type = e.GetType();
      if (type == typeof(IndexOutOfRangeException))
      {
        return 9;
      }
      if (type == typeof(RankException))
      {
        return 9;
      }
      //others
    }
    
    In addition, you will find MapErrorNumber(Int32) function in ErrObject class. It just recieved hResult (number) and handle each scenario via Switch...Case block like the following code snippet.
    internal int MapErrorNumber(int Number)
    {
      if (Number > 0xffff)
      {
        throw new ArgumentException(Utils.GetResourceString("Argument_InvalidValue1", new string[] { "Number" }));
      }
      if (Number < 0)
      {
        if ((Number & 0x1fff0000) == 0xa0000)
        {
          return (Number & 0xffff);
        }
        switch (Number)
        {
          case -2147467263:
            return 0x1bd;
    
          case -2147467262:
            return 430;
    
          //others
    }
    

    I hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 3:58 AM

All replies

  • Thank for the post.

    Whenever possible, we suggest you can check Try...Catch...Finally block intead of unstructured exception handling and On Error block. Please check this document for more information about Try...Catch...Finally (VB). http://msdn.microsoft.com/en-us/library/fk6t46tz.aspx

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Saturday, July 16, 2011 1:42 PM
  • Larcolais,

    I agree that Try/Catch should be used.  The problem is that Catch does not return an error number.  I guess (in the Function AttachTable) I could check the Catch error message for .IndexOf("already exists") but that does not seem like the best way.

        Example:
        Catch ex As Exception
            If msErrorMsg.IndexOf("already exists") > -1 Then
                result = -2147217857
            End If

        Is there a better way?

    Monday, July 18, 2011 2:20 PM
  • Why not just look through the Tables collection first to see if you get a match on the table name?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, July 18, 2011 2:44 PM
  • I'm not sure why you didn't want to use Microsoft.VisualBasic assembly in your project. After I checked your need, unfortunately, I didn't find any assemblies can replace it. But I found some logics in Reflector. Perhaps you can consider re-writing this logic in your project.

    Information.Err().Number was like the following code snippet in the bottom frame.

    public int Number
    {
      get
      {
        if (this.m_NumberIsSet)
        {
          return this.m_curNumber;
        }
        if (this.m_curException != null)
        {
          this.Number = this.MapExceptionToNumber(this.m_curException);
          return this.m_curNumber;
        }
        return 0;
      }
      set
      {
        this.m_curNumber = this.MapErrorNumber(value);
        this.m_NumberIsSet = true;
      }
    }
    
    
    In MapExceptionToNumber function, you will find it just got the exception type and enumerate some general exception (IndexOutOfRangeException, RankException, etc.) in it (using If...Else block) like the following code snippet.
    private int MapExceptionToNumber(Exception e)
    {
      Type type = e.GetType();
      if (type == typeof(IndexOutOfRangeException))
      {
        return 9;
      }
      if (type == typeof(RankException))
      {
        return 9;
      }
      //others
    }
    
    In addition, you will find MapErrorNumber(Int32) function in ErrObject class. It just recieved hResult (number) and handle each scenario via Switch...Case block like the following code snippet.
    internal int MapErrorNumber(int Number)
    {
      if (Number > 0xffff)
      {
        throw new ArgumentException(Utils.GetResourceString("Argument_InvalidValue1", new string[] { "Number" }));
      }
      if (Number < 0)
      {
        if ((Number & 0x1fff0000) == 0xa0000)
        {
          return (Number & 0xffff);
        }
        switch (Number)
        {
          case -2147467263:
            return 0x1bd;
    
          case -2147467262:
            return 430;
    
          //others
    }
    

    I hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 3:58 AM
  • Thank you for spending the time to find the answer I was looking for.
    Tuesday, July 19, 2011 12:50 PM
  • It's my pleasure. Welcome to come back if you have any question later.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 3:30 PM