VB6 / Access DAO Add Field to Existing Table/Query

[SCBWV]

I have a VB6 application that uses DAO to read and write MS Access files. Since the program uses Access files exclusives, it was written in DAO instead of ADO. The recordsets within the program are based on queries in the database. I need to add a field to the table and query. I'm sure I can figure out testing for the existence of the field in. My question: How can I add a new field to the table AND query?

[atplerry]

Pls i also need to know how to use DAO instead of ADO Because i know a bit of ADO Connection and am seeking to know as a fresh user of DAO connect to the Database, You can email some few example if present OR send the site that give tutorial on either online or ebooks pls send the detail to the address below
[e-mail]

[choudhuryshouvi]

I have a VB6 application that uses DAO to read and write MS Access files. Since the program uses Access files exclusives, it was written in DAO instead of ADO. The recordsets within the program are based on queries in the database. I need to add a field to the table and query. I'm sure I can figure out testing for the existence of the field in. My question: How can I add a new field to the table AND query?

check this sample code in the attachment.
hope this will be able to give u some idea.

regards
Shouvik

[choudhuryshouvi]

Pls i also need to know how to use DAO instead of ADO Because i know a bit of ADO Connection and am seeking to know as a fresh user of DAO connect to the Database, You can email some few example if present OR send the site that give tutorial on either online or ebooks pls send the detail to the address below
[e-mail]

check out this sample project.
it was created in DAO technique and it has everything u need to know about connecting the database using DAO object and frequently used database operations.

regards
Shouvik

[SCBWV]

Ok, I figured out how to add a field to an existing table with DAO

Set dbsAccess = DBEngine.Workspaces(0).OpenDatabase(dbFile$, False, False)
'Agencies is a table in the Access database
Set td = dbsAccess.TableDefs("Agencies")
'Address2 is the field to be added
Set fd = td.CreateField("Address2", dbText, 50)
td.Fields.Append fd
dbsAccess.Close

Now the question is, how do I add the new field "Address2" to existing queries in the database? The database contains a query call "Agency Query." I need to add Address2 to this query.

Thanks for any help.

[SCBWV]

Nevermind... I figured it out. Using SQL of the QueryDefs overwrites the original query in the database with the new one. This thread can be closed.

Set dbsAccess = DBEngine.Workspaces(0).OpenDatabase(dbFile$, False, False)
Set qd = dbsAccess.QueryDefs("Agency Query")
qd.SQL = "SELECT...
dbsAccess.Close

[choudhuryshouvi]

Nevermind... I figured it out. Using SQL of the QueryDefs overwrites the original query in the database with the new one. This thread can be closed.

Set dbsAccess = DBEngine.Workspaces(0).OpenDatabase(dbFile$, False, False)
Set qd = dbsAccess.QueryDefs("Agency Query")
qd.SQL = "SELECT...
dbsAccess.Close

if you have benefited from the reply then post a feedback and mark the thread as solved.