Cannot open backup device 'C:\PATH\*.BAK'
Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
I added “NETWORK SERVICE” to the file root with the following permissions and the resore worked fine.
Extracting Text from Access Fields into New Fields
All too often, people will put multiple data sets into one field in a table. This could hinder querying or reporting on the data or if you were to convert databases down the road.
I encountered data like this in Access and needed to extract it:
A1234567 -> Okay!
A1234567/B1234567
and even:
A1234567/B1234567/C1234567
What is the formula to extract this?
We want to break this into three fields in an Access Query.
To grab the first set regardless of how many entries:
Field1: IIf(Mid([FIELD],9,1)='/',Left([FIELD],8),[FIELD])
To extract the second field:
Field2: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Mid([FIELD],10,8),IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)<>'/',Right([FIELD],8)," "))
Finally, the third field:
Field3: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Right([FIELD],8)," ")
So that was the easy one. Why? This was easy because all the data was the same length. What would you do about cases where you encounter variable data?
CompanyA
CompanyA/CorpB
CorpB/CompanyA
What is the formula to extract this?
Again, we want to break this into two fields in an Access Query.
To grab the first set regardless of how many entries:
Field1: IIf(([FIELD]) Like "*/*",Left([FIELD],InStr(1,[FIELD],"/")-1),[FIELD])
For the second field:
Field2: IIf(([FIELD]) Like "*/*",Right(Trim([FIELD]),Len(Trim([FIELD]))-InStr(1,[FIELD],"/")),"")
If you were converting from Access to SQL Server, for example, I suggest doing the work before converting. Access offers good querying tools and it is a good idea to take advantage of them. Don’t carry forward the mistakes of the past if you don’t have to!
