Hi all,

I am a complete newbie, so probably this problem I have is quite simple:

I am having about a couple of hundreds arrays which are different from size. The arrays look like this:

1,5,7,18,29
1,3,19,46,72,87
1,2,4,11,18,23,33,45,61
1,7,14
1,4,13,20,31

etc, etc.

What I am aiming for is to convert these arrays into a table with 2 fields, like:

field 1 field 2:
1 29
5 29
7 29
18 29
29 29
1 87
3 87
19 87
46 87
72 87
87 87
1 61
2 61
4 61
etc

IS there someone who can help me out with this. You can quote me if you want, as I am eagely looking to solve this issue.

ciao!

Mobley

Paste the following text into a new code module:

Public Sub CvtArray(strArray As String, tblName As String, _
fld1Name As String, fld2Name As String)
Dim Cvt As Integer, strTemp As String
ReDim varArray(0) As String

Do While InStr(1, strTemp, ",") > 0
ReDim Preserve varArray(UBound(varArray) + 1)
varArray(UBound(varArray) - 1) = Left(strTemp, InStr(1, strTemp, ",") - 1)
strTemp = Mid(strTemp, InStr(1, strTemp, ",") + 1)
Loop

ReDim Preserve varArray(UBound(varArray) - 1)

For Cvt = 0 To UBound(varArray)
CurrentDb.Execute "INSERT INTO [" & tblName & "] ([" & fld1Name & "],[" & fld2Name & "]) " _
& "SELECT '" & varArray(Cvt) & "','" & varArray(UBound(varArray)) & "';"
Next Cvt
End Sub


For example, to paste an array from your example as a string (e.g. "1,2,4,11,18,23,33,45,61") to fields Field1 and Field2 in table Table1, call the Sub thus:

Call CvtArray("1,2,4,11,18,23,33,45,61", "Table1", "Field1", "Field2")


See if this works for you.

ByteMyzer!

Many thanx!

It is still a bit abracadabra for me, what you are telling me.

What I did is that I copied the code in a new module. So far so good I think...

Than I cannot follow you anymore. :confused:

Isn't there a way to use this Sub to convert the whole table with arrays in a split second to another table with the aimed output?


Next to that I made a mistake... :eek: Sincerely sorry...

the output should be like this:

field 1 field 2 field 3
1 29 5
5 29 7
7 29 18
18 29 29
29 29 0
1 87 3
3 87 19
19 87 46
46 87 72
72 87 87
87 87 0
1 61 2
2 61 4
4 61 11
etc

so, an extra third field which is the figure from the string just behind the figure that was put in the first field.... :rolleyes:

Again as mentioned before, I am willing to pay you for this code. No problem. You are of great help.

ciao, Mobley

If you could attach a sample copy of your database, which shows the source and destination table and data structures, I would be better able to help you.

What I can do is send you the entire MDB file. It is actually a very simple one.

I can send it to your emailaddress. Please send an email to: ralph@delicasa.nl. Than I can email you the MDB file

Like I said, I have one table with records of one field containing the arrays.

In Boyce Codd NF I have:

paths(path#, path)
The field "path" holds 1 array e.g. "1,3,4,23,42" in record 1 and "1,2,4,22,34,49,88" for record 2, etc, etc. So it is a list of arrays with different lengths

segment(segment#, field 1, field 2, field 3)
With the above mentioned arrays, the values in this table for "field 1" shoud become like:

1----------------3---------------42
3----------------4---------------42
4----------------23--------------42
23---------------42--------------42
42---------------0---------------42
1----------------2---------------88
2----------------4---------------88
4----------------22--------------88
22---------------34--------------88
34---------------49--------------88
49---------------88--------------88
88---------------0---------------88

So the algorithm is (I think):

Big loop that picks up every array out of every record in the array table. In other words: keep on picking up arrays until the end of the recordset in table "paths"

a small loop that picks out the first figure in the array, puts in the first field of the segment table, take the second figure, put it in field2 and than take the last figure and put it on the last field.
This loop continues for every figure in the array until he reaches the end! When he reaches the end he has to put "0" in field 2

hth!

many thx!

mobley

segment(segment#, field 1, field 2, field 3)
With the above mentioned arrays, the values in this table for "field 1" shoud become like:

mobley

SORRY MISTAKE:
tha values in this table for : "field1", "field2" and "field3".....

ofcourse not only "field1"...sorry

Here's the code you need:

Public Sub Segment_Generate()
Dim rst As Recordset
Dim varPath As Variant, intCounter As Integer

Set rst = CurrentDb.OpenRecordset("Paths", dbOpenDynaset)

Do While Not rst.EOF
varPath = varArray(rst![Shortest Path], ",")
For intCounter = 0 To UBound(varPath) - 1
CurrentDb.Execute "INSERT INTO Segments ([Field 1], [Field 2], [Field 3]) " _
& "SELECT '" & varPath(intCounter) & "', '" & varPath(intCounter + 1) & "', '" & varPath(UBound(varPath) - 1) & "';"
Next intCounter
rst.MoveNext
Loop

rst.Close
End Sub

Public Function varArray(strArray As String, strDelimiter As String) As Variant
Dim strTemp As String, strPos As Integer
ReDim varTemp(0) As String
strTemp = strArray
If InStr(2, strTemp, strDelimiter) = 0 Then
ReDim Preserve varTemp(1)
varTemp(0) = strTemp
varTemp(1) = "0"
Else
Do
varTemp(UBound(varTemp)) = Left(strTemp, InStr(1, strTemp, ",") - 1)
strTemp = Mid(strTemp, InStr(1, strTemp, ",") + 1)
ReDim Preserve varTemp(UBound(varTemp) + 1)
Loop Until InStr(1, strTemp, ",") = 0
varTemp(UBound(varTemp)) = strTemp
ReDim Preserve varTemp(UBound(varTemp) + 1)
varTemp(UBound(varTemp)) = "0"
End If
varArray = varTemp
End Function

Thanks so much!

But how do you run this code? When I am running this code I immediately get an error in the Public Sub at:

Set rst = CurrentDb.OpenRecordset("Paths", dbOpenDynaset)

it says: Runtime Error 3001: invalid Argument

You might be missing some library references in your MDB file. Try importing the tables and code to a new blank mdb file.

Problem remains. I guess it is running at your side?

I just open the Module with the code you made, and Press F5....and get the error

I am using Access 2002 en VB 6.3

ByteMyzer!

A Million! It works. Not on my machine, but I put the mdb file on my laptop with Access 2003. Works abolutely fine!

What version are you using?

Do you know why this not working for Access 2002?

Again, many, many thanx!

Mobley,

It is a references problem. Use the Search facility here and look for
references.

Wayne

Mobley,

Just reread this.

Get your code in Design View.

Select Tools --> References

Select Microsoft DAO & Place it Higher than the ADO reference on the priority
list (if it is selected).

You are defaulting to ADO. Change your references to be like:

Dim rst As DAO.Recordset and/or
Dim dbs As DAO.Database

Then compile your code.
You should be OK.

Wayne