Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vba routine function needed

Posted on 2014-01-29
10
Medium Priority
?
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

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 31

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

604 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