By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Acc2K - Read Array into a New Table

MindBender77
100+
P: 234
Hello All,
I'm not very familiar using arrays and found myself getting more confused with each reference source I read. I have an array that I am trying to read into a new table or a temp table.
Here is the function so far:
Expand|Select|Wrap|Line Numbers
  1. Function SplitMemo()
  2. Dim astrMemo,memo
  3. Dim i as integer
  4. memo = Dlookup("[Ordernotes]","tbl_test1")
  5. astrmemo = split(memo,"\")
  6.  
  7. For i = 0 to UBound(astrMemo)
  8. Debug.print astrMemo(i)
  9. Next
  10.  
  11. 'I assume appending the array to a table begins here
  12. End Function
  13.  
Any help is most appreciated,
Bender
Aug 25 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,669
Your details are a little sketchy, but I'll provide you with a Generic Template for populating a Table with Values from an Array. Let's assume you have an Array of Strings consisting of 6 Last Names, and that you wanted to populate a Table named tblNames with these Values into a Field named [Last]:
Expand|Select|Wrap|Line Numbers
  1. Dim astrLastNames(1 To 6) As String
  2. Dim intCounter As Integer
  3. Dim strSQL As String
  4.  
  5. astrLastNames(1) = "Flintstone"
  6. astrLastNames(2) = "Wilson"
  7. astrLastNames(3) = "Johnson"
  8. astrLastNames(4) = "Munster"
  9. astrLastNames(5) = "Reveree"
  10. astrLastNames(6) = "McGettigan"
  11.  
  12. For intCounter = LBound(astrLastNames) To UBound(astrLastNames)
  13.   strSQL = "INSERT INTO tblNames ([Last]) Values ('" & _
  14.   astrLastNames(intCounter) & "');"
  15.   CurrentDb.Execute strSQL, dbFailOnError
  16. Next
Aug 26 '08 #2

MindBender77
100+
P: 234
My apologies for the lack of details and I do thank you for the clarifications. However, I do have a further question concerning Array Usage. Using your example as reference, say that the astrLastNames are populated from a temp table's memo field. What if it is not known that astrLastName(1) = "Flintstone" only that it is the first line delimited from the memo field? Would your template be coded as such?:
Expand|Select|Wrap|Line Numbers
  1. dim memo as string
  2. astrMemo as string
  3. dim astrLastNames(1 to 6) as string
  4.  
  5. memo = dlookup("[Field1]","Table1","[Field1] = some criteria")
  6. astrMemo = Split(memo,"\")
  7.  
  8. astrLastNames(1) = astrMemo(1) ' Is this possible?
  9. 'More code here.....
  10.  
Bender

Expand|Select|Wrap|Line Numbers
  1. Dim astrLastNames(1 To 6) As String
  2. Dim intCounter As Integer
  3. Dim strSQL As String
  4.  
  5. astrLastNames(1) = "Flintstone"
  6. astrLastNames(2) = "Wilson"
  7. astrLastNames(3) = "Johnson"
  8. astrLastNames(4) = "Munster"
  9. astrLastNames(5) = "Reveree"
  10. astrLastNames(6) = "McGettigan"
  11.  
Aug 26 '08 #3

ADezii
Expert 5K+
P: 8,669
My apologies for the lack of details and I do thank you for the clarifications. However, I do have a further question concerning Array Usage. Using your example as reference, say that the astrLastNames are populated from a temp table's memo field. What if it is not known that astrLastName(1) = "Flintstone" only that it is the first line delimited from the memo field? Would your template be coded as such?:
Expand|Select|Wrap|Line Numbers
  1. dim memo as string
  2. astrMemo as string
  3. dim astrLastNames(1 to 6) as string
  4.  
  5. memo = dlookup("[Field1]","Table1","[Field1] = some criteria")
  6. astrMemo = Split(memo,"\")
  7.  
  8. astrLastNames(1) = astrMemo(1) ' Is this possible?
  9. 'More code here.....
  10.  
Bender
Given your unique circumstances and a "\" Delimiter, the syntax would be something like:
Expand|Select|Wrap|Line Numbers
  1. Dim strMemo As String
  2. Dim varMemo As Variant
  3. Dim intCounter As Integer
  4. Dim strSQL As String
  5.  
  6. strMemo = "First\Second\Third\Fourth\Fifith\Sixth"
  7.  
  8. varMemo = Split(strMemo, "\")
  9.  
  10. For intCounter = LBound(varMemo) To UBound(varMemo)
  11.   strSQL = "INSERT INTO tblNames ([Last]) Values ('" & _
  12.            varMemo(intCounter) & "');"
  13.   CurrentDb.Execute strSQL, dbFailOnError
  14. Next
Aug 26 '08 #4

MindBender77
100+
P: 234
Thank you for your assistance, ADezii. With some minor tweaking, things work perfectly.

Thanks Again,
Bender
Aug 28 '08 #5

ADezii
Expert 5K+
P: 8,669
Thank you for your assistance, ADezii. With some minor tweaking, things work perfectly.

Thanks Again,
Bender
You are quite welcome.
Aug 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.