Solved

vba routine function needed

Posted on 2014-01-29
10
439 Views
Last Modified: 2014-01-29
hi, i am looking for a function that should do the following:

table1 has a column note
table2 has a column note

data in table1 is saved in one column separated by commas ("a,b,c,d")

function should store the above info in table2 in 4 records.
0
Comment
Question by:bfuchs
  • 4
  • 4
  • 2
10 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819152
Hi,

I can only hope that this is being done in an effort to properly normalize...

Try something like this (untested, add error handling, adjust types and names etc):

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    'split the values into an array
    v = Split(rs("YourField"), ",")
    'insert the values into the new table
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(0)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(1)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(2)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(3)) & """);"
    DoEvents

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window


You can probably manage the same thing through pure SQL as well, though this might give you a better "visual" on what's actually going on.

Note that this code makes some assumptions, particularly a) every row in Table1 has values in that field, and b) there's always exactly 4 values which are comma delimited.  Variations on this will require more logic.

hth
-jack
0
 
LVL 4

Accepted Solution

by:
Jack Leach earned 500 total points
ID: 39819177
Here's one that will handle varying numbers of data, though still reliant on a comma delimiter:

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant
  Dim i As Integer

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    If Not IsNull(rs("YourField") Then

      'split the values into an array
      v = Split(rs("YourField"), ",")

      'insert the values into the new table
      For i = 0 To Ubound(v)
        CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(i)) & """);"
      Next i

      DoEvents

    End If

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819243
Hi Jack,

thanks for replying, I will test that and let you know.

btw,
You can probably manage the same thing through pure SQL as well

just wonder how is that possible, i think would go for that..
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 30

Expert Comment

by:hnasr
ID: 39819435
Field has 3 commas. Is this a fixed format?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819498
hi,
no, it can have 0 to 8 commas.

e.g
a
a,b
a,b,c
a,b,c,x,y,z
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819507
the second function I posted will handle any amount of comments... from 0 to 32,000+ (or change i to a Long instead of Integer to get many more)
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39819525
hi Jack,
It seems that your suggestion would do the job, just waiting to hear if there is a sql solution for that, as i assume that will work much faster...

However if no one comes up with something i will definitely accept that,

Thanks very much for your quick response
Ben
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39819540
If format is not fixed, then a sub as Jack Leach suggested is fine.
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819578
I'm not sure of a SQL solution offhand. While I think it could be done, it would be a bit on the advanced side and I'm not as good with SQL to whip a solution like that off the top of my head the way I can with VBA.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 39819873
Thanks again Jack,
Please if you do find an answer on the sql path, share it with us..
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question