Getting and updating columns in database

[Sid Price]

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.Tables("Tables").Rows

Dim strTableName As String

strTableName = oTableRow("TableName")

oDatabase.Progress("Processing " & strTableName & " ...")

'

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

'

Try

oTableRecords = oDatabase.GetDataset("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.

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

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.Tables("Tables").Rows

Dim strTableName As String

strTableName = oTableRow("TableName")

oDatabase.Progress("Processing " & strTableName & " ...")

'

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

'

Try

oTableRecords = oDatabase.GetDataset("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.

[Sid Price]

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

"Kerry Moorman" <Ke**********@discussions.microsoft.comwrote in message
news:E6**********************************@microsof t.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.Tables("Tables").Rows

Dim strTableName As String

strTableName = oTableRow("TableName")

oDatabase.Progress("Processing " & strTableName & " ...")

'

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

'

Try

oTableRecords = oDatabase.GetDataset("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.