I have a question that I wonder if someone can help with...

I have imported 98000 image addresses by hyperlink into an access database. Unfortunately, the addresses which include multiple-page documents are not able to be accessed. Do I have to create a macro or some other trick to untie the multi-page doccuments? An example is as follows:

This one works:
J:\IMAGES\0000\ABS-0000008.JPG

This one does NOT work:

J:\IMAGES\0000\ABS-000000{3-4}.JPG

The problem seems to lie in the bracketing...any ideas would be appreciated.

Presuming that the numbers shown in this example:
J:\IMAGES\0000\ABS-000000{3-4}.JPG

represent two images :

3) J:\IMAGES\0000\ABS-0000003.JPG

4) J:\IMAGES\0000\ABS-0000004.JPG

And other variations that say for example you had :

J:\IMAGES\0000\ABS-000000{2-9}.JPG

and this represented images to through two nine (seven images in all)
It should be possible to create a function that will make new hyperlinks out of the old hyperlink. You would probably have to write some DAO code to make new records for the newly generated hyperlinks .

Can anyone give me a hand with the language needed to ungroup a range of numbers within a hyperlink address, into new record rows...

For instance, changing ........0000{4-7}.JPG into

00004.jpg
00005.jpg
00006.jpg
00007.jpg
with repeat of all other fields on each line (remain indentical).

Any help would be appreciated.

Could you post a cut down version of your database with say ten or twenty example records in it.

Does this part of the string ever change ?

J:\IMAGES\0000\ABS-

and the same for this part of the string : 000000{2-9}.JPG

the result is :
0000002.JPG
or
0000009.JPG

However if the string was this: 000000{1-25}.JPG

would be answered be this,
0000025.JPG

or would it be this,
00000025.JPG ---- notice the number of zeros it's different.

Unfortunately, the number of 0's does change, as does the file extension before that which has four digits (/0010/), and also the initials change. Suffice it to say almost all parts do change in this set of 100,000 documents. Finally, some of the images are JPG and some TIF.The only thing I see that forms a basis for creating a loop or a function is the brackets, which is the focus of my problem.

As my amateur mind sees it,
I need a way to ask the table,

"if there exists a set of brackets(which can be determined by querying as to the front bracket "{" if necessary) in a record row, take that range of numbers within the set of brackets and break out the number range into its component numbers on successive rows, appending it to the prefix and suffix of it's path, leaving all else the same, and then follow that with the next row sequentially from the original table.
I know I am making a complex problem sound simple, but this is the simple form of what I am looking to do.

Any help you could give me is greatly appreciated.

If you would like to post a DB we can at least make a start . The

It is as small as I can Make it and still 211 kb, with the limit here at 100kb., Any thoughts? Otherwise I am trying ...thanks for your patience.

Do you want to email it to me (as a *.Zip file please) at walterREMOVE.towerSPAM@lycos.co.uk (Remove "REMOVE SPAM")

all you need to do is find the values between the curlies , split them out and loop through with some string manipulation to concatenate them back onto a new string with the proper extension.

A regular expression would help you get those values nicely.
Do a search for regular expression on this forum. I've posted a handful of posts on this topic.

Did you get the Zip file?

Did you get the Zip file?

Yes, got it thanks, it's only 6Kb it should post OK, why not post it so everyone can look at it?

I tried earlier but the file showed as 210kb. Anyway, here it is!!!

Any ideas?

I am concerned for issues of etiquette, etc. I do not know whether to re-post my question or, frankly, what I should do. I have a problem with a database table as described above and in the posted table.

I have come to see that the fact these are hyperlinks is, really, irrelevant, since I am importing the links into access from excel, and could assign them as text, then later convert to hyperlink by copying or whatever. The issue I am truly perplexed over is writing a code to separate out the ranges of numbers seen within the strings which appear in the examples above.

I would be eternally grateful for any assistance. I have spent the last 5 days trying to comprehend and create the necessary code, and simply do not have the ability to create what I need.

Please pardon my indulgence/

Kevin Walsh

I would love to give you a hand with this, unfortunately I have been busy at work and next week I am on holiday, if I'm lucky I will be able to take a laptop with me! (depends on Whether the wife sees me sneak it in the suitcase)

To get you started and hopefully to generate some interest I thought I would outline the way I think it could be done. I think I would use DAO to Loop through a recordset extracting one record at a time, the unique record number and the offending text. Next I would need to write some code to extract the number portion this would give you the information you would need to re-generate the link with a single number using a for next loop.

Now this is the part that I have little experience with, it will be necessary to append both the record number and the newly generated links in turn to a new table.

A more experienced programmer may be able to offer a better method.
Also I would be very interested in Kodo's regular expressions, as I have no experience with these.

You can make a start yourself , see if you can develop the code to find the position of the square Brackets "[" and "]" in the string . The link is actually stored as a string so you should be able to determine the positions of the square brackets . Once you have done this you can break the string down into several sub strings, and once the numbers have been extracted re-assemble the string . You may think this sounds difficult to do but I think if you have a go at it you will find that it is relatively easy , and practice makes perfect !

here is a function that will parse what you need and make the new file names for you. Just pass the original string to it and it will grab the numbers {x-x} and make new single file names within that range.




Function DoNames(mysring As String) As String


Dim ObjRegExp As Object
Set ObjRegExp = New RegExp

ObjRegExp.pattern = "(\w*\-)(0+)\{([0-9])\-([0-9])\}(\.jpg)"


ObjRegExp.Global = True
ObjRegExp.ignorecase = True

Set Matches = ObjRegExp.Execute(mystring)

pre = ObjRegExp.Replace(Matches(0), "$1")
middle = ObjRegExp.Replace(Matches(0), "$2")
GetNum1 = ObjRegExp.Replace(Matches(0), "$3")
GetNum2 = ObjRegExp.Replace(Matches(0), "$4")
extension = ObjRegExp.Replace(Matches(0), "$5")


For i = GetNum1 To GetNum2
Debug.Print pre & middle & i & extension
Next

Set Matches = Nothing
Set ObjRegExp = Nothing
End Function


You'll need to make sure you have VBscript 5.5 or greater. The latest is 5.6. Then you'll need to reference VBScript regular expressions 5.5 in your app.

Don't forget to DIM your variables.. I didn't do that in this function. I leave that up to you.

And you may benefit from reading this previous thread (http://www.access-programmers.co.uk/forums/showthread.php?t=66460&highlight=Regular+expressions)

Obviously regular expressions are a bit advanced for a beginner, I know nothing about them, but I am developing a healthy interest. I do recall when I first encountered combo boxes my head ached for days as I tried to understand how they worked. But now I think nothing of them. I hope I can become as well versed in regular expressions as I am in combo boxes!

Right , I'm not getting anywhere !

I tried the following code in the immediate window :

donames("J:\Blaze_LG_Gold\CaseData\PBGMC CONSOLIDATED MASTER\IMAGES\0000\AXT-000000{7-9}.JPG")

but I got an error. I dimmed everything as string the first time then as integer the second time but both times I got an error message ?

Any thoughts ?

Right , I'm not getting anywhere !

I tried the following code in the immediate window :

donames("J:\Blaze_LG_Gold\CaseData\PBGMC CONSOLIDATED MASTER\IMAGES\0000\AXT-000000{7-9}.JPG")

but I got an error. I dimmed everything as string the first time then as integer the second time but both times I got an error message ?

Any thoughts ?

it only takes this value
AXT-000000{7-9}.JPG

if you want the whole path to go in there then you'll need to add this to get the jpg string BEFORE you pass it to the function
assuming:
MyPath=":\Blaze_LG_Gold\CaseData\PBGMC CONSOLIDATED MASTER\IMAGES\0000\AXT-000000{7-9}.JPG"

MyString=split(myPath,"\")

MyJpegString=MyString(ubound(mystring))

donames(MyJpegString)

but it will fill you guys in on just how much of a newbie I am. Am I supposed to just plug the code Kodos wrote into a query SQL field and hit run?

I assume I have to change the names Kodos used such as "MyJPEGString" to the name my column has already?

And how do I Dim?

but it will fill you guys in on just how much of a newbie I am. Am I supposed to just plug the code Kodos wrote into a query SQL field and hit run?

I assume I have to change the names Kodos used such as "MyJPEGString" to the name my column has already?

And how do I Dim?

Dim pre as string
Dim middle as string
Dim Extension as string
Dim GetNum1 as int
Dim GetNum2 as int
Dim MyString as string
Dim MyJpegString as string

When you Dim (Dimension) you allocate memory for a variable and give it a data-type. If you do not, they are considered variants and take up more memory generally is inefficient.

MyJpegString is a VARIABLE and nothing more. You pass this variable to the function.

this part of the function

For i = GetNum1 To GetNum2
Debug.Print pre & middle & i & extension
Next


is where your SQL will take place.

You can do something like


dim Fname as string
set conn=currentdatabase.connection

For i = GetNum1 To GetNum2
Fname= pre & middle & i & extension
SQL="Insert into TABLENAME (FIELDNAME) values('"& Fname &"') "
conn.execute(SQL)
Next

conn.close


that makes a connection to the DB.. and runs an insert command to the table.

Hope this clears things up..

I will be able to take a laptop with me!

I am not taking the laptop with me ! They charge £1.20 per minute for internet access !