add fields to an existing database 2

[mrregan]

Hello,
I have many users of my vb program which updates a
microsoft data base file. I need to add a couple of new fields to the database. Is there a way in my program that
i can see if the database already contains these fields and also how do I add these new fields to their existing database?

 

[gmmastros]

microsoft data base

Can you be more specific. Is this Access, Fox Pro, or SQL Server?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrregan]

Sorry, I am a bit of a novice with visual basic. These are .mdb files originally designed with visual data manager in Microsoft visual basic. I guess they would Access type files.

 

[gmmastros]

If this had been SQL Server... oh well.

A quick google search found this...

http://databases.aspfaq.com/database/how-do-i-determine-if-a-column-exists-in-a-given-table.html

Don't be too concerned that it's written for ASP. That just means a straight copy/paste won't work. With a little tweaking, you should be well on your way.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrregan]

Thank you, that will get me started! I still need to know how to add fields and their attributes in the vb program.

 

[gmmastros]

Adding columns to a table is similar in Access as it is in SQL Server (which I am much better at). Similar, but not necessarily the same (especially with the data types). The syntax is something like...

[tt][blue]Alter Table [!]TableName[/!] Add Column [!]ColumnName[/!] Text(20)[/blue][/tt]

This may not be perfect, but should help you get started.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Golom]

George

It is perfect!

The various field types in Access (at least the more common ones) are
[tt]
Text [red]Specify the length[/red]
Integer
Long
Double
Single
Currency
Counter [red]<--- Autonumber. Specify Start & Increment[/red]
Byte
Bit [red]<--- Boolean field[/red]
Decimal
Memo
[/tt]

 

[mrregan]

Thanks to all of you, the following code seems to work fine to test for the presence of a new field. The Alter Table worked as well.

Dim i As Integer
Dim sname As String
Dim isnewfield As Boolean
isnewfield=false

Set dbpayroll = DBEngine.Workspaces(0).OpenDatabase(dbpath, False)


Set rsemployee = dbpayroll.OpenRecordset("employee", dbOpenTable)


'Test to see if email field has been added

For i = 0 To rsemployee.Fields.Count - 1
sname = UCase$(rsemployee.Fields(i).Name)
Select Case sname
Case "EMPEMAIL": isnewfield = True
End Select
Next i