Moving Backend - Need to change data in multiple tables (1 Viewer)

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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 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\<username>\ Documents\References\” and replace it with “F:\References\”

Thanks for any suggestions.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:38
Joined
Feb 28, 2001
Messages
27,001
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.
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:38
Joined
Oct 29, 2018
Messages
21,358
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.
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:38
Joined
Oct 29, 2018
Messages
21,358
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
 

bastanu

AWF VIP
Local time
Today, 06:38
Joined
Apr 13, 2010
Messages
1,401
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
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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]
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:38
Joined
Feb 28, 2001
Messages
27,001
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],[COLOR="Red"]'[/COLOR]C:\Users\username\Documents\[COLOR="red"]'[/COLOR],[COLOR="red"]'[/COLOR]F:\[COLOR="red"]'[/COLOR]);", dbFailOnError

Note the use of apostrophes, which I have highlighted in red for you.
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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],[COLOR="Red"]'[/COLOR]C:\Users\username\Documents\[COLOR="red"]'[/COLOR],[COLOR="red"]'[/COLOR]F:\[COLOR="red"]'[/COLOR]);", 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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:38
Joined
Feb 28, 2001
Messages
27,001
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"
 

Solo712

Registered User.
Local time
Today, 09:38
Joined
Oct 19, 2012
Messages
828
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 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\<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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:38
Joined
Feb 28, 2001
Messages
27,001
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.
 

Solo712

Registered User.
Local time
Today, 09:38
Joined
Oct 19, 2012
Messages
828
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
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
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"

Just wanted to respond to your suggestion about searching in this forum. Over the years of searching for specific information in this forum, I've come to realize that I had better results from searching the internet with Google. Consequently, I have to admit that I don't use the search feature in this forum very much anymore.

For an Access Database forum, you would think that this forum should provide better searching capabilities than is currently available. For instance, searching for "Using Quotes" in just the title produces pages of results that have either of the two words. Unless I'm missing something, I still haven't been able to get good results for something specific I'm looking for in this forum. It would be great if we could use simple boolean to narrow down the search results.

My two cents...
 

isladogs

MVP / VIP
Local time
Today, 13:38
Joined
Jan 14, 2017
Messages
18,186
Using quotes is a very broad search term and it will get lots of hits in AWF - 500 to be precise. I agree it would be good if you could place the phrase in quote marks but that errors. However using advance search you can arrow it down by date or forum member etc

For comparison, this is what I get in Google:

Using quotes
About 1,290,000,000 results (0.54 seconds)

"Using quotes"
About 1,560,000 results (0.36 seconds)

How is that better/more focused?
 

Danick

Registered User.
Local time
Today, 09:38
Joined
Sep 23, 2008
Messages
351
Using quotes is a very broad search term and it will get lots of hits in AWF - 500 to be precise. I agree it would be good if you could place the phrase in quote marks but that errors. However using advance search you can arrow it down by date or forum member etc

For comparison, this is what I get in Google:

Using quotes
About 1,290,000,000 results (0.54 seconds)

"Using quotes"
About 1,560,000 results (0.36 seconds)

How is that better/more focused?

When using Google, you could add move words to narrow the search down even further. For instance, if you put this in a Google search:

access vba .Execute "using quotes"
About 72 results (0.43 seconds)

In fact, this thread shows up 5th from the top in a Google search. That's pretty good. But unfortunately, this doesn't work the same in the search feature in this forum. The advanced search feature in this forum actually produces even more results than before.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:38
Joined
Jan 14, 2017
Messages
18,186
Including the word 'using' isn't necessarily useful
I'm not suggesting the forum search is perfect but using advanced search I tried

1. Quotes VBA Execute ... with a least one reply = 271 results (including this thread of course)
https://www.access-programmers.co.uk/forums/search.php?searchid=13547874

2. As above ... and marked SOLVED = 17 results.
https://www.access-programmers.co.uk/forums/search.php?searchid=13547876

3. As above ...and within the last year = 1 result
https://www.access-programmers.co.uk/forums/search.php?searchid=13547881

IMO its not as bad as you are making out

Anyway, this is all somewhat off topic
 

Users who are viewing this thread

Top Bottom