Home Forum Contact

 Access World Forums Moving Backend - Need to change data in multiple tables
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

 01-25-2019, 06:24 AM #1 Danick Newly Registered User   Join Date: Sep 2008 Posts: 255 Thanks: 41 Thanked 2 Times in 2 Posts Moving Backend - Need to change data in multiple tables Hello, I have a split database and am preparing to relocate the backend to a different computer. Problem is that there is a lot of data in fields that are used as link fields that point to a specific location where the backend used to be. For instance, there are data fields that will have, “C:\Users\\Documents\References\text.txt ” And there are at least 5 different tables that I need to do this to. I can use Find and Replace to change this once, but I will most likely have to do this a few times as I’m testing to make sure all works beforehand. Is there any example of a macro that will do this with a button? For example, It would have to use Find and Replace in a few tables for “C:\Users\\ Documents\References\” and replace it with “F:\References\” Thanks for any suggestions.
 01-25-2019, 06:34 AM #2 The_Doc_Man Happy Retired Curmudgeon   Join Date: Feb 2001 Location: Suburban New Orleans, LA, USA Posts: 13,785 Thanks: 76 Thanked 1,534 Times in 1,423 Posts Re: Moving Backend - Need to change data in multiple tables You can build a macro to do a RunCode after which you can build a FUNCTION (Macros don't run subroutines) that contains the code to update fields to do this. I am not sure, however, why you have DATA leading to this back-end location. Is it because these are actually separate support files, not the actual back-end? If this is an Access back-end that you are moving, you can look at any linked table to find its location in the .Connect property so that if you move the tables, you can then use the moved tables to find the moved tables' new path. Might take just a wee bit of parsing, but not that difficult. Look up "Connect string" in this forum to see articles on how to use it. Now, if your problem is that these files are going to be scattered in different folders and you only are looking for those in one possible folder, that is a more difficult issue. But if ALL of the files are in the same folder, you can find the location of those files by grabbing the connect string and removing the back-end filename and filetype. What's left will either be the drive-letter and path OR the URS (networking) path. Either way, it will lead you there. __________________ I'm a certified grandpa (3 times now) and proud of it. Retired over one year and survived being home all day with the wife. She must really love me. If I have helped you, please either click the thanks or click the scales.
01-25-2019, 08:27 AM   #3
Danick
Newly Registered User

Join Date: Sep 2008
Posts: 255
Thanks: 41
Thanked 2 Times in 2 Posts
Re: Moving Backend - Need to change data in multiple tables

Well I found a clever way of doing this with an update query and using "Replace". Seems to work, but now am trying to run several of these queries in one statement. Can't find a way to do it.

These work on their own update queries

Code:
UPDATE tblAttachments SET tblAttachments.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\Username\","F:\");
and
Code:
UPDATE tblAttachments1 SET tblAttachments1.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\Username\","F:\");
But I get an error about characters at the end of the statement if I try to combine them in one query like this

Code:
UPDATE tblAttachments SET tblAttachments.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\Username\","F:\");
UPDATE tblAttachments1 SET tblAttachments1.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\Username\","F:\");
Any idea as to how I can combine both update queries into one?

01-25-2019, 08:48 AM   #4
theDBguy
I’m here to help

Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,574
Thanks: 29
Thanked 633 Times in 616 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by Danick Any idea as to how I can combine both update queries into one?
Hi. Unfortunately, you can only update one table at a time. You can write a macro or VBA code to run each query in sequence.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

01-25-2019, 08:54 AM   #5
Danick
Newly Registered User

Join Date: Sep 2008
Posts: 255
Thanks: 41
Thanked 2 Times in 2 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by theDBguy Hi. Unfortunately, you can only update one table at a time. You can write a macro or VBA code to run each query in sequence.
You mean I would have to save all of the updates queries separately (ie Query1, Query2, Query3, Query4...). Then create a button somewhere and on click event something like this

Code:
CurrentDb.Execute "Query1"
CurrentDb.Execute "Query2"
Please tell me there is another way of doing this...

 01-25-2019, 08:57 AM #6 theDBguy I’m here to help     Join Date: Oct 2018 Location: SunnySandyEggo Posts: 2,574 Thanks: 29 Thanked 633 Times in 616 Posts Re: Moving Backend - Need to change data in multiple tables No, you don't need to save them as query objects. You can simply execute them as SQL statements. For example: Code: With CurrentDb .Execute "UPDATE Table1 SET...", dbFailOnError .Execute "UPDATE Table2 SET...", dbFailOnError End With __________________ Just my 2 cents... To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. | To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
 01-25-2019, 09:32 AM #7 bastanu Registered User   Join Date: Apr 2010 Location: Kelowna, Canada Posts: 234 Thanks: 1 Thanked 64 Times in 63 Posts Re: Moving Backend - Need to change data in multiple tables To save you from doing this every time you need to change the folder where you store the attachments I suggest you create a local settings table and store the path in a field there. You can show that to the user on a switchboard form or any other "settings" form. Then you store only the file name in your tables and build the full path as needed in your forms, reports, etc. Cheers, Vlad
01-25-2019, 10:04 AM   #8
Danick
Newly Registered User

Join Date: Sep 2008
Posts: 255
Thanks: 41
Thanked 2 Times in 2 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by theDBguy No, you don't need to save them as query objects. You can simply execute them as SQL statements. For example: Code: With CurrentDb .Execute "UPDATE Table1 SET...", dbFailOnError .Execute "UPDATE Table2 SET...", dbFailOnError End With
I put the code in an onclick event for a new button but am getting a compile error, Expected end of statement. Any idea what I'm doing wrong?

Code:
With CurrentDb
.Execute "UPDATE tblAttachments SET tblAttachments.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\username\Documents\","F:\");", dbFailOnError
.Execute "UPDATE tblAttachments1 SET tblAttachments1.AttachmentFilePath = Replace([AttachmentFilePath],"C:\Users\username\Documents\","F:\");", dbFailOnError

End With
[/QUOTE]

 01-25-2019, 11:03 AM #9 The_Doc_Man Happy Retired Curmudgeon   Join Date: Feb 2001 Location: Suburban New Orleans, LA, USA Posts: 13,785 Thanks: 76 Thanked 1,534 Times in 1,423 Posts Re: Moving Backend - Need to change data in multiple tables This happens because your query is delimited by AND CONTAINS double-quote marks. Using the same character for two different purposes in the same string confuses the code parser because you didn't want the inner quoted strings to be compiled - you wanted them to stay as literal values. Try this style: Code:  .Execute "UPDATE tblAttachments SET tblAttachments.AttachmentFilePath = Replace([AttachmentFilePath],'C:\Users\username\Documents\','F:\');", dbFailOnError Note the use of apostrophes, which I have highlighted in red for you. __________________ I'm a certified grandpa (3 times now) and proud of it. Retired over one year and survived being home all day with the wife. She must really love me. If I have helped you, please either click the thanks or click the scales.
 The Following User Says Thank You to The_Doc_Man For This Useful Post: Danick (01-25-2019)
01-25-2019, 12:31 PM   #10
Danick
Newly Registered User

Join Date: Sep 2008
Posts: 255
Thanks: 41
Thanked 2 Times in 2 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by The_Doc_Man This happens because your query is delimited by AND CONTAINS double-quote marks. Using the same character for two different purposes in the same string confuses the code parser because you didn't want the inner quoted strings to be compiled - you wanted them to stay as literal values. Try this style: Code:  .Execute "UPDATE tblAttachments SET tblAttachments.AttachmentFilePath = Replace([AttachmentFilePath],'C:\Users\username\Documents\','F:\');", dbFailOnError Note the use of apostrophes, which I have highlighted in red for you.
That was it!! Thank you so much.

And Vlad, thanks for the local settings suggestion - something I should have done from the start.

 01-25-2019, 01:37 PM #11 The_Doc_Man Happy Retired Curmudgeon   Join Date: Feb 2001 Location: Suburban New Orleans, LA, USA Posts: 13,785 Thanks: 76 Thanked 1,534 Times in 1,423 Posts Re: Moving Backend - Need to change data in multiple tables For future references, you can search the web for topic "MS Access use of quotes" and get a ton of stuff online from multiple forums. Or if you search using this forum's Search facility (see thin ribbon near top of the page, 3rd from right item), just search for "Using Quotes" __________________ I'm a certified grandpa (3 times now) and proud of it. Retired over one year and survived being home all day with the wife. She must really love me. If I have helped you, please either click the thanks or click the scales.
01-25-2019, 08:15 PM   #12
Solo712
Newly Registered User

Join Date: Oct 2012
Posts: 737
Thanks: 14
Thanked 124 Times in 121 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by Danick Hello, I have a split database and am preparing to relocate the backend to a different computer. Problem is that there is a lot of data in fields that are used as link fields that point to a specific location where the backend used to be. For instance, there are data fields that will have, “C:\Users\\Documents\References\text.txt ” And there are at least 5 different tables that I need to do this to. I can use Find and Replace to change this once, but I will most likely have to do this a few times as I’m testing to make sure all works beforehand. Is there any example of a macro that will do this with a button? For example, It would have to use Find and Replace in a few tables for “C:\Users\\ Documents\References\” and replace it with “F:\References\” Thanks for any suggestions.
Hi,
the simplest way I know to handle "resource files" (if that is what they are) is to have them in directories relative to the database location. Then when you move the database (the Front End I assume) you move the resource files into the same relative location. You do not have to re-code anything if you use relative addressing. So e.g. if you place your "References" above the location of the database then you can retrieve material from the folder by the referring to the relative path like this
Code:
 Application.CurrentProject.Path & "\References"
When you move the database, you move all the associated directories with it.

If you want to place your references with the Back End, the same principle would apply. You retrieve the current path to the linked tables and then place the files in the folder "\References" one level above the BE network location.

Best,
Jiri
__________________
Dyslexics of the world untie !

 01-25-2019, 08:25 PM #13 The_Doc_Man Happy Retired Curmudgeon   Join Date: Feb 2001 Location: Suburban New Orleans, LA, USA Posts: 13,785 Thanks: 76 Thanked 1,534 Times in 1,423 Posts Re: Moving Backend - Need to change data in multiple tables Another good method, Jiri, but with a split database, what file path do you get when you use .CurrentProject? The FE or the BE? Because the original question relates to files that are located with the BE. __________________ I'm a certified grandpa (3 times now) and proud of it. Retired over one year and survived being home all day with the wife. She must really love me. If I have helped you, please either click the thanks or click the scales.
 01-25-2019, 09:30 PM #14 GinaWhipp AWF VIP     Join Date: Jun 2011 Location: Ohio, USA Posts: 5,291 Thanks: 20 Thanked 875 Times in 860 Posts Re: Moving Backend - Need to change data in multiple tables This may help going forward... https://www.access-diva.com/d13.html __________________ Gina Whipp Microsoft MVP (Access 2010-2015) To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
01-26-2019, 06:29 AM   #15
Solo712
Newly Registered User

Join Date: Oct 2012
Posts: 737
Thanks: 14
Thanked 124 Times in 121 Posts
Re: Moving Backend - Need to change data in multiple tables

Quote:
 Originally Posted by The_Doc_Man Another good method, Jiri, but with a split database, what file path do you get when you use .CurrentProject? The FE or the BE? Because the original question relates to files that are located with the BE.
Obviously, you have to use a routine to find the current back end to your database, if that is where your resource files are.

Here is my clumsy way of doing it:

Code:
Public Function GETBEPath() As String
Dim strConnect As String, lngPos As Long

strConnect = GetConnectString
' handle zero length string
lngPos = InStr(1, strConnect, "DATABASE=") + Len("DATABASE=")
strConnect = StrReverse(mID(strConnect, lngPos, 255))
lngPos = InStr(1, strConnect, "\")
GETBEPath = StrReverse(mID(strConnect, lngPos, 255))
End Function
'-----------------------------------------------------------------
Private Function GetConnectString() As String
Dim obj As AccessObject, dbs As Object, db As DAO.Database, tdf As TableDef, thisTbl As String

Set dbs = Application.CurrentData
Set db = CurrentDb

For Each obj In dbs.AllTables
Set tdf = db.TableDefs(obj.Name)
If tdf.Connect <> "" Then
If Left(tdf.Connect, Len(";DATABASE=")) = ";DATABASE=" Then
GetConnectString = tdf.Connect
Exit For
End If
End If
Next obj

Set tdf = Nothing
Set db = Nothing
Set dbs = Nothing
End Function
Best,
Jiri

__________________
Dyslexics of the world untie !

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post JJKramer Tables 4 04-04-2007 07:18 AM pdx_man Excel 2 04-17-2006 09:50 AM SteveClarkson Queries 1 01-29-2004 01:02 PM InformerLBBD Tables 11 06-19-2003 11:32 AM MrTibbs Modules & VBA 1 11-08-2001 12:36 AM

All times are GMT -8. The time now is 03:21 PM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Featured Forum post Sponsored Links