VB 6.0 - Getting Sql View Text

  • Thread starter Thread starter elliotbusiness
  • Start date Start date
E

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
 
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...
 
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
 
Back
Top