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

how to import complex text file into ms access

P: 14
I only need "sample, additional info, reference, analyte, % and Scaling Ref."

see below for example of the text file,
-----------------------------------------------

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:04 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 23.60
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45
STD 0.17 0.32 0.14 0.35 0.13 0.37 0.093 0.07 0.079 0.12
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:16 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 29.55
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31
STD 0.15 0.28 0.12 0.31 0.11 0.32 0.074 0.06 0.069 0.093
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:28 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 33.03
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42
STD 0.15 0.28 0.11 0.3 0.11 0.32 0.07 0.059 0.066 0.096
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

Method: 1 Metal Mode Sample: A46731 8/1/13 6:57:41 PM
Additional info: C4027
Duration: 5
Good Match
Reference: Inco 718
Difference: 27.10
Screening Method:Ni Chem 1
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34
STD 0.14 0.26 0.11 0.28 0.1 0.3 0.072 0.056 0.061 0.086
Grades: Inconel 718
Scaling Ref. : Inconel 718
Scaling Method: Ni ID

-----------------------------------------------------
thanks for your help
Dec 16 '13 #1

✓ answered by ADezii

@msaccess4me:
I do believe that I have arrived at a viable solution to this interesting Thread. Again, simply extract the Revised Database and Input.txt into the 'same' Folder then Open the Database. Before I put any more time into this, I want to make absolutely sure that this is what you are looking for, or at least close to it.

P.S. - Should you wish to use the Parent ==> Child Table approach, it will be a simple matter to do so since I incorporated an AutoNumber/Primary Key into the Results Table (tblResults). Let us know how you make out.

Share this Question
Share on Google+
14 Replies


zmbd
Expert Mod 5K+
P: 5,331
Is the format set?
Are these all in one file or are these examples in seperate files?

We are not supposed to write the code for you; however, some ideas to get you started:

I'd be looking at the standard IO commands
Some would use the File Scripting Object
ReadLN()
SPLIT()
Test result in element(0) for Method, Additional, etc and select case for action based on that return to process the correct element for the table
if not eof then loop/
Dec 16 '13 #2

ADezii
Expert 5K+
P: 8,616
Extracting this Data could be as simple as a series of If...ElseIf...End If Statements as indicated below:
Expand|Select|Wrap|Line Numbers
  1. Dim strLine As String
  2. Dim intLineNum As Integer
  3.  
  4. Open "C:\Security\Test.txt" For Input As #1
  5.  
  6. Do While Not EOF(1)
  7.   Line Input #1, strLine    'Read line into variable.
  8.     If InStr(strLine, "Sample:") > 0 Then
  9.       Debug.Print Mid$(strLine, InStr(strLine, "Sample:") + 8)
  10.     ElseIf InStr(strLine, "Additional Info:") > 0 Then
  11.       Debug.Print Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
  12.     ElseIf InStr(strLine, "Reference:") > 0 Then
  13.       Debug.Print Mid$(strLine, InStr(strLine, "Reference:") + 11)
  14.     ElseIf InStr(strLine, "Analyte") > 0 Then
  15.       Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
  16.     ElseIf Left$(strLine, 1) = "%" Then
  17.       Debug.Print Mid$(strLine, 3)
  18.     ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
  19.       Debug.Print Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
  20.     End If
  21. Loop
  22.  
  23. Close #1
OUTCOME:
Expand|Select|Wrap|Line Numbers
  1. A46731 8/1/13 6:57:04 PM
  2. C4027
  3. Inco 718
  4. Ti Cr Mn Fe Co Ni Cu Nb Mo W
  5. 0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45
  6. Inconel 718
  7. A46731 8/1/13 6:57:16 PM
  8. C4027
  9. Inco 718
  10. Ti Cr Mn Fe Co Ni Cu Nb Mo W
  11. 1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31
  12. Inconel 718
  13. A46731 8/1/13 6:57:28 PM
  14. C4027
  15. Inco 718
  16. Ti Cr Mn Fe Co Ni Cu Nb Mo W
  17. 1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42
  18. Inconel 718
  19. A46731 8/1/13 6:57:41 PM
  20. C4027
  21. Inco 718
  22. Ti Cr Mn Fe Co Ni Cu Nb Mo W
  23. 1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34
  24. Inconel 718
Now, what do you wish to do with this extracted Data.
Dec 17 '13 #3

P: 14
thank you ADezii, I will like to import these extracted data in to a ms access table with field names as below:
-----------------------------------------
Sample
Date
AdditionalInfo
Reference
Ti
Cr
Mn
Fe
Co
Ni
Cu
Nb
Mo
W
ScalingRef
-------------------------------------------
Is anyway to do it?
Dec 17 '13 #4

zmbd
Expert Mod 5K+
P: 5,331
Read the values in to the variables as either ADezii or myself have indicated.

If the table exsists then a simple insert sql will work.

So I've used your field names for the table
and prefaced each with a "z" for the variables

Thus if the sample is A46731
then set the varible zSample = A46731

Expand|Select|Wrap|Line Numbers
  1. zsql = "INSERT INTO tbl_schedule" & _
  2.        "(Sample,AdditionalInfo,Reference,Ti,Cr," & _
  3.        "Mn,Fe,Co,Ni,Cu,Nb,Mo,W,ScalingRef)" & _
  4.        " VALUES" & _
  5.        " (" & zSample & "," & zAdditionalInfo & _
  6.        "," & zReference & "," & zTi & "," & zCr & _
  7.        "," & zMn & "," & zFe & "," & zCo & _
  8.        "," & zNi & "," & zCu & "," & zNb & _
  9.        "," & zMo & "," & zW & "," & zScalingRef & ")"
  10. zdb.Execute zsql, dbFailOnError
So open the file, parse the text using either ADezii or my method, then insert the records. If the table exsists then the simple insert sql will work.
NOTE: I build the string first this way, I can do a debug.print to see the resolved string as spacing and all of that is importaint.

Also, This may not really be the most normalized method to import your sample data.
Dec 17 '13 #5

ADezii
Expert 5K+
P: 8,616
I like dealing with Recordsets so I took a different approach from zmbd. Here is what I cam up with along withe the Results, but I am a little confused on the [Analyte] Field. Any questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. Dim strLine As String
  2. Dim intLineNum As Integer
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5.  
  6. Open "C:\Test\Test.txt" For Input As #1
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
  10.  
  11. CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError     'Clear tblResults
  12.  
  13. With rst
  14.   .AddNew       'for 1st Block of Data only
  15.     Do While Not EOF(1)
  16.       Line Input #1, strLine            'Read line into variable.
  17.       intLineNum = intLineNum + 1       'Line Counter
  18.         If InStr(strLine, "Sample:") > 0 Then
  19.           ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 8)
  20.         ElseIf InStr(strLine, "Additional Info:") > 0 Then
  21.           ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
  22.         ElseIf InStr(strLine, "Reference:") > 0 Then
  23.           ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
  24.         ElseIf InStr(strLine, "Analyte") > 0 Then
  25.           'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
  26.         ElseIf Left$(strLine, 1) = "%" Then
  27.           ![Percent] = Mid$(strLine, 3)
  28.         ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
  29.           ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
  30.         ElseIf intLineNum Mod 14 = 0 Then
  31.           intLineNum = 0        'RESET Line Counter for next Block of Data
  32.            ![Date] = Date
  33.             .Update             'Record Separator, time to Save Record
  34.             .AddNew             'Return to ADD Mode for next Block
  35.     End If
  36. Loop
  37. End With
  38.  
  39. Close #1
  40. rst.Close
  41. Set rst = Nothing
  42.  
tblResults:
Expand|Select|Wrap|Line Numbers
  1. Sample    Date    AdditionalInfo    Reference    Percent    ScalingRef
  2. A46731 8/1/13 6:57:04 PM    12/17/2013    C4027    Inco 718    0.89 18.68 0.07 18.37 0.33 48.06 0.00 >5.38 3.03 0.45    Inconel 718
  3. A46731 8/1/13 6:57:16 PM    12/17/2013    C4027    Inco 718    1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31    Inconel 718
  4. A46731 8/1/13 6:57:28 PM    12/17/2013    C4027    Inco 718    1.13 19.02 0.00 18.44 0.29 48.44 0.00 >5.34 2.89 0.42    Inconel 718
  5. A46731 8/1/13 6:57:41 PM    12/17/2013    C4027    Inco 718    1.08 18.26 0.08 18.54 0.32 48.71 0.00 >5.37 2.88 0.34    Inconel 718
P.S. - Kindly forgive my lack of proper Formatting on tblResults, heading out the door!
Dec 17 '13 #6

zmbd
Expert Mod 5K+
P: 5,331
ADezii,
I'll go out on the limb here being the Chemist, I recognize this type of output:

I am assuming that the results are mass-mass percentage:
Analyte Ti Cr Mn Fe Co Ni Cu Nb Mo W
% 1.18 18.43 <0.04 18.90 0.18 47.89 0.00 >5.48 3.13 0.31

Ti = Titanium = 1.18%(m/m)
Cr = Chromium = 18.43%(m/m)
(...)
W = Tungsten = 0.31%(m/m)

The way I would parse this:
(Table
[fields]

example based on entry one post 1)
->

tbl_samplelog
[samplelog_pk]autonumber
[samplelog_name]text(40)
[samplelog_day] date/time
[samplelog_AdditionalInfo]text(40)
[samplelog_Reference]text(40) << This might be another table
[samplelog_ScalingRef]text(40) << This might be another table

[1][A46731][8/1/13 6:57:04 PM][C4027][Inco 718][Inconel 718]

tbl_sampleconsituents
[sampleconsituents_pk]autonumber
[sampleconsituents_name]text(40)
[sampleconsituents_LDL]text(10)
[sampleconsituents_UDL]text(10)

[1][Ti][<1.0][>100]
[2][Cr][<1.0][>100]
[3][Mn][<0.04][>100]
(...)
[8][Nb][<1.0][>5.38]
(...)
[10][W][<1.0][>100]

tbl_analyteresults
[analyteresults_pk]autonumber
[analyteresults_fk_samplelog]numeric(long)
[analyteresults_fk_sampleconsituents]numeric(long)
[analyteresults_result]numeric(double)

[1][1][1][0.89]
[1][1][2][18.68]
[1][1][3][-1]
(...)
[1][1][8][101]
(...)
[1][1][10][0.45]

Reports would be set so that -1 and 101 which are outside of the percent range would the reference the LDL (lower detectio limit) or the UDL (upper detection limit) for the return value; thus, keeping [analyteresults_result]numeric(double) - However, there appears to be an inconsistancy with UDL on say Nb... so this is either part of the scaling or reference material and hence my thought above that there may be a new table or two needed.

Therefore I'd more than likely open a recordset on each of the tables.
Open the file for read, do while not eof
Read my line in (lineinput()), SPLIT([on spaces]) pull the
so line 1 yields the following array:
[Method:][1][Metal][Mode][Sample:][A46731][8/1/13][6:57:04][PM]

Select case on element[0]="Method:"
zsample=element[5]
zdate= "#" & element[5] & " " & element[6] & " " & element[6] & "#"

DAO add this to tbl_samplelog return bookmark to last modified and then return [samplelog_pk] for use later

Parse each string,
for example "Analyte" the select case might start a second loop to handle the consituents. Perhaps, Pulling Find first on the text to return [sampleconsituents_pk], then use [samplelog_pk]; however, if the results ALWAYS include ALL of the analyte results then, in this case, I think I'd use the insert sql, it's cleaner, drop the "%" concatenate the elements, then read the next line, would have to eval each array element for "</>" and alter as needed...

might need either "Scaling Method" or "Method:" to flag for new sample... would have to N/S this to make sure the logic is stable.
Dec 17 '13 #7

P: 14
zmbd is right, the "analyte" and "%" are the percentage of each element. thanks to ADezii, I got everything else working except the analyte results. I will like to ignore the "<" and ">" in the results, so I need create another table just for analyte results, and with the autonumbers I can link the the results back with the log information right?
so I can use split? select case? array? I am new at vba coding, could you show me how it get done to import the results in a table like below
table II
-------------------------------------------------------
AutoNumber 1 2 3
Ti 0.89 1.18 1.13
Cr 18.68 18.43 19.02
Mn 0.07 0.04 0.00
Fe 18.37 18.90 18.44
Co 0.33 0.18 0.29
Ni 48.06 47.89 48.44
Cu 0.00 0.00 0.00
Nb 5.38 5.48 5.34
Mo 3.03 3.13 2.89
W 0.45 0.31 0.42
--------------------------------------------------
thank you
Dec 18 '13 #8

zmbd
Expert Mod 5K+
P: 5,331
Would you please post the code you have so far so that we can make suggestions that integrate with what you already have in place.

Also, the way you have the table:
analyte, result 1, result 2, reasult 3

is not normalized and will be very difficult to relate back to samples A46731 8/1/13 6:57:04 PM; A46731 8/1/13 6:57:16 PM; etc...
Dec 18 '13 #9

ADezii
Expert 5K+
P: 8,616
@zmbd:
Thanks for the Chemistry Lesson.
@msaccess4me:
Everything can be neatly contained within a single Table as I feel it should be. The Code below will do just that, assuming Test.txt is in the same Folder as the Database within which the Code is being executed. I am a firm believer in 'Seeing is believing' so in addition to Posting the Code, I am attaching the Demo Database that I used for this Thread. Copy both Files in the *.Zip to any Folder, they must reside in the same Folder. Open the Database and the rest will be quite obvious. If this is not what you are looking for, no harm done. Good luck and let us know how you make out.
Expand|Select|Wrap|Line Numbers
  1. Dim strLine As String
  2. Dim intLineNum As Integer
  3. Dim MyDB As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim varSplit As Variant
  6.  
  7. Open CurrentProject.Path & "\Test.txt" For Input As #1
  8.  
  9. Set MyDB = CurrentDb
  10. Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
  11.  
  12. CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError     'Clear tblResults
  13.  
  14. With rst
  15.   .AddNew       'for 1st Block of Data only
  16.     Do While Not EOF(1)
  17.       Line Input #1, strLine            'Read line into variable.
  18.       intLineNum = intLineNum + 1       'Line Counter
  19.         If InStr(strLine, "Sample:") > 0 Then
  20.           ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 8)
  21.         ElseIf InStr(strLine, "Additional Info:") > 0 Then
  22.           ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
  23.         ElseIf InStr(strLine, "Reference:") > 0 Then
  24.           ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
  25.         ElseIf InStr(strLine, "Analyte") > 0 Then
  26.           'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
  27.         ElseIf Left$(strLine, 1) = "%" Then
  28.           varSplit = Split(strLine)
  29.             ![Ti] = varSplit(1)
  30.             ![Cr] = varSplit(2)
  31.             ![Mn] = varSplit(3)
  32.             ![Fe] = varSplit(4)
  33.             ![Co] = varSplit(5)
  34.             ![Ni] = varSplit(6)
  35.             ![Cu] = varSplit(7)
  36.             ![Nb] = varSplit(8)
  37.             ![Mo] = varSplit(9)
  38.             ![W] = varSplit(10)
  39.           '![Percent] = Mid$(strLine, 3)
  40.         ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
  41.           ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
  42.         ElseIf intLineNum Mod 14 = 0 Then
  43.           intLineNum = 0        'RESET Line Counter for next Block of Data
  44.            ![Date] = Date
  45.             .Update             'Record Separator, time to Save Record
  46.             .AddNew             'Return to ADD Mode for next Block
  47.     End If
  48. Loop
  49. End With
  50.  
  51. Close #1
  52. rst.Close
  53. Set rst = Nothing
  54.  
  55. With DoCmd
  56.   .OpenTable "tblResults", acViewNormal, acReadOnly
  57.   .Maximize
  58. End With
  59.  
  60. Exit_cmdTest_Click:
  61.   Exit Sub
  62.  
  63. Err_cmdTest_Click:
  64.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  65.     Resume Exit_cmdTest_Click
  66.  
Attached Files
File Type: zip Complex Text File.zip (27.7 KB, 159 views)
Dec 18 '13 #10

P: 14
thank you ADezii, you code works very well. I made a few small adjustments, because the Analyte elements maybe different base on what kind material we are using, but I was having hard time to carry over the value (varSplitA) I want to link with next line. Here is what I have so far
Expand|Select|Wrap|Line Numbers
  1. Function InputRawDate1()
  2. Dim strLine As String
  3. Dim intLineNum As Integer
  4. Dim MyDB As DAO.Database
  5. Dim rst As DAO.Recordset
  6. Dim varSplitA As Variant
  7. Dim varSplit As Variant
  8. Dim strReplacedPercentage As String
  9. Dim strReplacedAnalyte As String
  10.  
  11.  
  12.  
  13. Open CurrentProject.Path & "\input.txt" For Input As #1
  14.  
  15. Set MyDB = CurrentDb
  16. Set rst = MyDB.OpenRecordset("tblRawMaterialData", dbOpenDynaset)
  17.  
  18. CurrentDb.Execute "DELETE * FROM tblRawMaterialData", dbFailOnError     'Clear tblResults
  19.  
  20. With rst
  21.   .AddNew       'for 1st Block of Data only
  22.     Do While Not EOF(1)
  23.       Line Input #1, strLine            'Read line into variable.
  24.       intLineNum = intLineNum + 1       'Line Counter
  25.         If InStr(strLine, "Sample:") > 0 Then
  26.           ![sample] = Mid$(strLine, InStr(strLine, "Sample:") + 9, 6)
  27.           ![fldDate] = Mid$(strLine, InStr(strLine, "Sample:") + 16)
  28.         ElseIf InStr(strLine, "Additional Info:") > 0 Then
  29.           ![AdditionalInfo] = Mid$(strLine, InStr(strLine, "Additional Info:") + 17)
  30.         ElseIf InStr(strLine, "Reference:") > 0 Then
  31.           ![Reference] = Mid$(strLine, InStr(strLine, "Reference:") + 11)
  32.         ElseIf InStr(strLine, "Analyte") > 0 Then
  33.         strReplacedAnalyte = Trim(Replace(strLine, vbTab, " "))
  34.         varSplitA = Split(Mid$(strReplacedAnalyte, InStr(strReplacedAnalyte, "Analyte") + 8), " ")
  35.           'Debug.Print Mid$(strLine, InStr(strLine, "Analyte") + 8)
  36.         ElseIf Left$(strLine, 1) = "%" Then
  37.          strReplacedPercentage = Trim(Replace(strLine, vbTab, " "))
  38.           'varSplit = Split(strLine)
  39.           varSplit = Split(Mid$(strReplacedPercentage, InStr(strReplacedPercentage, "%") + 8), " ")
  40.             ![varSplitA(1)] = varSplit(1)
  41.             ![varSplitA(2)] = varSplit(2)
  42.             ![varSplitA(3)] = varSplit(3)
  43.             ![varSplitA(4)] = varSplit(4)
  44.             ![varSplitA(5)] = varSplit(5)
  45.             ![varSplitA(6)] = varSplit(6)
  46.             ![varSplitA(7)] = varSplit(7)
  47.             ![varSplitA(8)] = varSplit(8)
  48.             ![varSplitA(9)] = varSplit(9)
  49.             ![varSplitA(10)] = varSplit(10)
  50.           '![Percent] = Mid$(strLine, 3)
  51.         ElseIf InStr(strLine, "Scaling Ref. :") > 0 Then
  52.           ![ScalingRef] = Mid$(strLine, InStr(strLine, "Scaling Ref. :") + 15)
  53.         ElseIf intLineNum Mod 14 = 0 Then
  54.           intLineNum = 0        'RESET Line Counter for next Block of Data
  55.            '![Date] = Date
  56.             .Update             'Record Separator, time to Save Record
  57.             .AddNew             'Return to ADD Mode for next Block
  58.     End If
  59. Loop
  60. End With
  61.  
  62. Close #1
  63. rst.Close
  64. Set rst = Nothing
  65.  
  66. With DoCmd
  67.   .OpenTable "tblRawMaterialData", acViewNormal, acReadOnly
  68.   .Maximize
  69. End With
  70.  
  71. 'Exit_cmdTest_Click:
  72.   'Exit Sub
  73.  
  74. 'Err_cmdTest_Click:
  75.   'MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  76.     'Resume Exit_cmdTest_Click
  77.  
  78. End Function
I attached a sample txt file, so you can see better. thanks
Attached Files
File Type: txt Input.txt (4.1 KB, 250 views)
Dec 18 '13 #11

zmbd
Expert Mod 5K+
P: 5,331
I made a few small adjustments, because the Analyte elements maybe different base on what kind material we
Hence the method I suggested with the multiple tables... it is the normalized approach.
If you are interested, I may have time to bash something togeither based upon what I have in the lab.
Dec 18 '13 #12

ADezii
Expert 5K+
P: 8,616
You can populate the Analyte Elements and their corresponding Percentages into Parallel Arrays where each Element in the Analyte Array can represent a Field in tblResults. You can then set the Value of this Field in a Recordset to its corresponding Percentage, as the Debug Statement will show. These Code Segments should illustrate these points.
Expand|Select|Wrap|Line Numbers
  1. Dim varAnalyte As Variant
  2. Dim varValue As Variant
  3. Dim intCtr
  4. Dim MyDB As DAO.Database
  5. Dim rst As DAO.Recordset
  6.  
  7. Set MyDB = CurrentDb
  8. Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
  9.  
  10. varAnalyte = Array("Ti", "Cr", "Mn", "Fe", "Co", "Ni", "Cu", "Nb", "Mo", "W")
  11. varValue = Array(0.89, 18.68, 0.07, 18.37, 0.33, 48.06, 0#, 5.38, 3.03, 0.45)
  12.  
  13. rst.AddNew
  14. For intCtr = LBound(varAnalyte) To UBound(varAnalyte)
  15.   Debug.Print varAnalyte(intCtr) & " <==> " & varValue(intCtr)
  16.   'Update the corresponding Field in tblResults("Ti","Cr","Mn","Fe","Co","Ni","Cu","Nb","Mo","W")
  17.   rst.Fields(varAnalyte(intCtr)) = varValue(intCtr)
  18. Next
  19. rst.Update
  20.  
  21. rst.Close
  22. Set rst = Nothing
  23.  
Debug.Print results:
Expand|Select|Wrap|Line Numbers
  1. Ti <==> 0.89
  2. Cr <==> 18.68
  3. Mn <==> 0.07
  4. Fe <==> 18.37
  5. Co <==> 0.33
  6. Ni <==> 48.06
  7. Cu <==> 0
  8. Nb <==> 5.38
  9. Mo <==> 3.03
  10. W <==> 0.45
  11.  
Dec 19 '13 #13

ADezii
Expert 5K+
P: 8,616
@msaccess4me:
I do believe that I have arrived at a viable solution to this interesting Thread. Again, simply extract the Revised Database and Input.txt into the 'same' Folder then Open the Database. Before I put any more time into this, I want to make absolutely sure that this is what you are looking for, or at least close to it.

P.S. - Should you wish to use the Parent ==> Child Table approach, it will be a simple matter to do so since I incorporated an AutoNumber/Primary Key into the Results Table (tblResults). Let us know how you make out.
Attached Files
File Type: zip Complex Text File 2.zip (44.4 KB, 170 views)
Dec 19 '13 #14

P: 14
@ADezii: Thank you so much for helping me out. your code works very well.
also, @zmbd:thanks for your help too.
I works with MS Access a lot, but not so much VBA coding. I really like to learn more of it. Do you guys know any good way or good website to help me to understand it better.
p.s. Holidays are coming soon, Happy Holidays to you all :)
Dec 19 '13 #15

Post your reply

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