VB 6.0 - Getting Sql View Text

[elliotbusiness]

I have about 100 queries in my database and they all need to be edited
to reflect a new table name that will be used. I don't want to go in
one by one to make the change.

Is there a way from VB6 to retrieve the design Sql Text of a query,
update it to the correct text, and save it?

Thanks,

Elliot Semmelman

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = OpenDatabase("C:\Folder\File.mdb")
For Each qdfCurr In dbCurr.QueryDefs
qdfCurr.SQL = Replace(qdfCurr.SQL, "OldTableName", "NewTableName")
Next qdfCuff

This assumes that OldTableName is unique. If your old table name was
Customer and you also have CustomerInvoice and CustomerAddress, you'll have
problems...

 

[elliotbusiness]

Thanks, your help saved me much time.

-- I used vbtextcompare in the replace function instead of the default
binary compare because the source data was not consistently using the
same case.

-- FYI In the last line, qdfCuff should be qdfCurr.

Elliot Semmelman