Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 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
Danick is on a distinguished road
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\<username>\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 Im 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\<username>\ Documents\References\ and replace it with F:\References\

Thanks for any suggestions.

Danick is offline   Reply With Quote
Old 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
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
The_Doc_Man is offline   Reply With Quote
Old 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
Danick is on a distinguished road
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?

Danick is offline   Reply With Quote
Old 01-25-2019, 08:48 AM   #4
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,574
Thanks: 29
Thanked 633 Times in 616 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by Danick View Post
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.
theDBguy is offline   Reply With Quote
Old 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
Danick is on a distinguished road
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by theDBguy View Post
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...
Danick is offline   Reply With Quote
Old 01-25-2019, 08:57 AM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,574
Thanks: 29
Thanked 633 Times in 616 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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.
theDBguy is offline   Reply With Quote
Old 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
bastanu is on a distinguished road
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

bastanu is offline   Reply With Quote
Old 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
Danick is on a distinguished road
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by theDBguy View Post
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]
Danick is offline   Reply With Quote
Old 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
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Danick (01-25-2019)
Old 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
Danick is on a distinguished road
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by The_Doc_Man View Post
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.
Danick is offline   Reply With Quote
Old 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
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
The_Doc_Man is offline   Reply With Quote
Old 01-25-2019, 08:15 PM   #12
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 737
Thanks: 14
Thanked 124 Times in 121 Posts
Solo712 will become famous soon enough
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by Danick View Post
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\<username>\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 Im 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\<username>\ 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 !
Solo712 is offline   Reply With Quote
Old 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
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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.
The_Doc_Man is offline   Reply With Quote
Old 01-25-2019, 09:30 PM   #14
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,291
Thanks: 20
Thanked 875 Times in 860 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
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.
GinaWhipp is offline   Reply With Quote
Old 01-26-2019, 06:29 AM   #15
Solo712
Newly Registered User
 
Solo712's Avatar
 
Join Date: Oct 2012
Posts: 737
Thanks: 14
Thanked 124 Times in 121 Posts
Solo712 will become famous soon enough
Re: Moving Backend - Need to change data in multiple tables

Quote:
Originally Posted by The_Doc_Man View Post
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 !
Solo712 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving data from 2 related tables to 2 new tables as an archive JJKramer Tables 4 04-04-2007 07:18 AM
Multiple Pilvot Tables on Sheet - Moving them pdx_man Excel 2 04-17-2006 09:50 AM
Query to change data in backend SteveClarkson Queries 1 01-29-2004 01:02 PM
moving data in tables InformerLBBD Tables 11 06-19-2003 11:32 AM
Multiple backend tables with the same name 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


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World