By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,947 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,947 IT Pros & Developers. It's quick & easy.

Getting and updating columns in database

P: n/a
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.
May 4 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
May 4 '07 #2

P: n/a
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.

May 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.