Getting and updating columns in database

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Sid Price
    #1

    Getting and updating columns in database

    I have an application that has already been deployed and we need to add a
    new column to some of the tables in the application database. We are using
    an access database. We have a solution working that basically opens a
    dataset for each of the tables that needs updating, this dataset selects all
    rows with the new column and the exception that arises when the column dies
    not exist is caught and dealt with. It works fine with one exception and
    that is that when the database has the column and a load of data in it
    reading the dataset takes a long time.

    '

    ' Iterate through the data tables

    '

    For Each oTableRow In oTableNames.Tab les("Tables").R ows

    Dim strTableName As String

    strTableName = oTableRow("Tabl eName")

    oDatabase.Progr ess("Processing " & strTableName & " ...")

    '

    ' Check if the tables has a "Comment" column, if not add it

    '

    Try

    oTableRecords = oDatabase.GetDa taset("SELECT Comment FROM " & strTableName,
    "TableRecor ds")

    Catch ex As Exception

    '

    ' "Comment" not found, add it to this table

    '

    End try

    Is there a way to check if the tables in the database have the required
    column with using a select query to attempt to read those columns?


    Thanks,
    Sid.


    Tags: None
    • =?Utf-8?B?S2VycnkgTW9vcm1hbg==?=
      #2
      RE: Getting and updating columns in database

      Sid,

      You might try a select statement that does not return any data:

      "SELECT Comment FROM " & strTableName & " Where 1 = 0"

      Kerry Moorman


      "Sid Price" wrote:
      I have an application that has already been deployed and we need to add a
      new column to some of the tables in the application database. We are using
      an access database. We have a solution working that basically opens a
      dataset for each of the tables that needs updating, this dataset selects all
      rows with the new column and the exception that arises when the column dies
      not exist is caught and dealt with. It works fine with one exception and
      that is that when the database has the column and a load of data in it
      reading the dataset takes a long time.
      >
      '
      >
      ' Iterate through the data tables
      >
      '
      >
      For Each oTableRow In oTableNames.Tab les("Tables").R ows
      >
      Dim strTableName As String
      >
      strTableName = oTableRow("Tabl eName")
      >
      oDatabase.Progr ess("Processing " & strTableName & " ...")
      >
      '
      >
      ' Check if the tables has a "Comment" column, if not add it
      >
      '
      >
      Try
      >
      oTableRecords = oDatabase.GetDa taset("SELECT Comment FROM " & strTableName,
      "TableRecor ds")
      >
      Catch ex As Exception
      >
      '
      >
      ' "Comment" not found, add it to this table
      >
      '
      >
      End try
      >
      Is there a way to check if the tables in the database have the required
      column with using a select query to attempt to read those columns?
      >
      >
      Thanks,
      Sid.
      >
      >
      >

        Comment

        • Sid Price
          #3
          Re: Getting and updating columns in database

          Thank you, that did indeed speed things up enormously,
          Sid.

          "Kerry Moorman" <KerryMoorman@d iscussions.micr osoft.comwrote in message
          news:E61C5BF8-1716-42B8-B283-69883AA4D7D4@mi crosoft.com...
          Sid,
          >
          You might try a select statement that does not return any data:
          >
          "SELECT Comment FROM " & strTableName & " Where 1 = 0"
          >
          Kerry Moorman
          >
          >
          "Sid Price" wrote:
          >
          >I have an application that has already been deployed and we need to add a
          >new column to some of the tables in the application database. We are
          >using
          >an access database. We have a solution working that basically opens a
          >dataset for each of the tables that needs updating, this dataset selects
          >all
          >rows with the new column and the exception that arises when the column
          >dies
          >not exist is caught and dealt with. It works fine with one exception and
          >that is that when the database has the column and a load of data in it
          >reading the dataset takes a long time.
          >>
          >'
          >>
          >' Iterate through the data tables
          >>
          >'
          >>
          >For Each oTableRow In oTableNames.Tab les("Tables").R ows
          >>
          >Dim strTableName As String
          >>
          >strTableName = oTableRow("Tabl eName")
          >>
          >oDatabase.Prog ress("Processin g " & strTableName & " ...")
          >>
          >'
          >>
          >' Check if the tables has a "Comment" column, if not add it
          >>
          >'
          >>
          >Try
          >>
          >oTableRecord s = oDatabase.GetDa taset("SELECT Comment FROM " &
          >strTableName ,
          >"TableRecords" )
          >>
          >Catch ex As Exception
          >>
          >'
          >>
          >' "Comment" not found, add it to this table
          >>
          >'
          >>
          >End try
          >>
          >Is there a way to check if the tables in the database have the required
          >column with using a select query to attempt to read those columns?
          >>
          >>
          >Thanks,
          >Sid.
          >>
          >>
          >>

            Comment

            Previous template Next
            Working...