469,307 Members | 1,796 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Help With Find Replace in Access


Im trying to do something with my database and Im not even sure if its possible. I have a finished database with about 30 queries all updating the same table. I would like to make an exact copy of this database but I would like all of the new 30 queies to update a table with a different name. All Im trying to do is replace one table with another in all the 30 queries. Is that possible at all?

Thanks so much! Im starting to drive myself up the wall.

best wishes O.K.
Jun 23 '10 #1
2 1284
157 100+
Yes. In a way.

If your new tables overwrites the name of the old table, all queryes will point to new table.
Jun 24 '10 #2
1,260 Expert 1GB
Try this:
1.Back up the entire database.
2. Verify that you backed up the entire database
3. Run this code
Expand|Select|Wrap|Line Numbers
  1. Dim intTbnameBegin As Integer
  2. Dim strOldTbName As String
  3. Dim strNewTbName As String
  4. Dim strOldQryText As String
  5. Dim strNewQryText As String
  6. Dim strQryName As String
  8. strOldTbName = "OldTableName"
  9. strNewTbName = "NewTableName"
  11. For Each qdf In CurrentDb.QueryDefs ' for every query
  12.     strQryName = qdf.Name
  13.     If Left(qdf.Name, 1) <> "~" Then  ' ignore systems stuff
  14.         If intTbnameBegin <> 0 Then   ' if this qry has the table name replace it and rewrite it
  15.             strNewQryText = Replace(strOldQryText, strOldTbName, strNewTbName)
  16.             CurrentDb.QueryDefs.Delete (strQryName)
  17.             Set qdfNew = CurrentDb.CreateQueryDef(strQryName, strNewQryText)
  18.         End If
  19.     End If
  20. Next qdf
Let me know if it worked.

Jun 24 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by ddddd | last post: by
8 posts views Thread by skinnybloke | last post: by
2 posts views Thread by :\\\\derian | last post: by
2 posts views Thread by scorpion53061 | last post: by
reply views Thread by vipal.keshwala | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.